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

On Fri, Aug 8, 2014 at 4:05 AM, Evgeniy Shishkin <itparanoia(at)gmail(dot)com> wrote:
>>>>> 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)

> And actually with this kind of query we really want the most wanted notifications, by the user.
> So we really can rewrite to order by users.priority desc, id asc, notifications.priority desc according to business logic.

You can rewrite it with LATERAL to trick the planner into sorting each
user's notifications separately. This should give you the nestloop
plan you expect:

SELECT *
FROM users,
LATERAL (
SELECT * FROM notifications WHERE notifications.user_id=users.id
ORDER BY notifications.priority DESC
) AS notifications
ORDER BY users.priority DESC, users.id

It would be great if Postgres could do this transformation automatically.

There's a "partial sort" patch in the current CommitFest, which would
solve the problem partially (it could use the index on users, but the
notifications sort would have to be done in memory still).
https://commitfest.postgresql.org/action/patch_view?id=1368

Regards,
Marti