Re: index prefetching

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