Re: [PATCH] Reduce pg_class scans in GRANT/REVOKE ON ALL TABLES IN SCHEMA - Mailing list pgsql-hackers

  I ran a benchmark on the patch *Reduce pg_class scans in GRANT/REVOKE
  ON ALL TABLES IN SCHEMA* (collapses 5 per-relkind pg_class heap scans
  into 1 scan distributed into per-relkind buckets). Summary below.(It took much time to tests)


  ## Assumptions

  - Two builds of PostgreSQL 19devel from the same source tree (one
    patched, one at master tip), identical compile flags, separate
    --prefix.
  - Separate data directories, run sequentially on an otherwise idle
    host.
  - GUCs: shared_buffers=2GB, max_locks_per_transaction=100000,
    fsync=off, synchronous_commit=off, full_page_writes=off,
    autovacuum=off.
  - bench_s schema contains N empty tables (CREATE TABLE t_i()).
  - Measured operations:
      GRANT  SELECT ON ALL TABLES IN SCHEMA bench_s TO   bench_role
      REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA bench_s FROM bench_role
  - Best of 3 runs reported (seconds).
  - Two scenarios:
      A. Clean catalog   — VACUUM FULL pg_class; VACUUM FULL pg_attribute
                           immediately before measurement. pg_class
                           densely packed.
      B. Bloated catalog — pre-bloat phase: GRANT+REVOKE on all N tables
                           repeated C cycles, no VACUUM. Both patched
                           and master operate on catalogs with identical
                           relpages and n_dead_tup.


  ## Results — Scenario A (Clean catalog)

  macOS (Apple Silicon), best of 3, seconds:

    ntables | GRANT patched | GRANT master | REVOKE patched | REVOKE master
    --------+---------------+--------------+----------------+--------------
     20,000 |     0.116     |    0.115     |     0.231      |    0.226
     40,000 |     0.250     |    0.250     |     0.460      |    0.460
    100,000 |     0.730     |    0.678     |     1.193      |    1.193

  Honestly, there is no measurable performance difference in the clean
  state. Patched and master are statistically indistinguishable within
  run-to-run noise. This matches the design of the patch: when pg_class
  is densely packed, repeating a small seq scan five times is cheap, so
  collapsing it into one has nothing meaningful to save. The patch adds
  no overhead either — worst case is a tie.


  ## Results — Scenario B (Bloated catalog)

  ### Linux x86_64, C=20, best of 3, seconds

    ntables   | dead_tup  | GRANT patched | GRANT master |   Δ     | REVOKE patched | REVOKE master |   Δ
    ----------+-----------+---------------+--------------+---------+----------------+---------------+---------
       10,000 |         0 |    0.0924     |    0.0935    |  −1.2 % |     0.1668     |    0.1696     |  −1.6 %
       20,000 |   109,825 |    0.2027     |    0.2069    |  −2.0 % |     0.3381     |    0.3533     |  −4.3 %
       50,000 |   329,468 |    0.5555     |    0.5895    |  −5.8 % |     0.8901     |    0.9371     |  −5.0 %
      100,000 |   879,311 |    1.1732     |    1.1968    |  −2.0 % |     1.8808     |    1.9555     |  −3.8 %
      200,000 | 1,978,925 |    2.2188     |    2.3470    |  −5.5 % |     3.7290     |    3.9064     |  −4.5 %
      500,000 | 4,178,604 |    6.0260     |    6.6663    |  −9.6 % |     9.8162     |   10.2169     |  −3.9 %
    1,000,000 | 9,678,399 |   12.9241     |   14.7657    | −12.5 % |    24.8893     |   28.7566     | −13.4 %

  ### macOS (Apple Silicon), C=20 (C=10 at 1M), best of 3, seconds

    ntables   | dead_tup  | GRANT patched | GRANT master |   Δ    | REVOKE patched | REVOKE master |   Δ
    ----------+-----------+---------------+--------------+--------+----------------+---------------+--------
       20,000 |   299,960 |    0.168      |    0.163     |  +3 %  |     0.260      |    0.278      |  −6 %
       40,000 |   519,601 |    0.307      |    0.307     |   0 %  |     0.552      |    0.564      |  −2 %
      100,000 |   959,268 |    0.784      |    0.934     | −16 %  |     1.405      |    1.419      |  ~0 %
      200,000 | 2,058,886 |    1.787      |    1.878     |  −5 %  |     2.745      |    2.849      |  −4 %
      500,000 | 4,258,565 |    4.727      |    5.197     |  −9 %  |     7.126      |    7.908      | −10 %
    1,000,000 | 9,758,364 |   10.977      |   11.126     |  −1 %  |    19.473      |   20.759      |  −6 %

  Negative Δ = patched faster. Under catalog bloat the patch produces a
  consistent, reproducible improvement on both operating systems.


  Happy to share the bench scripts and raw logs on request.

  Thanks,
  charsyam