sam.saffron wrote > I have this query: > > select * from topics > order by case when id=1 then 0 else 1 end, bumped_at desc > limit 30 > > It works fine, bumps id 1 to the front of the sort fine but is > terribly inefficient and scans > > OTH > > "select * from topics where id = 1" is super fast > > "select * from topics order by bumped_at desc limit 30" is super fast > > Even this is fast, and logically equiv as id is primary key unique > > select * from topic > where id = 1000 > union all > select * from ( > select * from topics > where id <> 1000 > order by bumped_at desc > limit 30 > ) as x > limit 30 > > > However, the contortions on the above query make it very un-ORM > friendly as I would need to define a view for it but would have no > clean way to pass limits and offsets in. > > Is there any clean technique to bump up particular rows to the front > of a sort if a certain condition is met without paying a huge > performance hit?
CREATE FUNCTION ...? Probably with a VARIADIC argument. David J. -- View this message in context: http://postgresql.nabble.com/How-do-I-bump-a-row-to-the-front-of-sort-efficiently-tp5836354p5836356.html Sent from the PostgreSQL - hackers mailing list archive at Nabble.com. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
