From: Evgeniy Shishkin <itparanoia@gmail.com>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: quoted-printable
Subject: two table join with order by on both tables attributes
Message-Id: <8501437C-8756-47B8-8691-CFD97717ACBB@gmail.com>
Date: Fri, 8 Aug 2014 02:21:51 +0300
To: "pgsql-performance@postgresql.org list" <pgsql-performance@postgresql.org>
Mime-Version: 1.0 (Mac OS X Mail 8.0 \(1971.5\))
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

Hello,

suppose you have two very simple tables with fk dependency, by which we joi=
n them
and another attribute for sorting

like this
select * from users join  notifications on users.id=3Dnotifications.user_id=
 ORDER BY users.priority desc ,notifications.priority desc limit 10;

Very typical web query.

No matter which composite indexes i try, postgresql can not make efficient =
nested loop plan using indexes.
It chooses all sorts of seq scans and hash joins or merge join and always a=
 sort node and then a limit 10.

Neither plan provides acceptable performance. And tables tend to grow =3D\

Can anybody suggest something or explain this behavior?

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