Feedback-ID: id4a34324:Fastmail
Date: Mon, 2 Mar 2026 14:29:16 -0500
From: Andres Freund <andres@anarazel.de>
To: Alexandre Felipe <o.alexandre.felipe@gmail.com>
Cc: Tomas Vondra <tomas@vondra.me>, Peter Geoghegan <pg@bowt.ie>,
	Thomas Munro <thomas.munro@gmail.com>,
 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>
Subject: Re: index prefetching
Message-ID: <s5p7iou7pdhxhvmv4rohmskwqmr36dc4rybvwlep5yvwrjs4pa@6oxsemms5mw4>
References: <issqornf6vdn3vb64fjuoathypmu3e5pgputd3lpfuvoeqyvzr@qfordnhplp2v>
 <CAE8JnxOn4+xUAnce+M7LfZWOqfrMMxasMaEmSKwiKbQtZr65uA@mail.gmail.com>
 <7e707787-272a-4c52-b5f1-5ac990514ecc@vondra.me>
 <c96ba898-02fb-4756-a1c7-0ddb08159804@vondra.me>
 <CAE8JnxPtia9m8y7b5s+gOMjZ_3QP=pTo+A6p_HmtrAV4PMo3ZQ@mail.gmail.com>
 <e972a7ab-015c-4026-8ef7-5a49f59f10f8@vondra.me>
 <CAE8JnxOJ48NU3rwW+gS67NUDKgxDS5pKNUywbUBGCBJkgUf+Hg@mail.gmail.com>
 <bf66ffb5-74b7-433d-aee4-0c9f29f97324@vondra.me>
 <3cbwjhwkomjv7jifau4yhb357gfnckut3sdrlbmhwzesd3kngj@affs2mpxg4gh>
 <CAE8JnxOQG=m_6-v-M_Nude5KRrOzAcbi2QPhySCJQ+e771BQHA@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: 
 <CAE8JnxOQG=m_6-v-M_Nude5KRrOzAcbi2QPhySCJQ+e771BQHA@mail.gmail.com>
Archived-At: 
 <https://www.postgresql.org/message-id/s5p7iou7pdhxhvmv4rohmskwqmr36dc4rybvwlep5yvwrjs4pa%406oxsemms5mw4>
Precedence: bulk

Hi,

On 2026-03-02 18:45:03 +0000, Alexandre Felipe wrote:
> > Unfortunately that has too big a performance penalty for fully cached
> > workloads :(. Doing buffer mapping lookups ahead of the current point is
> not
> > free.
>
> Could you elaborate?

Looking ahead requires pinning those buffers (or you need to do more buffer
lookups, which also isn't free). The way things work right now is that the
infrastructure to maintain buffer pins gets more expensive the more buffers
you pin.  That increased overhead doesnt' matter much if you actually avoid IO
or you have fair bit of work for each buffer (like looking at each tuple), but
if you just are fully cached and just look at a single tuple, it's painful.


CREATE TABLE uncorrelated(id int8 primary key, balance float not null);
INSERT INTO uncorrelated(id, balance) SELECT id, 0 FROM (SELECT generate_series(1, 1000000) AS id) ORDER BY random();
VACUUM (FREEZE, ANALYZE) uncorrelated;

CREATE TABLE correlated(id int8 primary key, balance float not null);
INSERT INTO correlated(id, balance) SELECT id, 0 FROM (SELECT generate_series(1, 1000000) AS id);
VACUUM (FREEZE, ANALYZE) correlated;


SELECT pg_prewarm('uncorrelated');
SET max_parallel_workers_per_gather=0;SET enable_bitmapscan = 0;SET enable_seqscan = 0;
SELECT sum(balance) FROM uncorrelated WHERE id > 1000 and id < 500000;

SELECT pg_prewarm('correlated');
SET max_parallel_workers_per_gather=0;SET enable_bitmapscan = 0;SET enable_seqscan = 0;
SELECT sum(balance) FROM correlated WHERE id > 1000 and id < 500000;


Index prefetching patch:
uncorrelated: 228.936 ms
correlated:   71.684  ms

+ minimum distance 2:
uncorrelated: 244.361 ms
correlated:   71.848 ms

+ minimum distance 4:
uncorrelated: 246.164 ms
correlated:   71.912 ms

+ minimum distance 8:
uncorrelated: 268.686 ms
correlated:   71.880 ms

+ minimum distance 16:
uncorrelated: 310.320 ms
correlated:   71.890 ms

+ minimum distance 32:
uncorrelated: 330.714 ms
correlated:   74.178 ms


There starts to be some overhead of the increased distance even when
correlated, but it's small and starts much later.


We probably can reduce the overhead of buffer tracking, but probably not
enough for it to not be a factor at all.


Possible improvements to refcount tracking:

- increase REFCOUNT_ARRAY_ENTRIES - there's a very significant cliff at 8
  right now, and with vectorized lookup it might not hurt too much to go to 16
  or so

- To make the cliff at REFCOUNT_ARRAY_ENTRIES smaller, replace dynahash with
  simplehash. That should reduce the perf penalty a good bit.


Unfortunately it's not just the refcount tracking, it's also resowner
management that gets more expensive.

Greetings,

Andres Freund