Re: Initdb-time block size specification

Hi,

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
10-20%).

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.

Greetings,

Andres Freund