Re: Show various offset arrays for heap WAL records - Mailing list pgsql-hackers

On Tue, Mar 21, 2023 at 3:37 PM Peter Geoghegan <pg@bowt.ie> wrote:
> One problem that I often run into when performing analysis of VACUUM
> using pg_walinspect is the issue of *who* pruned which heap page, for
> any given PRUNE record. Was it VACUUM/autovacuum, or was it
> opportunistic pruning? There is no way of knowing for sure right now.
> You *cannot* rely on an xid of 0 as an indicator of a given PRUNE
> record coming from VACUUM; it could just have been an opportunistic
> prune operation that happened to take place when a SELECT query ran,
> before any XID was ever allocated.

In case it's unclear how much of a problem this can be, here's an example:

The misc.sql regression test does a bulk update of the table "onek". A
little later, one of the queries that appears under the section "copy"
from the same file SELECTs from "onek". This produces a succession of
opportunistic prune records that look exactly like what you'd expect from
a VACUUM when viewed through pg_walinspect (without this patch). Each
PRUNE record has XID 0. The records appear in ascending heap block
number order, since there is a sequential scan involved (we go through
heapgetpage() to get to heap_page_prune_opt(), where the query prunes
opportunistically).

Another slightly surprising fact revealed by the patch is the ratio of
opportunistic prunes ("Heap2/PRUNE") to prunes run during VACUUM
("Heap2/PRUNE+BYVACUUM") with the regression tests:

│ resource_manager/record_type │ Heap2/PRUNE                 │
│ count                        │ 4,521                       │
│ count_perc                   │ 0.220                       │
│ rec_size                     │ 412,442                     │
│ avg_rec_size                 │ 91                          │
│ rec_size_perc                │ 0.194                       │
│ fpi_size                     │ 632,828                     │
│ fpi_size_perc                │ 1.379                       │
│ combined_size                │ 1,045,270                   │
│ combined_size_perc           │ 0.404                       │
├─[ RECORD 61 ]────────────────┼─────────────────────────────┤
│ resource_manager/record_type │ Heap2/PRUNE+BYVACUUM        │
│ count                        │ 2,784                       │
│ count_perc                   │ 0.135                       │
│ rec_size                     │ 467,057                     │
│ avg_rec_size                 │ 167                         │
│ rec_size_perc                │ 0.219                       │
│ fpi_size                     │ 546,344                     │
│ fpi_size_perc                │ 1.190                       │
│ combined_size                │ 1,013,401                   │
│ combined_size_perc           │ 0.391                       │
├─[ RECORD 62 ]────────────────┼─────────────────────────────┤
│ resource_manager/record_type │ Heap2/VACUUM                │
│ count                        │ 3,463                       │
│ count_perc                   │ 0.168                       │
│ rec_size                     │ 610,038                     │
│ avg_rec_size                 │ 176                         │
│ rec_size_perc                │ 0.286                       │
│ fpi_size                     │ 893,964                     │
│ fpi_size_perc                │ 1.948                       │
│ combined_size                │ 1,504,002                   │
│ combined_size_perc           │ 0.581                       │
├─[ RECORD 63 ]────────────────┼─────────────────────────────┤
│ resource_manager/record_type │ Heap2/VISIBLE               │
│ count                        │ 7,293                       │
│ count_perc                   │ 0.354                       │
│ rec_size                     │ 431,382                     │
│ avg_rec_size                 │ 59                          │
│ rec_size_perc                │ 0.202                       │
│ fpi_size                     │ 1,794,048                   │
│ fpi_size_perc                │ 3.909                       │
│ combined_size                │ 2,225,430                   │
│ combined_size_perc           │ 0.859                       │


--
Peter Geoghegan