performance_tests.txt

Performance Results
===================

Test Environment:
- CPU: Apple M2 Pro (12 cores)
- RAM: 32 GB
- OS: macOS 26.5.1 (darwin 25.5.0, arm64)
- Compiler: Apple clang 21.0.0 (clang-2100.1.1.101)
- PostgreSQL: 19beta1 (built from source, -O2, no --enable-cassert)
- Disk: internal SSD (APFS)

Wall Clock Timing: pg_dump --schema-only (7 runs each, milliseconds)
---------------------------------------------------------------------

Database: perf_baseline
  pg_init_privs rows: 249 | user functions/aggs: 0

  Unpatched:  49.69  52.29  46.42  49.73  63.11  51.58  53.50
  Patched v6: 52.49  50.23  50.02  51.87  48.83  65.23  64.58

  Median unpatched: 51.58 ms
  Median patched:   52.49 ms
  Delta: +0.91 ms (+1.8%)

Database: perf_10k_functions
  pg_init_privs rows: 249 | user functions/aggs: 10500

  Unpatched:  1619.04  1674.27  1638.30  1598.82  1597.56  1613.76  1591.44
  Patched v6: 1582.23  1584.01  1657.28  1598.13  1573.98  1576.01  1572.51

  Median unpatched: 1613.76 ms
  Median patched:   1582.23 ms
  Delta: -31.53 ms (-2.0%)  [within noise, patched is NOT slower]

Database: perf_dangling_500
  pg_init_privs rows: 749 (500 dangling) | user functions/aggs: 500

  Unpatched:  107.28  104.59  104.23  102.89  102.20  96.26  102.95
  Patched v6: 103.90  105.64  104.61  105.18  106.71  105.50  107.20

  Median unpatched: 102.95 ms
  Median patched:   105.50 ms
  Delta: +2.55 ms (+2.5%)

Summary Table:

  | Database           | initprivs rows | Median unpatched | Median patched | Delta      |
  |--------------------|----------------|------------------|----------------|------------|
  | perf_baseline      | 249            | 51.58 ms         | 52.49 ms       | +0.9 ms    |
  | perf_10k_functions | 249            | 1613.76 ms       | 1582.23 ms     | -31.5 ms * |
  | perf_dangling_500  | 749            | 102.95 ms        | 105.50 ms      | +2.6 ms    |

  * Negative delta = within measurement noise; the patch does NOT slow
    down large-schema dumps.

EXPLAIN ANALYZE: getAdditionalACLs() Query Isolation
-----------------------------------------------------

Database: perf_baseline (249 rows, no dangling entries)

  Unpatched:
    Seq Scan on pg_init_privs (actual time=0.005..0.014 rows=249 loops=1)
      Buffers: shared hit=3
    Planning Time: 0.198 ms
    Execution Time: 0.043 ms

  Patched v6:
    Seq Scan on pg_init_privs pip (actual time=0.042..1.037 rows=249 loops=1)
      Buffers: shared hit=5
      SubPlan array_1
        -> Function Scan on unnest elt (actual time=0.003..0.003 rows=1.65 loops=249)
             SubPlan exists_1
               -> Function Scan on aclexplode ace (actual time=0.001..0.001 rows=0 loops=411)
                    SubPlan exists_3
                      -> Seq Scan on pg_authid (rows=18 loops=1) [hashed]
                    SubPlan exists_5
                      -> Seq Scan on pg_authid (rows=18 loops=1) [hashed]
    Planning Time: 0.620 ms
    Execution Time: 1.082 ms

  Overhead: +1.04 ms (one-time cost at dump startup)

Database: perf_dangling_500 (749 rows, 500 dangling entries)

  Unpatched:
    Seq Scan on pg_init_privs (actual time=0.005..0.032 rows=749 loops=1)
      Buffers: shared hit=9
    Planning Time: 0.230 ms
    Execution Time: 0.069 ms

  Patched v6:
    Seq Scan on pg_init_privs pip (actual time=0.038..2.174 rows=749 loops=1)
      Buffers: shared hit=96
      SubPlan array_1
        -> Function Scan on unnest elt (actual time=0.002..0.002 rows=0.55 loops=749)
             Rows Removed by Filter: 1
             SubPlan exists_1
               -> Function Scan on aclexplode ace (actual time=0.001..0.001 rows=0.55 loops=911)
                    SubPlan exists_3
                      -> Seq Scan on pg_authid (rows=18 loops=1) [hashed]
                    SubPlan exists_5
                      -> Seq Scan on pg_authid (rows=18 loops=1) [hashed]
    Planning Time: 0.517 ms
    Execution Time: 2.225 ms

  Overhead: +2.16 ms (worst case: 500 dangling entries filtered)

Key observation: pg_authid is scanned ONCE and hashed (loops=1). The
hashed subplan is reused for all 749 rows, making the overhead O(n) in
pg_init_privs rows with a very small constant.

EXPLAIN ANALYZE: getAggregates WHERE Clause (UNCHANGED)
--------------------------------------------------------

Database: perf_10k_functions (500 user aggregates, 10000 user functions)

  Hash Left Join (actual time=1.004..1.113 rows=500 loops=1)
    Hash Cond: (p.oid = pip.objoid)
    Filter: (p.pronamespace <> ... OR p.proacl IS DISTINCT FROM pip.initprivs)
    Rows Removed by Filter: 163
    Buffers: shared hit=348
    -> Seq Scan on pg_proc p (actual time=0.039..1.029 rows=663 loops=1)
         Filter: (prokind = 'a')
         Rows Removed by Filter: 13274
    -> Hash (actual time=0.022..0.022 rows=69 loops=1)
         -> Seq Scan on pg_init_privs pip (actual time=0.010..0.013 rows=69 loops=1)
              Filter: (classoid = 1255 AND objsubid = 0)
              Rows Removed by Filter: 180
  Planning Time: 0.834 ms
  Execution Time: 1.168 ms

  This query is byte-for-byte IDENTICAL in patched and unpatched code.
  The patch does NOT modify any WHERE clause. Timing is identical.


The filtering adds ~1ms (249 rows) to ~2ms (749 rows) to the one-time
getAdditionalACLs() query that runs at pg_dump startup. This is a fixed
cost that does NOT scale with the number of functions, aggregates, or
other objects in the database.

The critical performance test is perf_10k_functions (10,000 functions +
500 aggregates): the patched version shows NO regression. The median is
actually 31ms faster, which is just noise. The per-object queries
(getAggregates, getFuncs) are completely unchanged and produce identical
execution plans.