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.
