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-0005hj-8j 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-1j for pgsql-hackers@arkaria.postgresql.org; Thu, 01 Aug 2019 20:49:27 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1ht9XB-00064G-0r for pgsql-hackers@lists.postgresql.org; Thu, 01 Aug 2019 11:46:13 +0000 Received: from mail.index.de ([213.160.90.22]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1ht9X8-0000E5-4J for pgsql-hackers@lists.postgresql.org; Thu, 01 Aug 2019 11:46:12 +0000 Received: from localhost (localhost [127.0.0.1]) by mail.index.de (Postfix) with ESMTP id E8F90446A58; Thu, 1 Aug 2019 11:46:08 +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 abqc01Mn-QBk; Thu, 1 Aug 2019 11:46:08 +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 C46F244698E; Thu, 1 Aug 2019 11:46:08 +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 13:46:08 +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 13:46:08 +0200 From: Arne Roland To: Richard Guo CC: "pgsql-hackers@lists.postgresql.org" Subject: Re: Partial join Thread-Topic: Partial join Thread-Index: AQHVRhDNZ8hUeRNKjUmj3sfzUQx80qbl85QWgAATZwCAACNfrQ== Date: Thu, 1 Aug 2019 11:46:08 +0000 Message-ID: <6a00c26703c24046afb2b690256715d4@index.de> References: <9dc03e949e9b4327a0d074da9ab44318@index.de> , In-Reply-To: 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_6a00c26703c24046afb2b690256715d4indexde_" MIME-Version: 1.0 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --_000_6a00c26703c24046afb2b690256715d4indexde_ Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable Hello Richard, thanks for your quick reply. > We need to fix this. Do you have a better idea than just keeping the old quals - possibly just t= he ones that get eliminated - in a separate data structure? Is the push dow= n of quals the only case of elimination of quals, only counting the ones wh= ich happen before the restrict lists are generated? Regards Arne ________________________________ From: Richard Guo Sent: Thursday, August 1, 2019 1:14:44 PM To: Arne Roland Cc: pgsql-hackers@lists.postgresql.org Subject: Re: Partial join On Thu, Aug 1, 2019 at 5:38 PM Arne Roland > wrote: Hello, I attached one example of a partitioned table with multi column partition k= ey. I also attached the output. Disabling the hash_join is not really necessary, it just shows the more dra= stic result in the case of low work_mem. Comparing the first and the second query I was surprised to see that SET en= able_partitionwise_join could cause the costs to go up. Shouldn't the paths= of the first query be generated as well? The third query seems to have a different issue. That one is close to my or= iginal performance problem. It looks to me like the push down of the sl con= dition stops the optimizer considering a partial join. If so would it be sane to keep a copy of the original quals to make the par= tial join possible? Do you have better ideas? For the third query, a rough investigation shows that, the qual 'sl =3D 5' and 'sc.sl =3D sg.sl' will form an equivalen= ce 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. Thanks Richard --_000_6a00c26703c24046afb2b690256715d4indexde_ Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

Hello Richard,

thanks for your quick reply.


> We need to fix this.


Do you have a better idea than just keeping the old quals - possibly jus= t the ones that get eliminated - in a separate data structure? Is the push = down of quals the only case of elimination of quals, only counting the ones= which happen before the restrict lists are generated?


Regards

Arne

From: Richard Guo <riguo= @pivotal.io>
Sent: Thursday, August 1, 2019 1:14:44 PM
To: Arne Roland
Cc: pgsql-hackers@lists.postgresql.org
Subject: Re: Partial join
 

On Thu, Aug 1, 2019 at 5:38 PM Arne R= oland <A.Roland@index.de> wr= ote:
Hello,

I attached one example of a partitioned table with multi column partition k= ey. I also attached the output.
Disabling the hash_join is not really necessary, it just shows the more dra= stic result in the case of low work_mem.

Comparing the first and the second query I was surprised to see that SET en= able_partitionwise_join could cause the costs to go up. Shouldn't the paths= of the first query be generated as well?

The third query seems to have a different issue. That one is close to my or= iginal performance problem. It looks to me like the push down of the sl con= dition stops the optimizer considering a partial join.
If so would it be sane to keep a copy of the original quals to make the par= tial join possible? Do you have better ideas?

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 s= g.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.

Thanks
Richard
--_000_6a00c26703c24046afb2b690256715d4indexde_--