Message-ID: <51b5f71b-5f19-4453-91ff-2b9f2a840c58@vondra.me>
Date: Tue, 12 Aug 2025 23:52:17 +0200
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: index prefetching
To: Peter Geoghegan <pg@bowt.ie>, Thomas Munro <thomas.munro@gmail.com>
Cc: Andres Freund <andres@anarazel.de>, Nazir Bilal Yavuz
 <byavuz81@gmail.com>, Robert Haas <robertmhaas@gmail.com>,
 Melanie Plageman <melanieplageman@gmail.com>,
 PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>,
 Georgios <gkokolatos@protonmail.com>,
 Konstantin Knizhnik <knizhnik@garret.ru>, Dilip Kumar <dilipbalaut@gmail.com>
References: <8ed1d326-5c6e-476e-b3fd-30d3da210546@vondra.me>
 <38b865bd-2ae9-4a94-a788-6e3dc99ccd70@vondra.me>
 <CAH2-WzmER9kc4OtmkDh+h51QV=v6Yc5BGsJikwJHtucf1C1HWw@mail.gmail.com>
 <306fc8c0-c882-4602-86f5-a106b9ace603@vondra.me>
 <CAH2-Wz=0enySZ5g0k0BLY3tHRs=wyG=7yXDYP=Abt=6GM=7XkQ@mail.gmail.com>
 <152ea782-5bd4-4435-b021-0ab2da61e63d@vondra.me>
 <CAH2-WzkX2fwtiNOX4RrBR8=XKta999NM_5+ghTUnwUBkeyfcHQ@mail.gmail.com>
 <7c2f6350-6fca-4e39-b0a8-8ac735f5d58a@vondra.me>
 <CAH2-WzkPh+L2u8_4jG=NgGgzFNqW7ZZhSxGb6mJR=2YdouL1_Q@mail.gmail.com>
 <2e63cadd-2a03-46b1-866e-7ea5d3ffd37f@vondra.me>
 <xhe4l63oxs36sunvqbf3bzlzrondjyhumm7ywt4arkxu622f52@yriy7rejf5g2>
 <f516d66b-5bed-45af-8127-d09b684dad9e@vondra.me>
 <CAH2-WzmdiO4fHA1O06SYUjgMQZG7haysY7Tu5DS5z-CHsv5MLQ@mail.gmail.com>
 <d7bad36a-ac8d-4727-a52b-822621142d64@vondra.me>
 <CAH2-Wz=Y-PsC6_tZOPhHWvPx0geGnrh9VKjUZ-168ezUM_XM2Q@mail.gmail.com>
 <CA+hUKGKMaZLmNQHaa_DZMw9MJJKGegjrqnTY3KOZB-_nvFa3wQ@mail.gmail.com>
 <DC0RJDW1PEAX.NDGIXR6WPM94@bowt.ie>
Content-Language: en-US
From: Tomas Vondra <tomas@vondra.me>
In-Reply-To: <DC0RJDW1PEAX.NDGIXR6WPM94@bowt.ie>
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 7bit
Archived-At: 
 <https://www.postgresql.org/message-id/51b5f71b-5f19-4453-91ff-2b9f2a840c58%40vondra.me>
Precedence: bulk


On 8/12/25 23:22, Peter Geoghegan wrote:
> ...
>
> It looks like the patch does significantly better with the forwards scan,
> compared to the backwards scan (though both are improved by a lot).  But that's
> not the main thing about these results that I find interesting.
> 
> The really odd thing is that we get "shared hit=6619 read=49933" for the
> forwards scan, and "shared hit=10350 read=49933" for the backwards scan.  The
> latter matches master (regardless of the scan direction used on master), while
> the former just looks wrong.  What explains the "missing buffer hits" seen with
> the forwards scan?
> 
> Discrepancies
> -------------
> 
> All 4 query executions agree that "rows=1048576.00", so the patch doesn't appear
> to simply be broken/giving wrong answers.  Might it be that the "Buffers"
> instrumentation is broken?
> 

I think a bug in the prefetch patch is more likely. I tried with a patch
that adds various prefetch-related counters to explain, and I see this:


test=# EXPLAIN (ANALYZE, VERBOSE, COSTS OFF) SELECT * FROM t WHERE a
BETWEEN 16336 AND 49103 ORDER BY a;

                                QUERY PLAN
------------------------------------------------------------------------
 Index Scan using idx on public.t (actual time=0.682..527.055
rows=1048576.00 loops=1)
   Output: a, b
   Index Cond: ((t.a >= 16336) AND (t.a <= 49103))
   Index Searches: 1
   Prefetch Distance: 271.263
   Prefetch Count: 60888
   Prefetch Stalls: 1
   Prefetch Skips: 991211
   Prefetch Resets: 3
   Prefetch Histogram: [2,4) => 2, [4,8) => 8, [8,16) => 17, [16,32) =>
24, [32,64) => 34, [64,128) => 52, [128,256) => 82, [256,512) => 60669
   Buffers: shared hit=5027 read=50872
   I/O Timings: shared read=33.528
 Planning:
   Buffers: shared hit=78 read=23
   I/O Timings: shared read=2.349
 Planning Time: 3.686 ms
 Execution Time: 559.659 ms
(17 rows)


test=# EXPLAIN (ANALYZE, VERBOSE, COSTS OFF) SELECT * FROM t WHERE a
BETWEEN 16336 AND 49103 ORDER BY a DESC;
                                QUERY PLAN
------------------------------------------------------------------------
 Index Scan Backward using idx on public.t (actual time=1.110..4116.201
rows=1048576.00 loops=1)
   Output: a, b
   Index Cond: ((t.a >= 16336) AND (t.a <= 49103))
   Index Searches: 1
   Prefetch Distance: 271.061
   Prefetch Count: 118806
   Prefetch Stalls: 1
   Prefetch Skips: 962515
   Prefetch Resets: 3
   Prefetch Histogram: [2,4) => 2, [4,8) => 7, [8,16) => 12, [16,32) =>
17, [32,64) => 24, [64,128) => 3, [128,256) => 4, [256,512) => 118737
   Buffers: shared hit=30024 read=50872
   I/O Timings: shared read=581.353
 Planning:
   Buffers: shared hit=82 read=23
   I/O Timings: shared read=3.168
 Planning Time: 4.289 ms
 Execution Time: 4185.407 ms
(17 rows)

These two parts are interesting:

   Prefetch Count: 60888
   Prefetch Skips: 991211

   Prefetch Count: 118806
   Prefetch Skips: 962515

It looks like the backwards scan skips fewer blocks. This is based on
the lastBlock optimization, i.e. looking for runs of the same block
number. I don't quite see why would it affect just the backwards scan,
though. Seems weird.

> The premise of my original complaint was that big inconsistencies in performance
> shouldn't happen between similar forwards and backwards scans (at least not with
> direct I/O).  I now have serious doubts about that premise, since it looks like
> OS readahead remains a big factor with direct I/O.  Did I just miss something
> obvious?
> 

I don't think you missed anything. It does seem the assumption relies on
the OS handling the underlying I/O patterns equally, and unfortunately
that does not seem to be the case. Maybe we could "invert" the data set,
i.e. make it "descending" instead of "ascending"? That would make the
heap access direction "forward" again ...

regards

-- 
Tomas Vondra