Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1Wjnjq-0002IB-7T for pgsql-performance@arkaria.postgresql.org; Mon, 12 May 2014 10:45:42 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1Wjnjp-00070r-95 for pgsql-performance@arkaria.postgresql.org; Mon, 12 May 2014 10:45:41 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1Wjnjn-00070i-2T for pgsql-performance@postgresql.org; Mon, 12 May 2014 10:45:39 +0000 Received: from mail-we0-x22c.google.com ([2a00:1450:400c:c03::22c]) by magus.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1Wjnje-0003gF-NC for pgsql-performance@postgresql.org; Mon, 12 May 2014 10:45:38 +0000 Received: by mail-we0-f172.google.com with SMTP id k48so6707666wev.17 for ; Mon, 12 May 2014 03:45:29 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=user-agent:date:subject:from:to:message-id:thread-topic :mime-version:content-type; bh=vM616Tjy1plYiMeYaobogSmJln21PW0FvuyUjE88PhE=; b=1HSocH3Jp9VeZmq2e49uVS/8bnYUTKKcCOo19HFQvZxMveJn8/cN+xj4SUUdD3ioYc f7LjzHVWr8pJfqUJ0mV+lQXuhQ5naIF/aP2IoQ0PjeraLJy2IYZWSMyQng6EdfxWHV6G 7Qoy7EuX3efxfWh8I9aDCTQw0LvV9YY/tEeXmrBwBNpKBEB0ndYuTBFE++rJTp+nvrQu FXR6U31r+d/rWapcNvFpRiQSbWtQcKn5gmCqZcnmGHBUeII1VApzDskbXqqgJqNgy4sA vqrH1vWHHvATRcIxyj3wMKYtcGB8v6tj8RP9mDbA0Rme4oOuqv2q+lePbbGmClVQDt+4 3F5Q== X-Received: by 10.180.91.1 with SMTP id ca1mr15228439wib.32.1399891529358; Mon, 12 May 2014 03:45:29 -0700 (PDT) Received: from [192.168.0.28] (cpc14-dals19-2-0-cust193.20-2.cable.virginm.net. [62.31.88.194]) by mx.google.com with ESMTPSA id dk4sm5292416wib.14.2014.05.12.03.45.23 for (version=TLSv1 cipher=RC4-SHA bits=128/128); Mon, 12 May 2014 03:45:28 -0700 (PDT) User-Agent: Microsoft-MacOutlook/14.4.1.140326 Date: Mon, 12 May 2014 11:45:20 +0100 Subject: 9.2.4 vs 9.3.0 query planning (sort merge join vs hash join) From: Tim Kane To: "pgsql-performance@postgresql.org" Message-ID: Thread-Topic: 9.2.4 vs 9.3.0 query planning (sort merge join vs hash join) Mime-version: 1.0 Content-type: multipart/alternative; boundary="B_3482739927_64376209" X-Pg-Spam-Score: -2.0 (--) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org > This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible. --B_3482739927_64376209 Content-type: text/plain; charset="UTF-8" Content-transfer-encoding: quoted-printable Hi all, Is there a propensity for 9.2.4 to prefer a sort-merge-join, in place of a hash join? I=E2=80=99m fairly sure the answer is yes, but I also want to be sure I=E2=80=99m interpreting the explain output correctly. I=E2=80=99m comparing behaviour between two systems, which for all intents and purposes are identical save for the version of postgres. There appears to be nothing wrong with the row estimates given in the explain plan on either machine, however actual performance is significantly impaired on the 9.2.4 setup due to the preference for the use of a sort-merge join, compared to a hash-join on 9.3.0 Snippet from 9.2.4 -> Merge Left Join (cost=3D19598754.29..19602284.00 rows=3D469996 width=3D434) (actual time=3D6369152.750..6386029.191 rows=3D6866896 loops=3D1) Buffers: shared hit=3D489837 read=3D1724585 -> Sort (cost=3D19598650.62..19599825.61 rows=3D469996 width=3D120) (actual time=3D6369151.307..6373591.881 rows=3D6866896 loops=3D1) (A) Sort Method: quicksort Memory: 1162266kB Buffers: shared hit=3D489765 read=3D1724585 -> Hash Left Join (cost=3D429808.90..19554371.62 rows=3D469996 width=3D120) (actual time=3D37306.534..6353455.046 rows=3D6866896 loops=3D1) (B) Rows Removed by Filter: 20862464 Buffers: shared hit=3D489765 read=3D1724585 Snippet from 9.3.0 -> Hash Left Join (cost=3D617050.43..20948535.43 rows=3D566893 width=3D434) (actual time=3D51816.864..934723.548 rows=3D6866896 loops=3D1) Buffers: shared hit=3D1732 read=3D2010920 written=3D1 -> Hash Left Join (cost=3D616993.23..20870529.73 rows=3D566893 width=3D120) (actual time=3D51796.882..923196.579 rows=3D6866896 loops=3D1) Rows Removed by Filter: 20862464 Buffers: shared hit=3D1732 read=3D2010877 written=3D1 As you can see, the estimates are similar enough between them, but 9.2.4 want=E2=80=99s to run sort-merge plan (A) =E2=80=93 and the resulting execution time bl= ows out hugely. Intersetingly, it actually looks like it is the hash join immediately preceding the quick sort that isn=E2=80=99t performing well (B). Though I suspe= ct this is just how an explain plan reads - is this ultimately because the sor= t node is unable to retrieve tuples from the child node quickly enough? Setting enable_mergejoin =3D 0 appears to solve this, but I think an upgrade to 9.3.4 is going to win over. Cheers, Tim --B_3482739927_64376209 Content-type: text/html; charset="UTF-8" Content-transfer-encoding: quoted-printable
Hi all,

