>>> select * from users join notifications on users.id=notifications.user_id ORDER BY users.priority desc
,notifications.prioritydesc limit 10;
>
>> In my understanding, i need to have two indexes
>> on users(priority desc, id)
>> and notifications(user_id, priority desc)
>> then postgresql would choose nested loop and get sorted data from indexes.
>> But it wont.
>
> Indeed. If you think a bit harder, you'll realize that the plan you
> suggest would *not* produce the sort order requested by this query.
> It would (if I'm not confused myself) produce an ordering like
> users.priority desc, id asc, notifications.priority desc
> which would only match what the query asks for if there's just a single
> value of id per users.priority value.
>
> Offhand I think that the planner will not recognize a nestloop as
> producing a sort ordering of this kind even if the query did request the
> right ordering. That could perhaps be improved, but I've not seen many
> if any cases where it would be worth the trouble.
Thanks Tom, you are right.
But may be some sort of skip index scan ala loose index scan will help with index on notifications(priority
desc,user_id)?
I know that this is currently not handled by native executors.
May by i can work around this using WITH RECURSIVE query?
Also, are there any plans to handle loose index scan in the upcoming release?