Re: Naive handling of inequalities by nbtree initial positioning code - Mailing list pgsql-hackers

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