Sort Postgresql table using previously calculated data

Question

I have an operation which gives me a list of IDs and some score related to these IDs. Then I need query the database and sort rows using the data above.

I tried something like (I'm using PostgreSQL):

SELECT * FROM sometable
LEFT OUTER JOIN (VALUES (629, 3), (624, 1))  /* Here is my data */
AS x(id, ordering)
USING (id)
WHERE some_column_id=1
ORDER BY x.ordering;

But for ~10000 rows it runs about 15sec on my machine. Is there a better way to sort my table using a previously calculated data?

@jarlh will it be OK for say ~1000 clauses in the CASE expression? — coldmind
– coldmind, Commented Jul 6, 2016 at 14:06
Write the ids and ordering into a temporary table (if they are not already in a table). Add an index on id and run the query. — Gordon Linoff
– Gordon Linoff, Commented Jul 6, 2016 at 14:10

Gordon Linoff · Accepted Answer · 2016-07-06 14:12:34Z

1

What is the performance of this version?

SELECT st.*
FROM sometable st
WHERE st.some_column_id = 1
ORDER BY (CASE WHEN st.id = 629 then 3 WHEN st.id = 624 THEN 1 END);

An index on sometable(some_column_id) might also speed the query.

However, I don't understand why your version on a table with 10,000 rows would take 15 seconds.

answered Jul 6, 2016 at 14:12

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

coldmind Over a year ago

Well, I tried my and your solution on another database with even more rows, and this works pretty fast, seems like an issue with my local database... (There already is an index on some_column_id).

coldmind Over a year ago

Will it be executing slow if there will be about ~1000 cases?

Gordon Linoff Over a year ago

@coldmind . . . That would probably slow it down considerably. You should probably use a (temp) table with an index.

Collectives™ on Stack Overflow

Sort Postgresql table using previously calculated data

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related