Received: from malur.postgresql.org ([217.196.149.56])
 by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from
 <pgsql-performance-owner+M52046=pgsql-performance-ar@postgresql.org>)
 id 1XFYd4-0001jd-OF
 for pgsql-performance@arkaria.postgresql.org; Fri, 08 Aug 2014 01:05:58 +0000
Received: from localhost ([127.0.0.1] helo=postgresql.org)
 by malur.postgresql.org with smtp (Exim 4.80) (envelope-from
 <pgsql-performance-owner+M52046=pgsql-performance-ar@postgresql.org>)
 id 1XFYd4-00057r-83
 for pgsql-performance@arkaria.postgresql.org; Fri, 08 Aug 2014 01:05:58 +0000
Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29])
 by malur.postgresql.org with esmtps (TLS1.2:DHE_RSA_AES_256_CBC_SHA256:256)
 (Exim 4.80) (envelope-from <itparanoia@gmail.com>)
 id 1XFYd3-00057l-Ei
 for pgsql-performance@postgresql.org; Fri, 08 Aug 2014 01:05:57 +0000
Received: from mail-wg0-x22f.google.com ([2a00:1450:400c:c00::22f])
 by magus.postgresql.org with esmtps (TLS1.0:RSA_AES_256_CBC_SHA1:256)
 (Exim 4.80) (envelope-from <itparanoia@gmail.com>)
 id 1XFYd0-0002U8-76
 for pgsql-performance@postgresql.org; Fri, 08 Aug 2014 01:05:56 +0000
Received: by mail-wg0-f47.google.com with SMTP id b13so4968419wgh.30
 for <pgsql-performance@postgresql.org>; Thu, 07 Aug 2014 18:05:52 -0700 (PDT)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113;
 h=content-type:mime-version:subject:from:in-reply-to:date:cc
 :content-transfer-encoding:message-id:references:to;
 bh=g9VpuHSvvcN8iV+DYCAqzoHjWpQjsJjc0iDGlRbC3zY=;
 b=LFG70Jq2AOZIddiCs4leSGVn8bri9gPydVgjmgBF7F2ZwIHeUTAyQ3Ij6HIp2ZmduU
 ey5Fb574qoQ1gHtBuyquyEDunNKfD2pg6zOFBh4TVDZto6xAk2IrHIg/eX7c4SQqtIDm
 I46CDJsziKRi/jSlk4dEsZTLcghGr/Zpe9O+6gAbin1OyTsyYXDcCWzD+lJc6CbuFjPJ
 /qqEbdFw860gJXnScTc4d9gcsOVffB3MYO0c2+ZUub0n5/fbhxM92QR9C6X2p3myl9Jp
 e4SX9/AvCWo/eEKxqNw840u8g/5hit7SnS8uA8EPECa451x8P9ZytMpd9yWXG6Ja2CfH
 XiYA==
X-Received: by 10.180.72.234 with SMTP id g10mr482393wiv.52.1407459952768;
 Thu, 07 Aug 2014 18:05:52 -0700 (PDT)
Received: from [192.168.1.4] ([178.124.207.68])
 by mx.google.com with ESMTPSA id mw4sm1764953wic.20.2014.08.07.18.05.51
 for <multiple recipients>
 (version=TLSv1 cipher=ECDHE-RSA-RC4-SHA bits=128/128);
 Thu, 07 Aug 2014 18:05:52 -0700 (PDT)
Content-Type: text/plain; charset=us-ascii
Mime-Version: 1.0 (Mac OS X Mail 8.0 \(1971.5\))
Subject: Re: two table join with order by on both tables attributes
From: Evgeniy Shishkin <itparanoia@gmail.com>
In-Reply-To: <3067AFAE-31AD-4E51-819E-99362483FA6F@gmail.com>
Date: Fri, 8 Aug 2014 04:05:45 +0300
Cc: David G Johnston <david.g.johnston@gmail.com>,
 pgsql-performance@postgresql.org
Content-Transfer-Encoding: quoted-printable
Message-Id: <F5D93B74-237C-4595-B159-9D90CCC47448@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>
To: Tom Lane <tgl@sss.pgh.pa.us>
X-Mailer: Apple Mail (2.1971.5)
X-Pg-Spam-Score: -2.0 (--)
List-Archive: <http://archives.postgresql.org/pgsql-performance>
List-Help: <mailto:majordomo@postgresql.org?body=help>
List-ID: <pgsql-performance.postgresql.org>
List-Owner: <mailto:pgsql-performance-owner@postgresql.org>
List-Post: <mailto:pgsql-performance@postgresql.org>
List-Subscribe: <mailto:majordomo@postgresql.org?body=sub%20pgsql-performance>
List-Unsubscribe: 
 <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
X-Mailing-List: pgsql-performance
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org


> On 08 Aug 2014, at 03:43, Evgeniy Shishkin <itparanoia@gmail.com> wrote:
>=20
>>>> select * from users join  notifications on users.id=3Dnotifications.us=
er_id ORDER BY users.priority desc ,notifications.priority desc limit 10;
>>=20
>>> 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 index=
es.
>>> But it wont.=20
>>=20
>> 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.
>>=20
>> 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.
>=20

And actually with this kind of query we really want the most wanted notific=
ations, by the user.
So we really can rewrite to order by users.priority desc, id asc, notificat=
ions.priority desc according to business logic.
And we will benefit if this case would be improved.

--=20
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance