Received: from malur.postgresql.org ([217.196.149.56])
 by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from
 <pgsql-performance-owner+M52045=pgsql-performance-ar@postgresql.org>)
 id 1XFYH8-0000ub-1r
 for pgsql-performance@arkaria.postgresql.org; Fri, 08 Aug 2014 00:43:18 +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+M52045=pgsql-performance-ar@postgresql.org>)
 id 1XFYH6-0000jY-JW
 for pgsql-performance@arkaria.postgresql.org; Fri, 08 Aug 2014 00:43:16 +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 1XFYH5-0000jR-M3
 for pgsql-performance@postgresql.org; Fri, 08 Aug 2014 00:43:15 +0000
Received: from mail-we0-x234.google.com ([2a00:1450:400c:c03::234])
 by magus.postgresql.org with esmtps (TLS1.0:RSA_AES_256_CBC_SHA1:256)
 (Exim 4.80) (envelope-from <itparanoia@gmail.com>)
 id 1XFYH1-0001yo-KP
 for pgsql-performance@postgresql.org; Fri, 08 Aug 2014 00:43:14 +0000
Received: by mail-we0-f180.google.com with SMTP id w61so4874391wes.11
 for <pgsql-performance@postgresql.org>; Thu, 07 Aug 2014 17:43:10 -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=H9ytB1Z9COQqLON9LVCBTdh6t8w1uCjp6onQieGvPXE=;
 b=G6rWQp7EMx1ct6D9dj7hRytT4h+WhoaX56H+W51jDJiyzOQ5IhRRx3q51A/kyoqCn/
 bwM2JCbDwCel9zqljuTyLQAhkHd3dfTVfQAyN829JZFgpoxLSymNLcgC+AsgtWFTmzhm
 2q+LDUsYbjfhz9b6PAIAB9XaVDjbi+LUW1BPh80ZX92+snoC5NKKe1gRtAMvXbpxDRZh
 IiHJNVO/HYj0aCMymO/MqBgoqProAWyXGhRZRrFdhxl7HERfmzUnkaoUoRrqtnW3zEU9
 8FC4rdfQMvse8LnPg/fgfkwtvsWwBUUSlatTtQWhHAA1wyBLnozGBoOJPZA+W2ovDsoC
 D+7g==
X-Received: by 10.180.11.72 with SMTP id o8mr255502wib.71.1407458590276;
 Thu, 07 Aug 2014 17:43:10 -0700 (PDT)
Received: from [192.168.1.4] ([178.124.207.68])
 by mx.google.com with ESMTPSA id v14sm13436406wjw.38.2014.08.07.17.43.08
 for <multiple recipients>
 (version=TLSv1 cipher=ECDHE-RSA-RC4-SHA bits=128/128);
 Thu, 07 Aug 2014 17:43:09 -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: <24132.1407457158@sss.pgh.pa.us>
Date: Fri, 8 Aug 2014 03:43:07 +0300
Cc: David G Johnston <david.g.johnston@gmail.com>,
 pgsql-performance@postgresql.org
Content-Transfer-Encoding: quoted-printable
Message-Id: <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>
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

>>> select * from users join  notifications on users.id=3Dnotifications.use=
r_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 indexe=
s.
>> 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.


Thanks Tom, you are right.

But may be some sort of skip index scan ala loose index scan will help with=
 index on notifications(priority desc,user_id)?

I know that this is currently not handled by native executors.
May by i can work around this using WITH RECURSIVE query?

Also, are there any plans to handle  loose index scan in the upcoming relea=
se?

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