Re: Initdb-time block size specification

From: Andres Freund <andres(at)anarazel(dot)de>
To: David Christensen <david(dot)christensen(at)crunchydata(dot)com>
Cc: Tomas Vondra <tomas(dot)vondra(at)enterprisedb(dot)com>, PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>, Stephen Frost <sfrost(at)snowman(dot)net>
Subject: Re: Initdb-time block size specification
Date: 2023-06-30 20:27:08
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers


On 2023-06-30 14:09:55 -0500, David Christensen wrote:
> On Fri, Jun 30, 2023 at 1:14 PM Tomas Vondra
> > I wonder how to best evaluate/benchmark this. AFAICS what we want to
> > measure is the extra cost of making the values dynamic (which means the
> > compiler can't just optimize them out). I'd say a "pgbench -S" seems
> > like a good test.
> Yep, I tested 100 runs apiece with pgbench -S at scale factor 100,
> default settings for optimized builds of the same base commit with and
> without the patch; saw a reduction of TPS around 1% in that case, but
> I do think we'd want to look at different workloads; I presume the
> largest impact would be seen when it's all in shared_buffers and no IO
> is required.

I think pgbench -S indeed isn't a good workload - the overhead for it is much
more in context switches and instantiating executor state etc than code that
is affected by the change.

And indeed. Comparing e.g. TPC-H, I see *massive* regressions. Some queries
are the same, sobut others regress by up to 70% (although more commonly around

That's larger than I thought, which makes me suspect that there's some bug in
the new code.

Interestingly, repeating the benchmark with a larger work_mem setting, the
regressions are still quite present, but smaller. I suspect the planner
chooses smarter plans which move bottlenecks more towards hashjoin code etc,
which won't be affected by this change.

IOW, you seriously need to evaluate analytics queries before this is worth
looking at further.


Andres Freund

In response to


Browse pgsql-hackers by date

  From Date Subject
Next Message Nathan Bossart 2023-06-30 20:42:11 Re: Should we remove db_user_namespace?
Previous Message Robert Haas 2023-06-30 20:16:31 Re: pg_upgrade instructions involving "rsync --size-only" might lead to standby corruption?