Received: from malur.postgresql.org ([217.196.149.56])
 by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from
 <pgsql-performance-owner+M52043=pgsql-performance-ar@postgresql.org>)
 id 1XFXeG-0007z0-29
 for pgsql-performance@arkaria.postgresql.org; Fri, 08 Aug 2014 00:03:08 +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+M52043=pgsql-performance-ar@postgresql.org>)
 id 1XFXeF-0002S7-A7
 for pgsql-performance@arkaria.postgresql.org; Fri, 08 Aug 2014 00:03:07 +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 1XFXeD-0002Ry-2q
 for pgsql-performance@postgresql.org; Fri, 08 Aug 2014 00:03:05 +0000
Received: from mail-wg0-x233.google.com ([2a00:1450:400c:c00::233])
 by magus.postgresql.org with esmtps (TLS1.0:RSA_AES_256_CBC_SHA1:256)
 (Exim 4.80) (envelope-from <itparanoia@gmail.com>)
 id 1XFXe8-0001DR-SW
 for pgsql-performance@postgresql.org; Fri, 08 Aug 2014 00:03:03 +0000
Received: by mail-wg0-f51.google.com with SMTP id b13so4916801wgh.22
 for <pgsql-performance@postgresql.org>; Thu, 07 Aug 2014 17:02:57 -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=Gpad2T6gxGSjPoiXc9HzCQdLeqM2nV9BMgJrJoE7lcg=;
 b=N1T+7utxdfeE48rhLik5JfebeeimUz1wWqvTHk/T3bTkRiCU2rW1xmOAlRV1kPb+Nt
 OIbn4YjAwWQm247UWn6hnHJIpBohF5qu7nJVV6PckRrCVOSHij8tMp97Q3mauYQw3QtF
 d5gT71khoZ3+rgptz0neOkjvHj7rcDMG/y4ui80Xwqjx9O7iN02rxg3DDUFUgormMBPM
 CE3t4Xb2lbzCPhkDTwrEZXQdHdCc+QZbV6ehAoClcuP/bczYcTTv/NaDfh3MAQsQQxS8
 JICOx0eNSojwNL8YfdAPCkHK0vh2hFx3oXt0YnNf8HasOP5lmBh5Xx4BAHen2f94c0VL
 7S7g==
X-Received: by 10.194.62.104 with SMTP id x8mr3079689wjr.7.1407456177495;
 Thu, 07 Aug 2014 17:02:57 -0700 (PDT)
Received: from [192.168.1.4] ([178.124.207.68])
 by mx.google.com with ESMTPSA id p3sm13279404wjw.13.2014.08.07.17.02.56
 for <multiple recipients>
 (version=TLSv1 cipher=ECDHE-RSA-RC4-SHA bits=128/128);
 Thu, 07 Aug 2014 17:02:56 -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: <1407454965510-5814137.post@n5.nabble.com>
Date: Fri, 8 Aug 2014 03:02:54 +0300
Cc: pgsql-performance@postgresql.org
Content-Transfer-Encoding: quoted-printable
Message-Id: <F9A97F30-180E-476B-A0B2-AA29B33114ED@gmail.com>
References: <8501437C-8756-47B8-8691-CFD97717ACBB@gmail.com>
 <1407454965510-5814137.post@n5.nabble.com>
To: David G Johnston <david.g.johnston@gmail.com>
X-Mailer: Apple Mail (2.1971.5)
X-Pg-Spam-Score: -0.9 (/)
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

My question was about that you can not have fast execution of this kind of =
query in postgresql.
With any runtime configuration you just swith from seq scan and hash join t=
o merge join, and then you have a sort node.

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.=20

I don't understand why.

Do you have any schema and GUCs which performs this kind of query well?

Sorry for top posting.=20=20

> Can you explain why a nested loop is best for your data?  Given my
> understanding of an expected "priority"cardinality I would expect your OR=
DER
> BY to be extremely inefficient and not all that compatible with a nested
> loop approach.
>=20
> You can use the various parameters listed on this page to force the desir=
ed
> plan and then provide EXPLAIN ANALYZE results for the various executed pl=
ans
> and compare them.
>=20
> http://www.postgresql.org/docs/9.3/interactive/runtime-config-query.html#=
RUNTIME-CONFIG-QUERY-ENABLE
>=20
> And now for the obligatory "read this" link:
>=20
> https://wiki.postgresql.org/wiki/SlowQueryQuestions
>=20
> If you can show that in fact the nested loop (or some other plan) performs
> better than the one chosen by the planner - and can provide data that the
> developers can use to replicate the experiment - then improvements can be
> made.  At worse you will come to understand why the planner is right and =
can
> then explore alternative models.
>=20
> David J.
>=20
>=20
>=20
>=20
> --
> View this message in context: http://postgresql.1045698.n5.nabble.com/two=
-table-join-with-order-by-on-both-tables-attributes-tp5814135p5814137.html
> Sent from the PostgreSQL - performance mailing list archive at Nabble.com.
>=20
>=20
> --=20
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance



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