From: | Sam Saffron <sam(dot)saffron(at)gmail(dot)com> |
---|---|
To: | PGSQL Mailing List <pgsql-general(at)postgresql(dot)org>, "pgsql-hackers(at)postgresql(dot)org" <pgsql-hackers(at)postgresql(dot)org> |
Subject: | How do I bump a row to the front of sort efficiently |
Date: | 2015-02-02 06:16:40 |
Message-ID: | CAAtdryPRK9Fb-a4-JPNB=p77jLrW7Mvs4j8VQ8-Hf5o09E49pg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general pgsql-hackers |
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?
From | Date | Subject | |
---|---|---|---|
Next Message | David G Johnston | 2015-02-02 06:29:29 | Re: How do I bump a row to the front of sort efficiently |
Previous Message | David G Johnston | 2015-02-02 05:11:32 | Re: Problem with REFERENCES on INHERITS |
From | Date | Subject | |
---|---|---|---|
Next Message | David G Johnston | 2015-02-02 06:29:29 | Re: How do I bump a row to the front of sort efficiently |
Previous Message | Josh Berkus | 2015-02-02 06:04:44 | Re: Release note bloat is getting out of hand |