Re: How do I bump a row to the front of sort efficiently

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.