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

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 <pj(at)illuminatedcomputing(dot)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.1280268424@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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>