On Sun, Aug 13, 2023 at 5:50 PM Peter Geoghegan <p...@bowt.ie> wrote: > select * from tenk1 > where > two = 1 > and four = 3 > and hundred = 91 > and thousand = 891 > and tenthous = 1891; > > The query returns one row, and touches 3 buffers/pages (according to > EXPLAIN ANALYZE with buffers). The overheads here make perfect sense: > there's one root page access, one leaf page access, and a single heap > page access. Clearly the nbtree initial positioning code is able to > descend to the exact leaf page (and page offset) where the first > possible match could be found. Pretty standard stuff.
I probably should have made this first query use "four >= 3" instead of using "four = 3" (while still using "four > 2" for the second, "bad" query). The example works a bit better that way because now the queries are logically equivalent, and yet still have this big disparity. (We get 4 buffer hits for the "good" >= query, but 16 buffer hits for the equivalent "bad" > query.) -- Peter Geoghegan
