Content-Type: multipart/mixed; boundary="------------JHSn3OcJk0xd88aZ3IZYOS1u"
Message-ID: <8f123089-49ed-4c35-ab28-e3df70d28043@garret.ru>
Date: Thu, 25 Dec 2025 17:39:24 +0200
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: index prefetching
To: Peter Geoghegan <pg@bowt.ie>, Tomas Vondra <tomas@vondra.me>
Cc: Andres Freund <andres@anarazel.de>, 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>, Dilip Kumar <dilipbalaut@gmail.com>
References: <6d59c277-c440-4d1f-a46e-157958c06a5f@vondra.me>
 <qdl4fojnbfcnm2k7b4zpvgd6gwzwdgtbl5c7shpimrb76dbyy6@scdnspus3ejh>
 <bpdeohyqvltb77viyft4bza4xc4peed3jcoep74d2ih6ynqlke@wbnhcwmq3ril>
 <CAH2-WznFdjY_OB2S7_BY4iAyeffK+XrE2qsX6aghgP63VocRfQ@mail.gmail.com>
 <4zeu5yb73byiquvf3eefsunnrydyqfxy3eup66jrliutrtd4xl@5iifjey4n5m5>
 <c515d037-9c9c-40d3-9f1f-f0e68bf5842b@vondra.me>
 <CAH2-WzkAC_gWQgc3MV3MWBx2EUaE4TfPk8XQPkJgs1R_qNvo=A@mail.gmail.com>
 <CAH2-WznLRnUZHC4kmR+J3MgGpN0iTUaZ-4xeSHPiaYR=y3C8Og@mail.gmail.com>
 <CAH2-WzkHK0++yn3_nmWL2rWzXiu8Qc=2=NPQDG4nfVjvYkRDrQ@mail.gmail.com>
 <CAH2-Wzk9=x=a2TbcqYcX+XXmDHQr5=1v9m4Z_v8a-KwF1Zoz0A@mail.gmail.com>
 <CAH2-WzmYqhacBH161peAWb5eF=Ja7CFAQ+0jSEMq=qnfLVTOOg@mail.gmail.com>
 <CAH2-Wzmm+mXuv_r+eYgcwOKZzNSmbYoEmxi5mdKMUtkmYa_qXw@mail.gmail.com>
 <CAH2-WzkC_EdGB_tdEQr63BiOuJbcvZiV=xO+OJYux4wOaxPD8w@mail.gmail.com>
 <CAH2-Wzn8whv-RE4E5L2WQB_Ha_jy0UReJq1yypu83gDYHeXq5g@mail.gmail.com>
 <CAH2-Wzm7-QuDOs6TcqfhhDsGEZCuHtn=D-SriOTnTZ_fiXNBvA@mail.gmail.com>
Content-Language: en-US
From: Konstantin Knizhnik <knizhnik@garret.ru>
In-Reply-To: 
 <CAH2-Wzm7-QuDOs6TcqfhhDsGEZCuHtn=D-SriOTnTZ_fiXNBvA@mail.gmail.com>
Archived-At: 
 <https://www.postgresql.org/message-id/8f123089-49ed-4c35-ab28-e3df70d28043%40garret.ru>
Precedence: bulk

This is a multi-part message in MIME format.
--------------JHSn3OcJk0xd88aZ3IZYOS1u
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit


On 21/12/2025 7:55 PM, Peter Geoghegan wrote:
> On Wed, Dec 10, 2025 at 9:21 PM Peter Geoghegan <pg@bowt.ie> wrote:
>> Attached is v4.
> Attached is v5. Changes from v4:
>
> * Simplified and optimized index-only scans, with a particular
> emphasis on avoiding regressions with nested loop joins with an inner
> index-only scan.
>
> There were quite a number of small problems/dead code related to
> index-only scans fixed by this new v5. Overall, I'm quite a bit
> happier with the state of index-only scans, which I'd not paid too
> much attention to before now.
>
> * Added Valgrind instrumentation to the hash index patch, which was
> required to fix some false positives.
>
> The generic indexam_util_batch_unlock routine had Valgrind
> instrumentation in earlier versions, just to keep nbtree's buffer
> locking checks from generating similar false positives. Some time
> later, when I added the hashgetbatch patch, there were new Valgrind
> false positives during hash index scans -- which I missed at first.
> This new v5 revisions adds similar Valgrind checks to hash itself
> (changes that add code that is more or less a direct port of the stuff
> added to nbtree by commit 4a70f829), which fixes the false positives,
> and is independently useful.
>
> The rule for amgetbatch-based index AMs is that they must have similar
> buffer locking instrumentation. That seems like a good thing.
>
> --
> Peter Geoghegan

