While working on #2748 I noticed that the ordering that is computed and reasoned about in AbstractDataAccessRule is the sort order that is achieved when realized and compensated matches are intersected when in fact the intersection is created prior to applying the intersected compensation on the individual legs of the intersection. That sort order may be different (see FDBNestedFieldQueryTest.nestedThenWithAndPartial()) where an equality predicate is applied as part of the compensation which only makes the intersection applicable after that equality predicate is applied (as compensation).
Note that index value columns are not really interesting here as they don't contribute to orderings. An equality predicate of an index key is only not applied as part of the index arguments if it is not part of the bound prefix of that match.
In #2748 I added some logic to demote an already established MatchedOrderingPart that uses an equality if that ordering part is not part of the bound prefix of the match. This approach, while correcting for incorrect plans, however, eliminates interesting orderings that can allow intersections to happen.
In general, the approach to defer compensation until after the intersection is the right one since any leg can filter a predicate AND it may be possible to implement a special intersection operator on index scans that works like a zig-zag join (in the future). What we really want here is to create a pre compensation for equality predicates so that we don't lose interesting orders that can partake in index ANDing.
Example:
index a: (a, b, c)
index b: (a, c)
SELECT * FROM T WHERE a > 10 AND b = 5
left: using index a yielding order after compensation (a-up, b =, c up)
right: using index b order after compensation (a up, c up)
intersection order computed without demotion: (a up, c up) (one example as a and b float freely)
Intersectio cannot be applied using a realized intersection as b=5 is not applied to left.
While working on #2748 I noticed that the ordering that is computed and reasoned about in
AbstractDataAccessRuleis the sort order that is achieved when realized and compensated matches are intersected when in fact the intersection is created prior to applying the intersected compensation on the individual legs of the intersection. That sort order may be different (seeFDBNestedFieldQueryTest.nestedThenWithAndPartial()) where an equality predicate is applied as part of the compensation which only makes the intersection applicable after that equality predicate is applied (as compensation).Note that index value columns are not really interesting here as they don't contribute to orderings. An equality predicate of an index key is only not applied as part of the index arguments if it is not part of the bound prefix of that match.
In #2748 I added some logic to demote an already established
MatchedOrderingPartthat uses an equality if that ordering part is not part of the bound prefix of the match. This approach, while correcting for incorrect plans, however, eliminates interesting orderings that can allow intersections to happen.In general, the approach to defer compensation until after the intersection is the right one since any leg can filter a predicate AND it may be possible to implement a special intersection operator on index scans that works like a zig-zag join (in the future). What we really want here is to create a pre compensation for equality predicates so that we don't lose interesting orders that can partake in index ANDing.
Example:
left: using index
ayielding order after compensation(a-up, b =, c up)right: using indexborder after compensation(a up, c up)intersection order computed without demotion:(a up, c up)(one example as a and b float freely)Intersectio cannot be applied using a realized intersection as b=5 is not applied to left.