Re: two table join with order by on both tables attributes

Evgeniy Shishkin <itparanoia(at)gmail(dot)com> writes:
>> select * from users join notifications on users.id=notifications.user_id ORDER BY users.priority desc ,notifications.priority desc 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.

regards, tom lane