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

> On 08 Aug 2014, at 16:29, Marti Raudsepp <marti(at)juffo(dot)org> wrote:
>
> 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
>

Thank you very much.

> 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