From: Evgeniy Shishkin <itparanoia@gmail.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: David G Johnston <david.g.johnston@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: two table join with order by on both tables attributes
Date: Fri, 8 Aug 2014 04:05:45 +0300
Message-ID: <F5D93B74-237C-4595-B159-9D90CCC47448@gmail.com> (raw)
In-Reply-To: <3067AFAE-31AD-4E51-819E-99362483FA6F@gmail.com>
References: <8501437C-8756-47B8-8691-CFD97717ACBB@gmail.com>
<1407454965510-5814137.post@n5.nabble.com>
<F9A97F30-180E-476B-A0B2-AA29B33114ED@gmail.com>
<24132.1407457158@sss.pgh.pa.us>
<3067AFAE-31AD-4E51-819E-99362483FA6F@gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
> On 08 Aug 2014, at 03:43, Evgeniy Shishkin <itparanoia@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)
>>> 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.
>
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.
And we will benefit if this case would be improved.
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: itparanoia@gmail.com, tgl@sss.pgh.pa.us, david.g.johnston@gmail.com
Subject: Re: two table join with order by on both tables attributes
In-Reply-To: <F5D93B74-237C-4595-B159-9D90CCC47448@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox