On Sat, May 11, 2024 at 3:19 PM Tom Lane <t...@sss.pgh.pa.us> wrote: > showing that we are reading practically the whole index, which > is pretty sad considering the index conditions are visibly > mutually contradictory. What's going on? I find that: > > 1. _bt_preprocess_keys, which is responsible for detecting > mutually-contradictory index quals, fails to do so because it > really just punts on row-comparison quals: it shoves them > directly from input to output scankey array without any > comparisons to other keys. (In particular, that causes the > row-comparison qual to appear before the a = 0 one in the > output scankey array.)
It turns out that this behavior also causes problems for _bt_advance_array_keys (not performance problems, correctness problems). I found that out when working on a bugfix commit for an issue affecting Postgres 18, with incomplete opfamilies: https://postgr.es/m/CAH2-Wz=ds4M+3NXMgwxYxqU8MULaLf696_v5g=9WNmWL2=u...@mail.gmail.com The bugfix that I posted earlier today fixes the issue highlighted by your test case from this thread. Your test case will only read a tiny fraction of the index, since with the patch in place, everybody looks at the "a = 0" key first. Note that the issue I'm seeing isn't unique to row compares, even though I say plenty about them on the same thread. All contradictory inequalities are equally affected with an incomplete opfamily in cross-type scenarios. I'm now motivated to deal with key ordering issues in the most general way possible, due to the implications for _bt_advance_array_keys highlighted on that other thread. > 2. The initial-positioning logic in _bt_first chooses "a = 0" > as determining where to start the scan, because it always > prefers equality over inequality keys. (This seems reasonable.) Part of the problem, as I see it, is that _bt_first gets to independently decide for itself that it should prefer "a = 0" over the row compare qual. That happens to be the right thing to do here, but _bt_first shouldn't get to go its own way. And neither should _bt_readpage/_bt_checkkeys. Everybody should agree on which one scan key on each index column should be used for starting the scan in one direction, and ending the scan in the other direction. That should be decided during preprocessing. There is always exactly one best way to order and required-mark the keys output by preprocessing -- even with an incomplete opfamily. (And even with row compares that happen to be redundant or contradictory, which are similar.) -- Peter Geoghegan
