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

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 to 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.

I don't understand why.

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

Sorry for top posting.

> 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.
>
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance