Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1htI0u-0005hk-8p for pgsql-hackers@arkaria.postgresql.org; Thu, 01 Aug 2019 20:49:28 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1htI0t-0008Hq-28 for pgsql-hackers@arkaria.postgresql.org; Thu, 01 Aug 2019 20:49:27 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1htDxO-000584-OU for pgsql-hackers@lists.postgresql.org; Thu, 01 Aug 2019 16:29:34 +0000 Received: from mail.index.de ([213.160.90.22]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.92) (envelope-from ) id 1htDxH-0000DQ-Hi for pgsql-hackers@lists.postgresql.org; Thu, 01 Aug 2019 16:29:33 +0000 Received: from localhost (localhost [127.0.0.1]) by mail.index.de (Postfix) with ESMTP id CE906446AC2; Thu, 1 Aug 2019 16:29:24 +0000 (UTC) Received: from mail.index.de ([127.0.0.1]) by localhost (mail.index.de [127.0.0.1]) (amavisd-new, port 10024) with LMTP id tk820mQQra-Q; Thu, 1 Aug 2019 16:29:24 +0000 (UTC) Received: from remote.index.de (unknown [217.199.72.165]) (using TLSv1.2 with cipher ECDHE-RSA-AES128-GCM-SHA256 (128/128 bits)) (No client certificate requested) by mail.index.de (Postfix) with ESMTPSA id A16D2446ABE; Thu, 1 Aug 2019 16:29:24 +0000 (UTC) Received: from acedia.index-gruppe.local (192.168.3.121) by acedia.index-gruppe.local (192.168.3.121) with Microsoft SMTP Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256) id 15.1.1034.26; Thu, 1 Aug 2019 18:29:22 +0200 Received: from acedia.index-gruppe.local ([fe80::ecd1:5d0a:a834:dd56]) by acedia.index-gruppe.local ([fe80::ecd1:5d0a:a834:dd56%5]) with mapi id 15.01.1034.033; Thu, 1 Aug 2019 18:29:22 +0200 From: Arne Roland To: Tom Lane , Richard Guo CC: "pgsql-hackers@lists.postgresql.org" Subject: Re: Partial join Thread-Topic: Partial join Thread-Index: AQHVRhDNZ8hUeRNKjUmj3sfzUQx80qbl85QWgAATZwCAADJXAIAARPHE Date: Thu, 1 Aug 2019 16:29:21 +0000 Message-ID: <563d1a984c2c41b5b126318aafceab11@index.de> References: <9dc03e949e9b4327a0d074da9ab44318@index.de> ,<5928.1564668894@sss.pgh.pa.us> In-Reply-To: <5928.1564668894@sss.pgh.pa.us> Accept-Language: en-US, de-DE Content-Language: en-US X-MS-Has-Attach: X-MS-TNEF-Correlator: x-originating-ip: [192.168.3.146] Content-Type: multipart/alternative; boundary="_000_563d1a984c2c41b5b126318aafceab11indexde_" MIME-Version: 1.0 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --_000_563d1a984c2c41b5b126318aafceab11indexde_ Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable "Tom Lane" wrote: > Uh ... why? The pushed-down restrictions should result in pruning > away any prunable partitions at the scan level, leaving nothing for > the partitionwise join code to do. It seems reasonable to me that the join condition can no longer be verified= , since 'sc.sl =3D sg.sl' is now replaced by 'sg.sl =3D 5' so the join con= dition can no longer be validated. It's true that the pruning would prune everything but one partition, in cas= e we'd just have a single column partition key. But we don't. I don't see h= ow pruning partitions should help in this case, since we are left with mult= iple partitions for both relations. Regards Arne ________________________________ From: Tom Lane Sent: Thursday, August 1, 2019 4:14:54 PM To: Richard Guo Cc: Arne Roland; pgsql-hackers@lists.postgresql.org Subject: Re: Partial join Richard Guo writes: > For the third query, a rough investigation shows that, the qual 'sl =3D > 5' and 'sc.sl =3D sg.sl' will form an equivalence class and generate two > implied equalities: 'sc.sl =3D 5' and 'sg.sl =3D 5', which can be pushed > down to the base rels. One consequence of the deduction is when > constructing restrict lists for the joinrel, we lose the original > restrict 'sc.sl =3D sg.sl', and this would fail the check > have_partkey_equi_join(), which checks if there exists an equi-join > condition for each pair of partition keys. As a result, this joinrel > would not be considered as an input to further partitionwise joins. > We need to fix this. Uh ... why? The pushed-down restrictions should result in pruning away any prunable partitions at the scan level, leaving nothing for the partitionwise join code to do. regards, tom lane --_000_563d1a984c2c41b5b126318aafceab11indexde_ Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable
"Tom Lane" <tgl@sss.pgh.pa.us> wrote:
> Uh ... why?  The pushed-down restrictions should result in p= runing
> away any prunable partitions at the scan level, leaving nothing f= or
> the partitionwise join code to do.

It seems reasonable to me that the join condition can no longer be ver= ified, since  'sc.sl =3D sg.sl' is now replaced by 'sg.sl =3D 5' so th= e join condition can no longer be validated.

It's true that the pruning would prune everything but one partition, in cas= e we'd just have a single column partition key. But we don't. I don't see h= ow pruning partitions should help in this case, since we are left with mult= iple partitions for both relations.

Regards
Arne

From: Tom Lane <tgl@ss= s.pgh.pa.us>
Sent: Thursday, August 1, 2019 4:14:54 PM
To: Richard Guo
Cc: Arne Roland; pgsql-hackers@lists.postgresql.org
Subject: Re: Partial join
 
Richard Guo <riguo@pivotal.io> writes:
> For the third query,  a rough investigation shows that, the qual = 'sl =3D
> 5' and 'sc.sl =3D sg.sl' will form an equivalence class and generate t= wo
> implied equalities: 'sc.sl =3D 5' and 'sg.sl =3D 5', which can be push= ed
> down to the base rels. One consequence of the deduction is when
> constructing restrict lists for the joinrel, we lose the original
> restrict 'sc.sl =3D sg.sl', and this would fail the check
> have_partkey_equi_join(), which checks if there exists an equi-join > condition for each pair of partition keys. As a result, this joinrel > would not be considered as an input to further partitionwise joins.
> We need to fix this.

Uh ... why?  The pushed-down restrictions should result in pruning
away any prunable partitions at the scan level, leaving nothing for
the partitionwise join code to do.

            &nb= sp;           regards, to= m lane



 --_000_563d1a984c2c41b5b126318aafceab11indexde_--