Re: Add pg_walinspect function with block info columns - Mailing list pgsql-hackers

On Wed, Mar 15, 2023 at 12:20 PM Michael Paquier <michael@paquier.xyz> wrote:
>
> On Tue, Mar 14, 2023 at 06:50:15PM -0700, Peter Geoghegan wrote:
> > On Tue, Mar 14, 2023 at 5:34 PM Melanie Plageman
> > <melanieplageman@gmail.com> wrote:
> >> Well, I think if you only care about the WAL record-level information
> >> and not the block-level information, having the WAL record information
> >> denormalized like that with all the block information would be a
> >> nuisance.
> >
> > I generally care about both. When I want to look at things at the
> > pg_get_wal_records_info() level (as opposed to a summary), the
> > block_ref information is *always* of primary importance. I don't want
> > to have to write my own bug-prone parser for block_ref, but why should
> > the only alternative be joining against pg_get_wal_block_info()? The
> > information that I'm interested in is "close at hand" to
> > pg_get_wal_records_info() already.
> >
>
> I am not sure to get the concern here.  As long as one is smart enough
> with SQL, there is no need to perform a double scan of the contents of
> pg_wal with a large scan on the start LSN.  If one wishes to only
> extract some block for a given record type, or for a filter of your
> choice, it is possible to use a LATERAL on pg_get_wal_block_info(),
> say:
> SELECT r.start_lsn, b.blockid
>   FROM pg_get_wal_records_info('0/01000028', '0/1911AA8') AS r,
>   LATERAL pg_get_wal_block_info(start_lsn, end_lsn) as b
>   WHERE r.resource_manager = 'Heap2';
>
> This will extract the block information that you'd want for a given
> record type.

It looks like nested-loop join is chosen for LATERAL query [1], that
is, for every start_lsn and end_lsn that we get from
pg_get_wal_records_info, pg_get_wal_block_info gets called. Whereas,
for non-LATERAL join [2], hash/merge join is chosen which is pretty
fast (5x) over 5mn WAL records. Therefore, I'm not sure if adding the
LATERAL query as an example is a better idea.

IIUC, the concern raised so far in this thread is not just on the
performance of JOIN queries to get both block info and record level
info, but on ease of using pg_walinspect functions. If
pg_get_wal_block_info emits the record level information too (which
turns out to be 50 LOC more), one doesn't have to be expert at writing
JOIN queries or such, but just can run the function, which actually
takes way less time (3sec) to scan the same 5mn WAL records [3].

[1]
postgres=# EXPLAIN (ANALYZE) SELECT * FROM
pg_get_wal_records_info(:'start_lsn', :'end_lsn') AS r,
  LATERAL pg_get_wal_block_info(start_lsn, end_lsn) AS b WHERE
r.resource_manager = 'Heap';
                                                                 QUERY
PLAN

---------------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=0.01..112.50 rows=5000 width=330) (actual
time=3175.114..49596.749 rows=5000019 loops=1)
   ->  Function Scan on pg_get_wal_records_info r  (cost=0.00..12.50
rows=5 width=168) (actual time=3175.058..4142.507 rows=4000019
loops=1)
         Filter: (resource_manager = 'Heap'::text)
         Rows Removed by Filter: 52081
   ->  Function Scan on pg_get_wal_block_info b  (cost=0.00..10.00
rows=1000 width=162) (actual time=0.011..0.011 rows=1 loops=4000019)
 Planning Time: 0.076 ms
 Execution Time: 49998.850 ms
(7 rows)

Time: 49999.203 ms (00:49.999)

[2]
postgres=# EXPLAIN (ANALYZE) SELECT * FROM
pg_get_wal_block_info(:'start_lsn', :'end_lsn') AS b
        JOIN pg_get_wal_records_info(:'start_lsn', :'end_lsn') AS w ON
w.start_lsn = b.lsn WHERE w.resource_manager = 'Heap';

QUERY PLAN

---------------------------------------------------------------------------------------------------------------------------------------------------
 Hash Join  (cost=12.57..26.57 rows=25 width=330) (actual
time=6241.449..9901.715 rows=5000019 loops=1)
   Hash Cond: (b.lsn = w.start_lsn)
   ->  Function Scan on pg_get_wal_block_info b  (cost=0.00..10.00
rows=1000 width=162) (actual time=1415.815..1870.522 rows=5067960
loops=1)
   ->  Hash  (cost=12.50..12.50 rows=5 width=168) (actual
time=4665.292..4665.292 rows=4000019 loops=1)
         Buckets: 65536 (originally 1024)  Batches: 128 (originally 1)
 Memory Usage: 7681kB
         ->  Function Scan on pg_get_wal_records_info w
(cost=0.00..12.50 rows=5 width=168) (actual time=3160.010..3852.332
rows=4000019 loops=1)
               Filter: (resource_manager = 'Heap'::text)
               Rows Removed by Filter: 52081
 Planning Time: 0.082 ms
 Execution Time: 10159.066 ms
(10 rows)

Time: 10159.465 ms (00:10.159)

[3]
postgres=# EXPLAIN ANALYZE SELECT * FROM
pg_get_wal_block_info(:'start_lsn', :'end_lsn');
                                                              QUERY
PLAN

--------------------------------------------------------------------------------------------------------------------------------------
 Function Scan on pg_get_wal_block_info  (cost=0.00..10.00 rows=1000
width=286) (actual time=2617.755..3081.526 rows=5004478 loops=1)
 Planning Time: 0.039 ms
 Execution Time: 3301.217 ms
(3 rows)

Time: 3301.817 ms (00:03.302)
postgres=#

--
Bharath Rupireddy
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com