> Or maybe instead of a view you could write a
> set-returning function, e.g. as described here:
I thought I'd see if I could make this work just for fun. Here is a
simple proof of concept (on 9.3):
-- DROP TABLE IF EXISTS topics;
CREATE TABLE topics (
id INTEGER PRIMARY KEY,
bumped_at INTEGE
Hey,
I'm not a guru, here is what I understood.
You are mixing several problems in the same question :
- 1. why the planner isn't more efficient
- 2. why the workaround is difficult to use with an ORM.
for 1. you can't do much (as said by others, you don't really need a case
here anyway). I thin
>> I imagine your original would be at risk of LIMITing out the very row you
>> seek to get at the "top", since you don't have an ORDER BY to tell it which
>> ones to keep during the outer LIMIT.
Here is an old thread about combining ORDER BY with UNION:
http://www.postgresql.org/message-id/16814
The union hack may be able to work, but what I want is slightly more complex
I want this to work efficiently, even without the case it chokes:
explain select * from testing order by id in (100,2,-1) desc, id limit 30;
QUERY PLAN
On Tue, Feb 3, 2015 at 11:28 PM, Sam Saffron wrote:
> Note: I still consider this a bug/missing feature of sorts since the
> planner could do better here, and there is no real clean way of
> structuring a query to perform efficiently here, which is why I
> erroneously cross posted this to hacker
Note: I still consider this a bug/missing feature of sorts since the
planner could do better here, and there is no real clean way of
structuring a query to perform efficiently here, which is why I
erroneously cross posted this to hacker initially:
# create table testing(id serial primary key, dat
On Tue, Feb 3, 2015 at 9:33 PM, BladeOfLight16
wrote:
> This is why ORMs are bad. They make hard problems *much* harder, and the
> only benefit is that they maybe make easy problems a little quicker. The
> cost/savings is *heavily* skewed toward the cost, since there's no upper
> bound on the cos
On Mon, Feb 2, 2015 at 1:16 AM, Sam Saffron wrote:
> 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.
>
This is why ORMs are bad. They make hard problems *much* harder,
On 02/02/15 08:40, hubert depesz lubaczewski wrote:
> On Mon, Feb 02, 2015 at 05:16:40PM +1100, Sam Saffron wrote:
>> 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
On Mon, Feb 02, 2015 at 05:16:40PM +1100, Sam Saffron wrote:
> 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
> l
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_a
11 matches
Mail list logo
