Alexey Nalbat <alexey(at)price(dot)ru> writes:
> While executing this query postgres at first creates hash on table
> "resellers", then get from index "products_mcr" for rows with
> "m_id=123" already ordered (!!!) pairs "c_id,r_id", for each that
> pair it checks join condition using hash. If postgers behaves like
> this then the result of this hash join is already sorted, and extra
> "Sort" is not needed.
No, because the hash step might choose to divide its processing into
multiple "batches", thereby destroying the sort order. The outer path's
ordering is preserved only in relatively small test cases...
regards, tom lane