Re: Partial join - Mailing list pgsql-hackers

From Richard Guo
Subject Re: Partial join
Date
Msg-id CAN_9JTwfWwkY6gMZZjn8vj5gP2U3=MiiU7VksDpahHXxoyeYpw@mail.gmail.com
Whole thread Raw
In response to Re: Partial join  (Tom Lane <tgl@sss.pgh.pa.us>)
List pgsql-hackers

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

pgsql-hackers by date:

Previous
From: Thomas Munro
Date:
Subject: A couple of random BF failures in kerberosCheck
Next
From: Richard Guo
Date:
Subject: Re: Partial join