On Fri, Oct 31, 2025 at 2:40 PM Robert Haas <[email protected]> wrote: > Thoughts?
I guess not. I spent a bunch more time investigating what was going on with the one problematic test case that I mentioned in my previous email. I discovered that my prior analysis missed a key point about this test case, which is that it involves a row-count inflating join. It uses "t1.c = t2.c" and "t1.c = t3.c" as join clauses, but the tables have 15-25 rows and there are only 5 possible values for column c. So, rows have multiple join partners. What this means is that the when we pick a partitionwise plan, the total number of rows that we estimate will need to be processed by an Append node is much greater. In the non-partitionwise plan, we need to append each pair of partitions, so the total number of rows that pass through Append across the whole plan tree is estimated as 15+15+25=55. In the partitionwise plan, the joins are performed first, increasing the row count because of the multiple join partners, and then we estimate we'll need to Append 42+250=292 rows. Therefore, the planner is actually being reasonable to choose the non-partitionwise plan: avoiding pushing a lot of extra rows through an Append node is a legitimate choice. However, it defeats the purpose of the test case. So, I just added added more join clauses, joining on "t1.a = t2.a AND t1.c = t2.c" and then on "t1.a = t3.a AND t1.c = t3.c". This preserves the original plan shape by removing the row-count inflation from the plan, allowing the partitionwise plan to win as before. I'm pretty happy with the resulting patch, and plan to commit it (only to master) if nobody has any complaints. Please let me know if you have complaints. -- Robert Haas EDB: http://www.enterprisedb.com
v4-0001-Don-t-reset-the-pathlist-of-partitioned-joinrels.patch
Description: Binary data
