Received: from sss.pgh.pa.us (sss.pgh.pa.us [216.151.103.158]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f4EEHJA00116 for ; Mon, 14 May 2001 10:17:19 -0400 (EDT) (envelope-from tgl@sss.pgh.pa.us) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.11.3/8.11.3) with ESMTP id f4EEH9R08990; Mon, 14 May 2001 10:17:09 -0400 (EDT) To: alexey@price.ru cc: pgsql-sql@postgresql.org Subject: Re: can't get rid of unnesesary SORT step in explain plan for hash join In-reply-to: <01051413453300.13379@workshop.price.ru> References: <01051118284600.06900@workshop.price.ru> <22028.989718836@sss.pgh.pa.us> <01051413453300.13379@workshop.price.ru> Comments: In-reply-to Alexey Nalbat message dated "Mon, 14 May 2001 11:36:17 +0400" Date: Mon, 14 May 2001 10:17:09 -0400 Message-ID: <8987.989849829@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200105/123 X-Sequence-Number: 2939 Alexey Nalbat 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