Re: Naive handling of inequalities by nbtree initial positioning code

On Sun, Aug 13, 2023 at 5:50 PM Peter Geoghegan <pg(at)bowt(dot)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