Horton

Re: strange perf regression with data checksums

Aleksander Alekseev <aleksander@timescale.com>

View thread View on postgr.es
From: Aleksander Alekseev <aleksander@timescale.com>
To: PostgreSQL Hackers <pgsql-hackers@postgresql.org>
Cc: Tomas Vondra <tomas@vondra.me>
Date: 2025-05-09T12:53:52Z
Lists: pgsql-hackers 
Hi Tomas,

> While running some benchmarks comparing 17 and 18, I ran into a simple
> workload where 18 throughput drops by ~80%. After pulling my hair for a
> couple hours I realized the change that triggered this is 04bec894a04c,
> which set checksums on by default. Which is very bizarre, because the
> workload is read-only and fits into shared buffers.
>
> [...]
>
> But why would it depend on checksums at all? This read-only test should
> be entirely in-memory, so how come it's affected?

These are interesting results.

Just wanted to clarify: did you make sure that all the hint bits were
set before executing the benchmark?

I'm not claiming that hint bits are necessarily the reason for the
observed behavior but when something is off with presumably read-only
queries this is the first reason that comes to mind. At least we
should make sure hint bits are excluded from the equation. If memory
serves, VACUUM FULL and CHECKPOINT after filling the table and
creating the index should do the trick.

-- 
Best regards,
Aleksander Alekseev

Commits

  1. Make _bt_killitems drop pins it acquired itself.

  2. Avoid BufferGetLSNAtomic() calls during nbtree scans.

  3. Reduce pinning and buffer content locking for btree scans.