Re: two table join with order by on both tables attributes

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-tp5814135p5814137.html
Sent from the PostgreSQL - performance mailing list archive at Nabble.com.