Re: Buffer usage detailed by RelKind in EXPLAIN ANALYZE BUFFERS - Mailing list pgsql-hackers

From Andres Freund
Subject Re: Buffer usage detailed by RelKind in EXPLAIN ANALYZE BUFFERS
Date
Msg-id 20230214024335.akq4mq6xwctyebej@awork3.anarazel.de
Whole thread Raw
In response to Re: Buffer usage detailed by RelKind in EXPLAIN ANALYZE BUFFERS  (Andrey Borodin <amborodin86@gmail.com>)
List pgsql-hackers
Hi,

On 2023-02-13 18:14:58 -0800, Andrey Borodin wrote:
> On Mon, Feb 13, 2023 at 4:39 PM Andres Freund <andres@anarazel.de> wrote:
> >
> > The problem I'm talking about is the increased overhead in InstrStopNode(),
> > due to BufferUsageAccumDiff() getting more expensive.
> >
> 
> Thanks, now I understand the problem better. According to godbolt.com
> my patch doubles the number of instructions in this function. Unless
> we compute only tables\indexes\matviews.
> Anyway, without regarding functionality of this particular patch,
> BufferUsageAccumDiff() does not seem slow to me. It's just a
> branchless bunch of simd instructions. Performance of this function
> might matter only when called gazillion times per second.

It is called gazillions of times per second when you do an EXPLAIN (ANALYZE,
BUFFERS). Every invocation of an executor node calls it.

Here's a quick pgbench, showing todays code, with -O3, without assertions:
       298.396           0  SELECT generate_series(1, 10000000) OFFSET 10000000;
       397.400           0  EXPLAIN (ANALYZE, TIMING OFF) SELECT generate_series(1, 10000000) OFFSET 10000000;
       717.238           0  EXPLAIN (ANALYZE, TIMING ON) SELECT generate_series(1, 10000000) OFFSET 10000000;
       419.736           0  EXPLAIN (ANALYZE, BUFFERS, TIMING OFF) SELECT generate_series(1, 10000000) OFFSET
10000000;
       761.575           0  EXPLAIN (ANALYZE, BUFFERS, TIMING ON) SELECT generate_series(1, 10000000) OFFSET 10000000;

The effect ends up a lot larger once you add in joins etc, because it ads
additional executor node that all have their instrumentation started/stopped.

Greetings,

Andres Freund



pgsql-hackers by date:

Previous
From: Kyotaro Horiguchi
Date:
Subject: Re: Time delayed LR (WAS Re: logical replication restrictions)
Next
From: David Rowley
Date:
Subject: Re: Some revises in adding sorting path