On Fri, Oct 31, 2025 at 4:57 AM Robert Haas <[email protected]> wrote: > And just like that, I found another way that this can happen. Consider > this query from the regression tests: > > SELECT t1.a, t1.c, t2.b, t2.c FROM prt1 t1, prt2 t2 WHERE t1.a = t2.a > AND t1.a = t2.b ORDER BY t1.a, t2.b;
I observed something interesting about this query. If you swap the two join conditions, you should theoretically get a semantically equivalent query. SELECT t1.a, t1.c, t2.b, t2.c FROM prt1 t1, prt2 t2 WHERE t1.a = t2.b AND t1.a = t2.a ORDER BY t1.a, t2.b; However, unlike the original query, the planner estimates that a partitionwise join is cheaper than a non-partitionwise join for this version, with costs of 12.74 and 14.24, respectively. What's more surprising is that the non-partitionwise plans for these two queries differ significantly in cost. The original query has an estimated cost of 7.86, while the modified version's cost is 14.24. This also indicates that the discrepancy is unrelated to partitionwise join. I looked into this a bit and traced it to mergejoinscansel(). This function estimates the value ranges of both inputs to determine how much of the input will actually be read, since a merge join can stop as soon as either input is exhausted. For the original query, the merge clause is "t1.a = t2.a", and the function estimates the maximum value of the right-side variable (t2.a) as 24. For the modified query, the merge clause becomes "t1.a = t2.b", and the estimated maximum value of the right-side variable (t2.b) is 597. This isn't actually incorrect given how get_variable_range() works: select max(a), max(b) from prt2; max | max -----+----- 24 | 597 (1 row) However, the logic overlooks the fact that t2.a is always constrained to be equal to t2.b, meaning their value ranges should be identical. I think we may need to do something here. However, it's a bit off-topic for this thread. I'm just noting it here in case anyone else is interested. - Richard
