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

On Thu, Jan 10, 2019 at 7:12 AM Amit Langote <Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp>
wrote:

> Fujita-san,
>
> On 2019/01/09 20:20, Etsuro Fujita wrote:
> > (2019/01/09 9:30), Amit Langote wrote:
> >> So, while the patch at [1] can take care of this issue as I also
> mentioned
> >> upthread, I was trying to come up with a solution that can be
> back-patched
> >> to PG 11. The patch I posted above is one such solution and as Ashutosh
> >> points out it's perhaps not the best, because it can result in
> potentially
> >> creating many copies of the same child EC member if we do it in
> joinrel.c,
> >> as the patch proposes. I will try to respond to the concerns he raised
> in
> >> the next week if possible.
> >
> > Thanks for working on this!
> >
> > 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.
>
>
Though this will solve a problem for performance when partition-wise join
is not possible, we still have the same problem when partition-wise join is
possible. And that problem really happens because our inheritance mechanism
requires expression translation from parent to child everywhere. That
consumes memory, eats CPU cycles and generally downgrades performance of
partition related query planning. I think a better way would be to avoid
these translations and use Parent var to represent a Var of the child being
dealt with. That will be a massive churn on inheritance based planner code,
but it will improve planning time for queries involving thousands of
partitions.

--
Best Wishes,
Ashutosh Bapat