Re: Query with high planning time at version 11.1 compared versions 10.5 and 11.0

Fujita-san,

On 2019/01/10 15:07, Etsuro Fujita wrote:
> Amit-san,
>
> (2019/01/10 10:41), Amit Langote wrote:
>> On 2019/01/09 20:20, Etsuro Fujita wrote:
>>> I like your patch in general.  I think one way to address Ashutosh's
>>> concerns would be to use the consider_partitionwise_join flag: originally,
>>> that was introduced for partitioned relations to show that they can be
>>> partitionwise-joined, but I think that flag could also be used for
>>> non-partitioned relations to show that they have been set up properly for
>>> partitionwise-joins, and I think by checking that flag we could avoid
>>> creating those copies for child dummy rels in try_partitionwise_join.
>>
>> Ah, that's an interesting idea.
>>
>> If I understand the original design of it correctly,
>> consider_partitionwise_join being true for a given relation (simple or
>> join) means that its RelOptInfo contains properties to consider it to be
>> joined with another relation (simple or join) using partitionwise join
>> mechanism.  Partitionwise join will occur between the pair if the other
>> relation also has relevant properties (hence its
>> consider_partitionwise_join set to true) and properties on the two sides
>> match.
>
> Actually, the flag being true just means that the tlist for a given
> partitioned relation (simple or join) doesn't contain any whole-row Vars. 
> And if two given partitioned relations having the flag being true have
> additional properties to be joined using the PWJ technique, then we try to
> do PWJ for those partitioned relations.

I see. Thanks for the explanation.

> (The name of the flag isn't
> good?  If so, that would be my fault because I named that flag.)

If it's really just to store the fact that the relation's targetlist
contains expressions that partitionwise join currently cannot handle, then
setting it like this in set_append_rel_size seems a bit misleading:

if (enable_partitionwise_join &&
rel->reloptkind == RELOPT_BASEREL &&
rte->relkind == RELKIND_PARTITIONED_TABLE &&
rel->attr_needed[InvalidAttrNumber - rel->min_attr] == NULL)
rel->consider_partitionwise_join = true;

Sorry, I wasn't around to comment on the patch which got committed in
7cfdc77023a, but checking the value of enable_partitionwise_join and other
things in set_append_rel_size() to set the value of
consider_partitionwise_join seems a bit odd to me. Perhaps,
consider_partitionwise_join should be initially set to true for a relation
(actually, to rel->part_scheme != NULL) and only set it to false if the
relation's targetlist is found to contain unsupported expressions. That
way, it becomes easier to think what it means imho. I think
enable_partitionwise_join should only be checked in relnode.c or
joinrels.c. I've attached a patch to show what I mean. Can you please
take a look?

If you think that this patch is a good idea, then you'll need to
explicitly set consider_partitionwise_join to false for a dummy partition
rel in set_append_rel_size(), because the assumption of your patch that
such partition's rel's consider_partitionwise_join would be false (as
initialized with the current code) would be broken by my patch. But that
might be a good thing to do anyway as it will document the special case
usage of consider_partitionwise_join variable more explicitly, assuming
you'll be adding a comment describing why it's being set to false explicitly.

>> That's a loaded meaning and abusing it to mean something else can be
>> challenged, but we can live with that if properly documented.  Speaking of
>> which:
>>
>>      /* used by partitionwise joins: */
>>      bool        consider_partitionwise_join;    /* consider
>> partitionwise join
>>                                                   * paths? (if partitioned
>> rel) */
>>
>> Maybe, mention here how it will be abused in back-branches for
>> non-partitioned relations?
>
> Will do.

Thank you.

>>> Please find attached an updated version of the patch.  I modified your
>>> version so that building tlists for child dummy rels are also postponed
>>> until after they actually participate in partitionwise-joins, to avoid
>>> that possibly-useless work as well.  I haven't done any performance tests
>>> yet though.
>>
>> Thanks for updating the patch.  I tested your patch (test setup described
>> below) and it has almost the same performance as my previous version:
>> 552ms (vs. 41159ms on HEAD vs. 253ms on PG 10) for the query also
>> mentioned below.
>
> Thanks for that testing!
>
> I also tested the patch with your script:
>
> 253.559 ms (vs. 85776.515 ms on HEAD vs. 206.066 ms on PG 10)

Oh, PG 11 doesn't appear as bad compared to PG 10 with your numbers as it
did on my machine. That's good anyway.

Thanks,
Amit