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

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. (The name of the flag
isn't good? If so, that would be my fault because I named that flag.)

> 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.

>> 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)

Best regards,
Etsuro Fujita