Re: Prefetch the next tuple's memory during seqscans

On Tue, Nov 1, 2022 at 5:17 AM David Rowley <dgrowleyml(at)gmail(dot)com> wrote:
>
> My test is to run 16 queries changing the WHERE clause each time to
> have WHERE a = 0, then WHERE a2 = 0 ... WHERE a16 = 0. I wanted to
> know if prefetching only the first cache line of the tuple would be
> less useful when we require evaluation of say, the "a16" column vs the
> "a" column.

I tried a similar test, but with text fields of random length, and there is
improvement here:

Intel laptop, turbo boost off
shared_buffers = '4GB'
huge_pages = 'on'
max_parallel_workers_per_gather = '0'

create table text8 as
select
repeat('X', int4(random() * 20)) a1,
repeat('X', int4(random() * 20)) a2,
repeat('X', int4(random() * 20)) a3,
repeat('X', int4(random() * 20)) a4,
repeat('X', int4(random() * 20)) a5,
repeat('X', int4(random() * 20)) a6,
repeat('X', int4(random() * 20)) a7,
repeat('X', int4(random() * 20)) a8
from generate_series(1,10000000) a;
vacuum freeze text8;

psql -c "select pg_prewarm('text8')" && \
for i in a1 a2 a3 a4 a5 a6 a7 a8;
do
echo Testing $i
echo "select * from text8 where $i = 'ZZZ';" > bench.sql
pgbench -f bench.sql -M prepared -n -T 10 postgres | grep latency
done

Master:

Testing a1
latency average = 980.595 ms
Testing a2
latency average = 1045.081 ms
Testing a3
latency average = 1107.736 ms
Testing a4
latency average = 1162.188 ms
Testing a5
latency average = 1213.985 ms
Testing a6
latency average = 1272.156 ms
Testing a7
latency average = 1318.281 ms
Testing a8
latency average = 1363.359 ms

Patch 0001+0003:

Testing a1
latency average = 812.548 ms
Testing a2
latency average = 897.303 ms
Testing a3
latency average = 955.997 ms
Testing a4
latency average = 1023.497 ms
Testing a5
latency average = 1088.494 ms
Testing a6
latency average = 1149.418 ms
Testing a7
latency average = 1213.134 ms
Testing a8
latency average = 1282.760 ms

--
John Naylor
EDB: http://www.enterprisedb.com