Hello,
In case it might be useful, I made some more tests.
On my dev computer (a notebook) I installed:
PostgreSQL 15.5 (Ubuntu 15.5-1.pgdg23.10+1) on x86_64-pc-linux-gnu,
compiled by gcc (Ubuntu 13.2.0-4ubuntu3) 13.2.0, 64-bit
and
PostgreSQL 16.1 (Ubuntu 16.1-1.pgdg23.10+1) on x86_64-pc-linux-gnu,
compiled by gcc (Ubuntu 13.2.0-4ubuntu3) 13.2.0, 64-bit
I adjusted work_mem to 1GB and disabled JIT, restored the same DB, did
VACUUM ANALYZE and ran the query several times to lower I/O interference.
Execution time is about the same on PG 14, 15 and 16, around ~120ms
I noticed that planning time, whatever the version, is very variable
between executions (ranging from 0.120ms to 0.400ms), probably due to
other programs activity and <1ms measurements imprecision. So the
results I gave you in my previous email are probably irrelevant.
On our production server, which is running PG 14.10 on Debian 11, same
work_mem, execution time is ~45ms but planning time is much more
consistent at ~0.110ms
Interestingly though, lowering work_mem to 32MB gives 22ms execution
time but planning time rises consistently at ~0.7ms
On my notebook with work_mem=32MB, execution time is also 22ms but
planning time is lower at ~0.4ms (?!?)
Let me know if I can do anything to provide you with more useful
benchmark. The DB is still very small so it is easy to do tests.
JC