> On 08 Aug 2014, at 16:29, Marti Raudsepp <ma...@juffo.org> wrote: > > On Fri, Aug 8, 2014 at 4:05 AM, Evgeniy Shishkin <itparan...@gmail.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 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
