From: David G Johnston <david.g.johnston@gmail.com>
To: pgsql-performance@postgresql.org
Subject: Re: two table join with order by on both tables attributes
Date: Thu, 7 Aug 2014 16:42:45 -0700 (PDT)
Message-ID: <1407454965510-5814137.post@n5.nabble.com> (raw)
In-Reply-To: <8501437C-8756-47B8-8691-CFD97717ACBB@gmail.com>
References: <8501437C-8756-47B8-8691-CFD97717ACBB@gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
Evgeniy Shishkin wrote
> Hello,
>
> suppose you have two very simple tables with fk dependency, by which we
> join them
> and another attribute for sorting
>
> like this
> select * from users join notifications on users.id=notifications.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 =\
>
> Can anybody suggest something or explain this behavior?
Can you explain why a nested loop is best for your data? Given my
understanding of an expected "priority"cardinality I would expect your ORDER
BY to be extremely inefficient and not all that compatible with a nested
loop approach.
You can use the various parameters listed on this page to force the desired
plan and then provide EXPLAIN ANALYZE results for the various executed plans
and compare them.
http://www.postgresql.org/docs/9.3/interactive/runtime-config-query.html#RUNTIME-CONFIG-QUERY-ENABLE
And now for the obligatory "read this" link:
https://wiki.postgresql.org/wiki/SlowQueryQuestions
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.
David J.
--
View this message in context: http://postgresql.1045698.n5.nabble.com/two-table-join-with-order-by-on-both-tables-attributes-tp581...
Sent from the PostgreSQL - performance mailing list archive at Nabble.com.
--
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: david.g.johnston@gmail.com
Subject: Re: two table join with order by on both tables attributes
In-Reply-To: <1407454965510-5814137.post@n5.nabble.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