Is the= re a propensity for 9.2.4 to prefer a sort-merge-join, in place of a hash jo= in?

I’m fairly sure the answer is yes, but I also want to be su= re I’m interpreting the explain output correctly.

I’m com= paring behaviour between two systems, which for all intents and purposes are= identical save for the version of postgres.
There appears to be nothing wrong with the row estimates given in th= e explain plan on either machine, however actual performance is significantl= y impaired on the 9.2.4 setup due to the preference for the use of a sort-me= rge join, compared to a hash-join on 9.3.0



Snippet from 9= .2.4
       =              ->  Merge Left Join=  (cost=3D19598754.29..19602284.00 rows=3D469996 width=3D434) (actual time=3D63= 69152.750..6386029.191 rows=3D6866896 loops=3D1)
                   = ;        Buffers: shared hit=3D489837 read=3D1724585
    =                      =  ->  Sort  (cost=3D19598650.62..19599825.61 rows=3D469996 wid= th=3D120) (actual time=3D6369151.307..6373591.881 rows=3D6866896 loops=3D1)   &= nbsp;               (A)
<= div style=3D"color: rgb(0, 0, 0);">    &nbs= p;                     &nb= sp;      Sort Method: quicksort  Memory: 1162266kB
        &nb= sp;                     &n= bsp;  Buffers: shared hit=3D489765 read=3D1724585
    &= nbsp;                     =        ->  Hash Left Join  (cost=3D429808.90.= .19554371.62 rows=3D469996 width=3D120) (actual time=3D37306.534..6353455.046 rows= =3D6866896 loops=3D1)        (B)
        =                      =          Rows Removed by Filter: 20862464
            &nb= sp;                     &n= bsp;    Buffers: shared hit=3D489765 read=3D1724585

Snipp= et from 9.3.0

              &= nbsp;      ->  Hash Left Join  (cost=3D617050.43..= 20948535.43 rows=3D566893 width=3D434) (actual time=3D51816.864..934723.548 rows=3D6= 866896 loops=3D1)
              &= nbsp;            Buffers: shared hit=3D1732 read= =3D2010920 written=3D1
      &nbs= p;                    ->= ;  Hash Left Join  (cost=3D616993.23..20870529.73 rows=3D566893 width=3D= 120) (actual time=3D51796.882..923196.579 rows=3D6866896 loops=3D1)
                    &nb= sp;            Rows Removed by Filter: 2086246= 4
                  &= nbsp;              Buffers: shared hit=3D17= 32 read=3D2010877 written=3D1

<= /div>

As you can see, the estimates are similar enough between them, but 9.= 2.4 want’s to run sort-merge plan (A) – and the resulting execut= ion time blows out hugely.
Intersetin= gly, it actually looks like it is the hash join immediately preceding the qu= ick sort that isn’t performing well (B).   Though I suspect this = is just how an explain plan reads - is this ultimately because the sort node= is unable to retrieve tuples from the child node quickly enough?

Setting enable_mergejoin =3D = 0 appears to solve this, but I think an upgrade to 9.3.4 is going to win= over.

Cheers,

Tim

=






=



--B_3482739927_64376209--