public inbox for [email protected]  

help / color / mirror / Atom feed

From: Tomas Vondra <[email protected]>
To: Andres Freund <[email protected]>
To: Peter Geoghegan <[email protected]>
Cc: Thomas Munro <[email protected]>
Cc: Nazir Bilal Yavuz <[email protected]>
Cc: Robert Haas <[email protected]>
Cc: Melanie Plageman <[email protected]>
Cc: PostgreSQL Hackers <[email protected]>
Cc: Georgios <[email protected]>
Cc: Konstantin Knizhnik <[email protected]>
Cc: Dilip Kumar <[email protected]>
Subject: Re: index prefetching
Date: Thu, 4 Sep 2025 20:55:19 +0200
Message-ID: <[email protected]> (raw)
In-Reply-To: <4zeu5yb73byiquvf3eefsunnrydyqfxy3eup66jrliutrtd4xl@5iifjey4n5m5>
References: <[email protected]>
	<5pltwb73d7cynsxo2yb54ygjk7haviatkrx43mnzihc6kkield@ahnstpgof46i>
	<CA+hUKGKL3MRvEftAE+kwBuL2PLg2CwUoHEMr=-KSvsWN8pHq9w@mail.gmail.com>
	<[email protected]>
	<e33gafg4p7iwvo24ytrxuw43nafm5xm3jefpdspnarcbkfurs7@3jbgdiinxem5>
	<[email protected]>
	<CAH2-Wz=DfvzasnzLv43cu36Q1Ca8Qi70_JjZ7SRbNhDwwgvirg@mail.gmail.com>
	<qdl4fojnbfcnm2k7b4zpvgd6gwzwdgtbl5c7shpimrb76dbyy6@scdnspus3ejh>
	<bpdeohyqvltb77viyft4bza4xc4peed3jcoep74d2ih6ynqlke@wbnhcwmq3ril>
	<CAH2-WznFdjY_OB2S7_BY4iAyeffK+XrE2qsX6aghgP63VocRfQ@mail.gmail.com>
	<4zeu5yb73byiquvf3eefsunnrydyqfxy3eup66jrliutrtd4xl@5iifjey4n5m5>

On 9/3/25 22:06, Andres Freund wrote:
> ...
>
> I continue to be worried that we're optimizing for queries that have no
> real-world relevance. The regression afaict is contingent on
> 
> 1) An access pattern that is unpredictable to the CPU (due to the use of
>    random() as part of ORDER BY during the data generation)
> 
> 2) Index and heap are somewhat correlated, but fuzzily, i.e. there are
>    backward jumps in the heap block numbers being fetched
> 

Aren't those two points rather contradictory? Why would it matter that
the data generator uses random() in the ORDER BY? Seems entirely
irrelevant, if the generated table is "somewhat correlated".

Which seems pretty normal in real-world data sets ...


> 3) There are 1 - small_number tuples on one heap tables
> 

What would you consider a reasonable number of tuples on one heap page?

The current tests generate data with 20-100 tuples per page, which seems
pretty reasonable to me. I mean, that's 80-400B per tuple. Sure, I could
generate data with narrower tuples, but would that be more realistic? I
doubt that.

FWIW it's not like the regressions only happen on fillfactor=20, with 20
tuples/page. It happens on fillfactor=100 (sure, the impact is smaller).

> 4) The query scans a huge number of tuples, without actually doing any
>    meaningful analysis on the tuples. As soon as one does meaningful work for
>    returned tuples, the small difference in per-tuple CPU costs vanishes
> 

I believe I already responded to this before. Sure, the relative
regression will get smaller. But I don't see why would the absolute
difference get smaller.

> 5) The query visits all heap pages within a range, just not quite in
>    order. Without that the kernel readahead would not work and the query's
>    performance without readahead would be terrible even on low-latency storage
> 

I'm sorry, I don't quite understand what this says :-( Or why would that
mean the issues triggered by the generated data sets are not valid even
for real-world queries.

> This just doesn't strike me as a particularly realistic combination of
> factors?
>

Aren't plenty of real-world data sets correlated, but not perfectly?

In any case, I'm the first one to admit these data sets are synthetic.
It's meant to generate data sets that gradually shift from perfectly
ordered to random, increasing number of duplicates, etc. The point was
to cover a wider range of data sets, not just a couple "usual" ones.

It's possible some of these data sets are not realistic, in which case
we can choose to ignore them and the regressions. The approach tends to
find "adversary" cases, hit corner cases (not necessarily as rare as
assumed), etc. But the issues we ran into so far seem perfectly valid
(or at least useful to think about).


regards

-- 
Tomas Vondra

view thread (348+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected]
  Subject: Re: index prefetching
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox