Re: Removing [Merge]Append nodes which contain a single subpath

On Fri, Oct 27, 2017 at 12:49 AM, David Rowley
<david(dot)rowley(at)2ndquadrant(dot)com> wrote:
> On 27 October 2017 at 01:44, Ashutosh Bapat
> <ashutosh(dot)bapat(at)enterprisedb(dot)com> wrote:
>> I think Antonin has a point. The join processing may deem some base
>> relations dummy (See populate_joinrel_with_paths()). So an Append
>> relation which had multiple child alive at the end of
>> set_append_rel_size() might ultimately have only one child after
>> partition-wise join has worked on it.
>
> hmm, I see. partition-wise join is able to remove eliminate partitions
> on the other side of the join that can't be matched to:
>
> # set enable_partition_wise_join=on;
> SET
> # explain select count(*) from ab ab1 inner join ab ab2 on ab1.a=ab2.a
> where ab1.a between 1 and 2 and ab2.a between 100000000 and 100000001;
> QUERY PLAN
> ------------------------------------------------
> Aggregate (cost=0.00..0.01 rows=1 width=8)
> -> Result (cost=0.00..0.00 rows=0 width=0)
> One-Time Filter: false
> (3 rows)
>
> # \d+ ab
> Table "public.ab"
> Column | Type | Collation | Nullable | Default | Storage | Stats
> target | Description
> --------+---------+-----------+----------+---------+---------+--------------+-------------
> a | integer | | | | plain | |
> b | integer | | | | plain | |
> Partition key: RANGE (a)
> Partitions: ab_p1 FOR VALUES FROM (1) TO (100000000),
> ab_p2 FOR VALUES FROM (100000000) TO (200000000)
>

IIUC, ab1_p2 and ab2_p1 are marked dummy by constraint exclusion.
Because of partition-wise join when the corresponding partitions are
joined, their inner joins are marked dummy resulting in an empty join.
This isn't the case of a join processing marking one of the joining
relations dummy, that I was referring to. But I think it's relevant
here since partition-wise join might create single child joins this
way.

--
Best Wishes,
Ashutosh Bapat
EnterpriseDB Corporation
The Postgres Database Company