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
>> 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:
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: