Thanks for the review and for tightening this up. v3 looks good to me. The nvalues == 1 restriction on the first-key MINVALUE/MAXVALUE trim is a clear improvement: the leading single-key step already trims the default there, and the BTEqualStrategyNumber block handles the multi-key prefixes, so the intersection removes the default without repeating the first-key check in every step. Your comment rewrite explains the interaction much better than my original; agreed it was too thin.
Happy with the adjustments -- thanks for picking this up. On Fri, Sep 18, 2026 at 4:55 AM David Rowley <[email protected]> wrote: > > On Thu, 6 Aug 2026 at 22:47, Ewan Young <[email protected]> wrote: > > 0001 tests the first key of the bound instead of the last key of the lookup > > value; the code change is two lines. I did not delete the blocks the way > > 709dfd27f14 did, because when the first key really is unbounded there is > > nothing beyond it and removing them would scan the default partition for no > > reason. Tests cover a trailing MAXVALUE, the MINVALUE mirror, a genuinely > > unbounded first key, and the no-default case; the last two are unchanged by > > the patch, on purpose. > > > > 0001 alone gives up one pruning opportunity: "WHERE a = 19 AND b >= 5" now > > also scans the default partition. That pruning was unsound where it > > happened - "WHERE a >= 19 AND b >= 5" makes the identical call (strategy > > >=, values (19, 5)), and there the rows above the bound do qualify; master > > answers it with 2 rows where 3 is correct. The function cannot tell the > > two apart. > > > > 0002 recovers it where the knowledge exists. In the equality-prefix path > > the code already skips the offset below the smallest matching bound when > > that bound is (prefix, MINVALUE); the mirror was missing, so a greatest > > matching bound of (prefix, MAXVALUE) still pulled in the offset above it. > > With 0002, "WHERE a = 19 AND b >= 5" scans t1 only, while "a >= 19 AND > > b >= 5" and plain "a = 19" still scan the default partition, the latter > > because a NULL in a later key is routed there. 0002 is a pruning > > improvement rather than a correctness fix, hence the split. > > I spent some time looking at these patches and I believe both the > changes together make sense. I feel the commentary around how all this > works is a bit lacking in general, however. > > The reason to switch to only checking for MINVALUE / MAXVALUE on the > first partition key is because you've made it so the > BTEqualStrategyNumber code handles doing this for subsequent keys, and > since the step generation always generates steps with leading subsets > of the given values, the code you added to BTEqualStrategyNumber will > trim off the default when the final partition found by the > BTEqualStrategyNumber code for the leading prefix has a MAXVALUE > clause. For example, if you have a query doing "WHERE a = 19 AND b >= > 5", the pruning code will generate 2 pruning steps, one for "a = 19" > and the 2nd with the full "a = 19 AND b >= 5". If there had been 3 > clauses, then steps would be generated for each intermediate prefix of > the quals matching each partition key (i.e 3 pruning steps). When we > get the pruning results for the "a = 19" step, that hits the > BTEqualStrategyNumber code and because we don't have quals there for > all partition key columns, we must find all partitions that match "a = > 19". There's a while loop doing that in the BTEqualStrategyNumber > case. > > Because we generate steps for each combination of leading prefix of > clauses matching the partition key, the leading "a = 19" step going > through the BTEqualStrategyNumber code will detect if the final > partition we land on that matches "a = 19" has a MAXVALUE, and not > include the DEFAULT. Since the result of this step and the step > containing the quals for both keys are intersected, you only need to > prune the default partition in one of the steps for it not to appear > in the intersected result of all pruning steps. > > I did modify the patch slightly so that the MINVALUE / MAXVALUE code > for the 0th partition key only executes when nvalues == 1. In the > example above, this means it only executes in the "a = 19" step and > not the "a = 19 and b >= 5" step. This doesn't make any difference to > the final result, but I think it makes more sense to do this as it > means we only apply that optimisation once rather than doing it > several times redundantly. I also tried to explain what's going on in > a paragraph of comments. > > I've attached an AI-written and hand-modified fuzzing tool that I used > to check your patch doesn't prune any partitions that should not be > pruned. This works by creating random permutations of RANGE > partitioned tables and then inserting a small number of records to > each partition. It then runs a few queries on that partitioned table > with various WHERE clauses and checks the number of records returned > with enable_partition_pruning both on and off, then checks the number > of records is the same in each instance. This would find cases where > partitions are pruned by mistake. I hand-modified the tool to write > the EXPLAIN output to pp_fuzz.debug_output and then I compared the > output from your patched version with master and checked to ensure the > results all made sense. > > Please have a look at v3 and confirm you're happy with the adjustments > I've made. > > David -- Regards, Ewan Young