I the previous mail I shared results of my experiments with different 
prefetch distance.
I think that we should start prefetching of heap tuples not from the 
second batch, but after some number of proceeded tids.

Attached please find a patch which implements this approach.
And below are updated results:

limit\prefetch    on      off   always  inc    threshold
1                 12074   12765  3146    3282     12394
2                 5912    6198   2463    2438      6124
4                 2919    3047   1334    1964      2910
8                 1554    1496   1166    1409      1588
16                815     775    947     940        600
32                424     403    687     695        478
64                223     208    446     453        358
128               115     106    258     270        232
256               68      53     138     149        131
512               43      27     72      78          71
1024              28      13     38      40          38

Last column is result of prefetch with read_stream_threshold=10.


--------------JHSn3OcJk0xd88aZ3IZYOS1u
Content-Type: text/plain; charset=UTF-8; name="read_stream_threshold.patch"
Content-Disposition: attachment; filename="read_stream_threshold.patch"
Content-Transfer-Encoding: base64

ZGlmZiAtLWdpdCBhL3NyYy9iYWNrZW5kL2FjY2Vzcy9oZWFwL2hlYXBhbV9oYW5kbGVyLmMg
Yi9zcmMvYmFja2VuZC9hY2Nlc3MvaGVhcC9oZWFwYW1faGFuZGxlci5jCmluZGV4IGI5ZDQy
YjE1YTE4Li5hYWM2NjNlZGZkZCAxMDA2NDQKLS0tIGEvc3JjL2JhY2tlbmQvYWNjZXNzL2hl
YXAvaGVhcGFtX2hhbmRsZXIuYworKysgYi9zcmMvYmFja2VuZC9hY2Nlc3MvaGVhcC9oZWFw
YW1faGFuZGxlci5jCkBAIC0zNiw2ICszNiw3IEBACiAjaW5jbHVkZSAiY29tbWFuZHMvcHJv
Z3Jlc3MuaCIKICNpbmNsdWRlICJleGVjdXRvci9leGVjdXRvci5oIgogI2luY2x1ZGUgIm1p
c2NhZG1pbi5oIgorI2luY2x1ZGUgIm9wdGltaXplci9jb3N0LmgiCiAjaW5jbHVkZSAicGdz
dGF0LmgiCiAjaW5jbHVkZSAic3RvcmFnZS9idWZtZ3IuaCIKICNpbmNsdWRlICJzdG9yYWdl
L2J1ZnBhZ2UuaCIKQEAgLTQ2LDYgKzQ3LDggQEAKICNpbmNsdWRlICJ1dGlscy9idWlsdGlu
cy5oIgogI2luY2x1ZGUgInV0aWxzL3JlbC5oIgogCitpbnQgcmVhZF9zdHJlYW1fdGhyZXNo
b2xkID0gREVGQVVMVF9SRUFEX1NUUkVBTV9USFJFU0hPTEQ7CisKIHN0YXRpYyB2b2lkIHJl
Zm9ybV9hbmRfcmV3cml0ZV90dXBsZShIZWFwVHVwbGUgdHVwbGUsCiAJCQkJCQkJCQkgUmVs
YXRpb24gT2xkSGVhcCwgUmVsYXRpb24gTmV3SGVhcCwKIAkJCQkJCQkJCSBEYXR1bSAqdmFs
dWVzLCBib29sICppc251bGwsIFJld3JpdGVTdGF0ZSByd3N0YXRlKTsKQEAgLTIyOSw2ICsy
MzIsNyBAQCBoZWFwX2JhdGNoX2FkdmFuY2VfcG9zKEluZGV4U2NhbkRlc2Mgc2Nhbiwgc3Ry
dWN0IEJhdGNoUXVldWVJdGVtUG9zICpwb3MsCiAJCQkJCSAgIFNjYW5EaXJlY3Rpb24gZGly
ZWN0aW9uKQogewogCUJhdGNoSW5kZXhTY2FuIGJhdGNoOworCWludCBwcm9jZWVkX2l0ZW1z
OwogCiAJLyogbWFrZSBzdXJlIHdlIGhhdmUgYmF0Y2hpbmcgaW5pdGlhbGl6ZWQgYW5kIGNv
bnNpc3RlbnQgKi8KIAliYXRjaF9hc3NlcnRfYmF0Y2hlc192YWxpZChzY2FuKTsKQEAgLTI4
OCw2ICsyOTIsMjQgQEAgaGVhcF9iYXRjaF9hZHZhbmNlX3BvcyhJbmRleFNjYW5EZXNjIHNj
YW4sIHN0cnVjdCBCYXRjaFF1ZXVlSXRlbVBvcyAqcG9zLAogCSAqLwogCWJhdGNoID0gSU5E
RVhfU0NBTl9CQVRDSChzY2FuLCBwb3MtPmJhdGNoKTsKIAorCXByb2NlZWRfaXRlbXMgPSBT
Y2FuRGlyZWN0aW9uSXNGb3J3YXJkKGRpcmVjdGlvbikKKwkJPyBwb3MtPml0ZW0gLSBiYXRj
aC0+Zmlyc3RJdGVtCisJCTogYmF0Y2gtPmxhc3RJdGVtIC0gcG9zLT5pdGVtOworCisJLyog
RGVsYXkgaW5pdGlhbGl6aW5nIHN0cmVhbSB1bnRpbCBwcm9jZWVkaW5nICovCisJaWYgKHBy
b2NlZWRfaXRlbXMgPj0gcmVhZF9zdHJlYW1fdGhyZXNob2xkCisJCSYmICFzY2FuLT54c19o
ZWFwZmV0Y2gtPnJzCisJCSYmICFzY2FuLT5iYXRjaHF1ZXVlLT5kaXNhYmxlZAorCQkmJiAh
c2Nhbi0+eHNfd2FudF9pdHVwCS8qIFhYWCBwcmVmZXRjaGluZyBkaXNhYmxlZCBmb3IgSW9T
LCBmb3Igbm93ICovCisJCSYmIGVuYWJsZV9pbmRleHNjYW5fcHJlZmV0Y2gpCisJeworCQlz
Y2FuLT54c19oZWFwZmV0Y2gtPnJzID0KKwkJCXJlYWRfc3RyZWFtX2JlZ2luX3JlbGF0aW9u
KFJFQURfU1RSRUFNX0RFRkFVTFQsIE5VTEwsCisJCQkJCQkJCQkgICBzY2FuLT5oZWFwUmVs
YXRpb24sIE1BSU5fRk9SS05VTSwKKwkJCQkJCQkJCSAgIHNjYW4tPmhlYXBSZWxhdGlvbi0+
cmRfdGFibGVhbS0+aW5kZXhfZ2V0bmV4dF9zdHJlYW0sCisJCQkJCQkJCQkgICBzY2FuLCAw
KTsKKwl9CisKIAlpZiAoU2NhbkRpcmVjdGlvbklzRm9yd2FyZChkaXJlY3Rpb24pKQogCXsK
IAkJaWYgKCsrcG9zLT5pdGVtIDw9IGJhdGNoLT5sYXN0SXRlbSkKZGlmZiAtLWdpdCBhL3Ny
Yy9iYWNrZW5kL3V0aWxzL21pc2MvZ3VjX3BhcmFtZXRlcnMuZGF0IGIvc3JjL2JhY2tlbmQv
dXRpbHMvbWlzYy9ndWNfcGFyYW1ldGVycy5kYXQKaW5kZXggOGY2ZmE2ODQzY2IuLjBjMjA4
MWUzMmJhIDEwMDY0NAotLS0gYS9zcmMvYmFja2VuZC91dGlscy9taXNjL2d1Y19wYXJhbWV0
ZXJzLmRhdAorKysgYi9zcmMvYmFja2VuZC91dGlscy9taXNjL2d1Y19wYXJhbWV0ZXJzLmRh
dApAQCAtMjMyMiw2ICsyMzIyLDE1IEBACiAgIG1heCA9PiAnREJMX01BWCcsCiB9LAogCit7
IG5hbWUgPT4gJ3JlYWRfc3RyZWFtX3RocmVzaG9sZCcsIHR5cGUgPT4gJ2ludCcsIGNvbnRl
eHQgPT4gJ1BHQ19VU0VSU0VUJywgZ3JvdXAgPT4gJ1FVRVJZX1RVTklOR19DT1NUJywKKyAg
c2hvcnRfZGVzYyA9PiAnTWluaW1hbCBudW1iZXIgb2YgaGVhcCB0dXBsZXMgZm9yIGNyZWF0
aW9uIHJlYWQgc3RyZWFtLicsCisgIGZsYWdzID0+ICdHVUNfRVhQTEFJTicsCisgIHZhcmlh
YmxlID0+ICdyZWFkX3N0cmVhbV90aHJlc2hvbGQnLAorICBib290X3ZhbCA9PiAnREVGQVVM
VF9SRUFEX1NUUkVBTV9USFJFU0hPTEQnLAorICBtaW4gPT4gJzAnLAorICBtYXggPT4gJ0lO
VF9NQVgnLAorfSwKKwogeyBuYW1lID0+ICdyZWNvdmVyeV9lbmRfY29tbWFuZCcsIHR5cGUg
PT4gJ3N0cmluZycsIGNvbnRleHQgPT4gJ1BHQ19TSUdIVVAnLCBncm91cCA9PiAnV0FMX0FS
Q0hJVkVfUkVDT1ZFUlknLAogICBzaG9ydF9kZXNjID0+ICdTZXRzIHRoZSBzaGVsbCBjb21t
YW5kIHRoYXQgd2lsbCBiZSBleGVjdXRlZCBvbmNlIGF0IHRoZSBlbmQgb2YgcmVjb3Zlcnku
JywKICAgdmFyaWFibGUgPT4gJ3JlY292ZXJ5RW5kQ29tbWFuZCcsCmRpZmYgLS1naXQgYS9z
cmMvaW5jbHVkZS9vcHRpbWl6ZXIvY29zdC5oIGIvc3JjL2luY2x1ZGUvb3B0aW1pemVyL2Nv
c3QuaAppbmRleCAwMGY0YzNkMDAxMS4uOTcxNTA0MzNjOTkgMTAwNjQ0Ci0tLSBhL3NyYy9p
bmNsdWRlL29wdGltaXplci9jb3N0LmgKKysrIGIvc3JjL2luY2x1ZGUvb3B0aW1pemVyL2Nv
c3QuaApAQCAtMjgsNiArMjgsNyBAQAogI2RlZmluZSBERUZBVUxUX0NQVV9PUEVSQVRPUl9D
T1NUICAwLjAwMjUKICNkZWZpbmUgREVGQVVMVF9QQVJBTExFTF9UVVBMRV9DT1NUIDAuMQog
I2RlZmluZSBERUZBVUxUX1BBUkFMTEVMX1NFVFVQX0NPU1QgIDEwMDAuMAorI2RlZmluZSBE
RUZBVUxUX1JFQURfU1RSRUFNX1RIUkVTSE9MRAkxMAogCiAvKiBkZWZhdWx0cyBmb3Igbm9u
LUNvc3QgcGFyYW1ldGVycyAqLwogI2RlZmluZSBERUZBVUxUX1JFQ1VSU0lWRV9XT1JLVEFC
TEVfRkFDVE9SICAxMC4wCkBAIC03Miw2ICs3Myw3IEBAIGV4dGVybiBQR0RMTElNUE9SVCBi
b29sIGVuYWJsZV9wYXJ0aXRpb25fcHJ1bmluZzsKIGV4dGVybiBQR0RMTElNUE9SVCBib29s
IGVuYWJsZV9wcmVzb3J0ZWRfYWdncmVnYXRlOwogZXh0ZXJuIFBHRExMSU1QT1JUIGJvb2wg
ZW5hYmxlX2FzeW5jX2FwcGVuZDsKIGV4dGVybiBQR0RMTElNUE9SVCBpbnQgY29uc3RyYWlu
dF9leGNsdXNpb247CitleHRlcm4gUEdETExJTVBPUlQgaW50IHJlYWRfc3RyZWFtX3RocmVz
aG9sZDsKIAogZXh0ZXJuIGRvdWJsZSBpbmRleF9wYWdlc19mZXRjaGVkKGRvdWJsZSB0dXBs
ZXNfZmV0Y2hlZCwgQmxvY2tOdW1iZXIgcGFnZXMsCiAJCQkJCQkJCSAgZG91YmxlIGluZGV4
X3BhZ2VzLCBQbGFubmVySW5mbyAqcm9vdCk7Cg==

--------------JHSn3OcJk0xd88aZ3IZYOS1u--