As I recently went into on the thread where we've been discussing my nbtree SAOP patch [1], there is good reason to suspect that one of the optimizations added by commit e0b1ee17 is buggy in the presence of an opfamily lacking the full set of cross-type comparisons. The attached test case confirms that these suspicions were correct. Running the tese case against HEAD will lead to an assertion failure (or a wrong answer when assertions are disabled).
To recap, the optimization in question (which is not to be confused with the "precheck" optimization from the same commit) is based on the idea that _bt_first must always land the scan ahead of the position that the scan would end on, were the scan direction to change (from forwards to backwards, say). It follows that inequality strategy scan keys that are required in the opposite-to-scan direction *only* must be redundant in the current scan direction (in the sense that _bt_checkkeys needn't bother comparing them at all). Unfortunately, that rationale is at least slightly wrong. Although some version of the same assumption must really hold in the case of required equality strategy scan keys (old comments in _bt_checkkeys and in _bt_first say as much), it isn't really guaranteed in the case of inequalities. In fact, the following sentence appears in old comments above _bt_preprocess_keys, directly contradicting the theory behind the optimization in question: "In general, when inequality keys are present, the initial-positioning code only promises to position before the first possible match, not exactly at the first match, for a forward scan; or after the last match for a backward scan." My test case mostly just demonstrates how to reproduce the scenario described by this sentence. It's probably possible to salvage the optimization, but that will require bookkeeping sufficient to detect these unsafe cases, so that _bt_checkkeys only skips the comparisons when it's truly safe. As far as I know, the only reason that inequalities differ from equalities is this respect is the issue that the test case highlights. (There were also issues with NULLs, but AFAICT Alexander dealt with that aspect of the problem already.) [1] https://postgr.es/m/CAH2-Wz=BuxYEHxpNH0tPvo=+g1wte1pamrovu1devow1vy9...@mail.gmail.com -- Peter Geoghegan
require_opposite_dir_repro.sql
Description: Binary data