From: Arne Roland <A.Roland@index.de>
To: Richard Guo <riguo@pivotal.io>
Cc: pgsql-hackers@lists.postgresql.org <pgsql-hackers@lists.postgresql.org>
Subject: Re: Partial join
Date: Thu, 1 Aug 2019 11:46:08 +0000
Message-ID: <6a00c26703c24046afb2b690256715d4@index.de> (raw)
In-Reply-To: <CAN_9JTxQtJr2RHLKAfDCJoZFF+qsUMAF5JF+BVyAVcmcC2kSQA@mail.gmail.com>
References: <9dc03e949e9b4327a0d074da9ab44318@index.de>
<dd01626a46a342f7b1d811cf38fcebe6@index.de>
<CAN_9JTxQtJr2RHLKAfDCJoZFF+qsUMAF5JF+BVyAVcmcC2kSQA@mail.gmail.com>
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 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 Roland <A.Roland@index.de<mailto:A.Roland@index.de>> wrote:
Hello,
I attached one example of a partitioned table with multi column partition key. I also attached the output.
Disabling the hash_join is not really necessary, it just shows the more drastic result in the case of low work_mem.
Comparing the first and the second query I was surprised to see that SET enable_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 original performance problem. It looks to me like the push down of the sl condition stops the optimizer considering a partial join.
If so would it be sane to keep a copy of the original quals to make the partial join possible? Do you have better ideas?
For the third query, a rough investigation shows that, the qual 'sl =
5' and 'sc.sl<http://sc.sl; = sg.sl<http://sg.sl>'; will form an equivalence class and generate two
implied equalities: 'sc.sl<http://sc.sl; = 5' and 'sg.sl<http://sg.sl; = 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<http://sc.sl; = sg.sl<http://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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: A.Roland@index.de, riguo@pivotal.io, pgsql-hackers@lists.postgresql.org
Subject: Re: Partial join
In-Reply-To: <6a00c26703c24046afb2b690256715d4@index.de>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox