Subject: Re: Support run-time partition pruning for hash join

From: Richard Guo <[email-masked]>
Date: Tue, 7 Nov 2023 15:55:16 +0800

On Mon, Nov 6, 2023 at 11:00 PM Alexander Lakhin <[email-masked]>
wrote:

> Please look at a warning and an assertion failure triggered by the
> following script:
> set parallel_setup_cost = 0;
> set parallel_tuple_cost = 0;
> set min_parallel_table_scan_size = '1kB';
>
> create table t1 (i int) partition by range (i);
> create table t1_1 partition of t1 for values from (1) to (2);
> create table t1_2 partition of t1 for values from (2) to (3);
> insert into t1 values (1), (2);
>
> create table t2(i int);
> insert into t2 values (1), (2);
> analyze t1, t2;
>
> select * from t1 right join t2 on t1.i = t2.i;
>
> 2023-11-06 14:11:37.398 UTC|law|regression|6548f419.392cf5|WARNING:  Join
> partition pruning $0 has not been performed yet.
> TRAP: failed Assert("node->as_prune_state"), File: "nodeAppend.c", Line:
> 846, PID: 3747061
>

Thanks for the report!  I failed to take care of the parallel-hashjoin
case, and I have to admit that it's not clear to me yet how we should do
join partition pruning in that case.

For now I think it's better to just avoid performing join partition
pruning for parallel hashjoin, so that the patch doesn't become too
complex for review.  We can always extend it in the future.

I have done that in v5.  Thanks for testing!

Thanks
Richard

---

From: vignesh C <[email-masked]>
Date: Sat, 27 Jan 2024 08:59:04 +0530

On Tue, 7 Nov 2023 at 13:25, Richard Guo <[email-masked]> wrote:
>
>
> On Mon, Nov 6, 2023 at 11:00 PM Alexander Lakhin <[email-masked]> wrote:
>>
>> 2023-11-06 14:11:37.398 UTC|law|regression|6548f419.392cf5|WARNING:  Join partition pruning $0 has not been performed yet.
>> TRAP: failed Assert("node->as_prune_state"), File: "nodeAppend.c", Line: 846, PID: 3747061
>
>

CFBot shows that the patch does not apply anymore as in [1]:
=== Applying patches on top of PostgreSQL commit ID
924d046dcf55887c98a1628675a30f4b0eebe556 ===
=== applying patch
./v5-0001-Support-run-time-partition-pruning-for-hash-join.patch
...
patching file src/include/nodes/plannodes.h
...
patching file src/include/optimizer/cost.h
Hunk #1 FAILED at 211.
1 out of 1 hunk FAILED -- saving rejects to file
src/include/optimizer/cost.h.rej

Please post an updated version for the same.

[1] - http://cfbot.cputube.org/patch_46_4512.log

Regards,
Vignesh

---

From: Richard Guo <[email-masked]>
Date: Tue, 30 Jan 2024 10:33:19 +0800

> Please post an updated version for the same.

Attached is an updated patch.  Nothing else has changed.

Thanks
Richard

---

From: Richard Guo <[email-masked]>
Date: Tue, 19 Mar 2024 14:12:34 +0800

> Attached is an updated patch.  Nothing else has changed.

Here is another rebase over master so it applies again.  Nothing else
has changed.

Thanks
Richard

---

From: Andrei Lepikhov <[email-masked]>
Date: Thu, 5 Sep 2024 09:56:55 +0200

On 19/3/2024 07:12, Richard Guo wrote:
> 
> On Tue, Jan 30, 2024 at 10:33 AM Richard Guo <[email-masked] 
> <mailto:[email-masked]>> wrote:
> 
The patch doesn't apply to the master now.
I wonder why this work was suppressed - it looks highly profitable in 
the case of foreign partitions. And the idea of cost-based enablement 
makes it a must-have, I think.
I have just skimmed through the patch and have a couple of questions:
1. It makes sense to calculate the cost and remember the minimum number 
of pruned partitions when the cost of HJ with probing is still 
profitable. Why don't we disable this probing in runtime if we see that 
the number of potentially pruning partitions is already too low?
2. Maybe I misunderstood the code, but having matched a hashed tuple 
with a partition, it makes sense for further tuples to reduce the number 
of probing expressions because we already know that the partition will 
not be pruned.

-- 
regards, Andrei Lepikhov