pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedPartial join
9+ messages / 3 participants
[nested] [flat]
* Partial join
@ 2019-07-29 16:43 Arne Roland <A.Roland@index.de>
0 siblings, 1 reply; 9+ messages in thread
From: Arne Roland @ 2019-07-29 16:43 UTC (permalink / raw)
To: pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
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?
Regards
Arne
Attachments:
[application/sql] querys.sql (2.3K, ../../9dc03e949e9b4327a0d074da9ab44318@index.de/3-querys.sql)
download
[application/sql] explain_analyze.sql (7.7K, ../../9dc03e949e9b4327a0d074da9ab44318@index.de/4-explain_analyze.sql)
download
^ permalink raw reply [nested|flat] 9+ messages in thread
* Partial join
@ 2019-08-01 08:07 Arne Roland <A.Roland@index.de>
parent: Arne Roland <A.Roland@index.de>
0 siblings, 1 reply; 9+ messages in thread
From: Arne Roland @ 2019-08-01 08:07 UTC (permalink / raw)
To: pgsql-hackers@lists.postgresql.org <pgsql-hackers@lists.postgresql.org>
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?
Regards
Arne
Attachments:
[application/sql] querys.sql (2.3K, ../../dd01626a46a342f7b1d811cf38fcebe6@index.de/3-querys.sql)
download
[application/sql] explain_analyze.sql (7.7K, ../../dd01626a46a342f7b1d811cf38fcebe6@index.de/4-explain_analyze.sql)
download
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Partial join
@ 2019-08-01 11:14 Richard Guo <riguo@pivotal.io>
parent: Arne Roland <A.Roland@index.de>
0 siblings, 2 replies; 9+ messages in thread
From: Richard Guo @ 2019-08-01 11:14 UTC (permalink / raw)
To: Arne Roland <A.Roland@index.de>; +Cc: pgsql-hackers@lists.postgresql.org <pgsql-hackers@lists.postgresql.org>
On Thu, Aug 1, 2019 at 5:38 PM Arne Roland <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 = sg.sl' will form an equivalence class and generate two
implied equalities: 'sc.sl = 5' and '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 = 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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Partial join
@ 2019-08-01 11:46 Arne Roland <A.Roland@index.de>
parent: Richard Guo <riguo@pivotal.io>
1 sibling, 1 reply; 9+ messages in thread
From: Arne Roland @ 2019-08-01 11:46 UTC (permalink / raw)
To: Richard Guo <riguo@pivotal.io>; +Cc: pgsql-hackers@lists.postgresql.org <pgsql-hackers@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Partial join
@ 2019-08-01 14:14 Tom Lane <tgl@sss.pgh.pa.us>
parent: Richard Guo <riguo@pivotal.io>
1 sibling, 2 replies; 9+ messages in thread
From: Tom Lane @ 2019-08-01 14:14 UTC (permalink / raw)
To: Richard Guo <riguo@pivotal.io>; +Cc: Arne Roland <A.Roland@index.de>; pgsql-hackers@lists.postgresql.org <pgsql-hackers@lists.postgresql.org>
Richard Guo <riguo@pivotal.io> writes:
> For the third query, a rough investigation shows that, the qual 'sl =
> 5' and 'sc.sl = sg.sl' will form an equivalence class and generate two
> implied equalities: 'sc.sl = 5' and '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 = 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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Partial join
@ 2019-08-01 16:29 Arne Roland <A.Roland@index.de>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 9+ messages in thread
From: Arne Roland @ 2019-08-01 16:29 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; Richard Guo <riguo@pivotal.io>; +Cc: pgsql-hackers@lists.postgresql.org <pgsql-hackers@lists.postgresql.org>
"Tom Lane" <tgl@sss.pgh.pa.us> 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 = sg.sl' is now replaced by 'sg.sl = 5' so the join condition can no longer be validated.
It's true that the pruning would prune everything but one partition, in case we'd just have a single column partition key. But we don't. I don't see how pruning partitions should help in this case, since we are left with multiple partitions for both relations.
Regards
Arne
________________________________
From: Tom Lane <tgl@sss.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 =
> 5' and 'sc.sl = sg.sl' will form an equivalence class and generate two
> implied equalities: 'sc.sl = 5' and '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 = 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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Partial join
@ 2019-08-02 09:33 Richard Guo <riguo@pivotal.io>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 9+ messages in thread
From: Richard Guo @ 2019-08-02 09:33 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Arne Roland <A.Roland@index.de>; pgsql-hackers@lists.postgresql.org <pgsql-hackers@lists.postgresql.org>
On Thu, Aug 1, 2019 at 10:15 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Richard Guo <riguo@pivotal.io> writes:
> > For the third query, a rough investigation shows that, the qual 'sl =
> > 5' and 'sc.sl = sg.sl' will form an equivalence class and generate two
> > implied equalities: 'sc.sl = 5' and '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 = 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.
>
Hmm..In the case of multiple partition keys, for range partitioning, if
we have no clauses for a given key, any later keys would not be
considered for partition pruning.
That is to day, for table 'p partition by range (k1, k2)', quals like
'k2 = Const' would not prune partitions.
For query:
select * from p as t1 join p as t2 on t1.k1 = t2.k1 and t1.k2 = t2.k2
and t1.k2 = 2;
Since we don't consider ECs containing consts when generating join
clauses, we don't have restriction 't1.k2 = t2.k2' when building the
joinrel. As a result, partitionwise join is not considered as it
requires there existing an equi-join condition for each pair of
partition keys.
Is this a problem? What's your opinion?
Thanks
Richard
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Partial join
@ 2019-08-02 10:00 Richard Guo <riguo@pivotal.io>
parent: Arne Roland <A.Roland@index.de>
0 siblings, 1 reply; 9+ messages in thread
From: Richard Guo @ 2019-08-02 10:00 UTC (permalink / raw)
To: Arne Roland <A.Roland@index.de>; +Cc: pgsql-hackers@lists.postgresql.org <pgsql-hackers@lists.postgresql.org>
On Thu, Aug 1, 2019 at 7:46 PM Arne Roland <A.Roland@index.de> wrote:
> 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?
>
In you case, the restriction 'sl = sl' is just not generated for the
join, because it forms an EC with const, which is not considered when
generating join clauses.
Please refer to the code snippet below:
@@ -1164,8 +1164,8 @@ generate_join_implied_equalities(PlannerInfo *root,
List *sublist = NIL;
/* ECs containing consts do not need any further
enforcement */
if (ec->ec_has_const)
continue;
Thanks
Richard
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Partial join
@ 2019-08-19 15:17 Arne Roland <A.Roland@index.de>
parent: Richard Guo <riguo@pivotal.io>
0 siblings, 0 replies; 9+ messages in thread
From: Arne Roland @ 2019-08-19 15:17 UTC (permalink / raw)
To: Richard Guo <riguo@pivotal.io>; +Cc: pgsql-hackers@lists.postgresql.org <pgsql-hackers@lists.postgresql.org>
Richard Guo <riguo@pivotal.io> wrote:
> Please refer to the code snippet below:
>
> @@ -1164,8 +1164,8 @@ generate_join_implied_equalities(PlannerInfo *root,
> List *sublist = NIL;
>
> /* ECs containing consts do not need any further enforcement */
> if (ec->ec_has_const)
> continue;
Sorry, I'm quite busy currently. And thanks! That was a good read.
I might be wrong, but I think have_partkey_equi_join in joinrels.c should be aware of the const case. My naive approach would be keeping pointers to the first few constant clauses, which are referencing to a yet unmatched partition key, to keep the memory footprint feasible in manner similar to pk_has_clause. The question would be what to do, if there are a lot of const expressions on the part keys. One could palloc additional memory in that case, hoping that it will be quite rare. Or is there a different, better way to go about that?
Thank you for your feedback!
Regards
Arne
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2019-08-19 15:17 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2019-07-29 16:43 Partial join Arne Roland <A.Roland@index.de>
2019-08-01 08:07 ` Arne Roland <A.Roland@index.de>
2019-08-01 11:14 ` Richard Guo <riguo@pivotal.io>
2019-08-01 11:46 ` Arne Roland <A.Roland@index.de>
2019-08-02 10:00 ` Richard Guo <riguo@pivotal.io>
2019-08-19 15:17 ` Arne Roland <A.Roland@index.de>
2019-08-01 14:14 ` Tom Lane <tgl@sss.pgh.pa.us>
2019-08-01 16:29 ` Arne Roland <A.Roland@index.de>
2019-08-02 09:33 ` Richard Guo <riguo@pivotal.io>
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