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 think using a CASE is equivalent for the planner to using your own custom blackbox function. So no way to improve that. for 2. : if you can't pass limit and offset in your ORM, a small workaround is to number your row following the order you defined with the function row_number() over(your order here), then you can use your ORM to design where conditions equivalent to limit and offset : WHERE row_number BETWEEN your_offset AND your_limit Cheers, Rémi-C 2015-02-04 21:40 GMT+01:00 Paul Jungwirth <p...@illuminatedcomputing.com>: > >> 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.1280268...@sss.pgh.pa.us > > So I think this query would work: > > select * from topic > where id = 1000 > union all > (select * from topic > where id <> 1000 > order by bumped_at desc > limit 29) > order by case when id = 1000 then 0 else 1 end, bumped_at desc > ; > > > I need to be able to offset and limit the union hack in a view, which > > is proving very tricky. > > Since this is sort of a "parameterized view" (which Postgres does not > have) you are probably better off figuring out how to make the UNION > query work with your ORM. What ORM is it? Maybe someone here can help > you with that. Or maybe instead of a view you could write a > set-returning function, e.g. as described here: > > > http://stackoverflow.com/questions/11401749/pass-in-where-parameters-to-postgresql-view > > Paul > > -- > _________________________________ > Pulchritudo splendor veritatis. > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >
