One deleted row disables metadata aggregates for the whole table, not just its row group

[jdatcmd]

Found while re-running the benchmark suite for the docs.

Measured

PostgreSQL 17.10 non-assert, 6,000,000 rows, one columnar table, median of 5:

state	count(*)	sum/avg	min/max
no deletes	0.02 ms	0.29 ms	0.30 ms
after deleting exactly 1 row	222.28 ms	233.89 ms	317.22 ms
after pgcolumnar.vacuum	0.02 ms	0.30 ms	0.30 ms

A single deleted row out of six million costs about 11,000x on count(*), and
vacuuming restores it.

Why

The fallback is correct: a zone map covers every row in its group including
deleted ones, so once anything is deleted the metadata answer is wrong. But the
test is storage-wide. columnar_native_agg_exec asks
ColumnarStorageHasDeleteVector(storageId, snapshot) and, if anything anywhere in
the storage has a delete vector, sends the entire aggregate down
columnar_native_scan_agg.

On the table above that means one deleted row in one group forces a full decode of
all 40 groups, including the 39 that have no deletes at all.

What could be done instead

Fold per row group rather than per storage: for each group, use the zone map when
that group has no delete-vector entry and scan only the groups that do. The
per-group delete-vector lookup is already how the scan path works, and
ColumnarReadDeleteVectorList(storageId, groupNumber, snap) is the existing call.

Cost of the current behaviour scales with how spread out the deletes are, and the
worst case is the common one: a handful of scattered deletes across a large table
takes every group off the fast path.

Not a correctness problem, and there is a workaround (pgcolumnar.vacuum), but
the cliff is steep and undocumented until now. docs/benchmarks.md records the
measurement.

[ChronicallyJD]

Reproduced on PostgreSQL 18.4, and there is a second half to this that changes what the fix has to touch.

Reproduced

2,000,000 rows, 14 row groups, EXPLAIN (ANALYZE, TIMING OFF) execution time, best of three:

state	count(*)	min/max
no deletes	0.006 ms	0.085 ms
after deleting exactly 1 row	90.941 ms	119.274 ms
after pgcolumnar.vacuum	0.008 ms	0.071 ms

Same cliff you measured, at a quarter of the scale.

The planner also stops choosing the path

The estimate moves with it, because ColumnarAddVecAggPaths prices the path with the same storage-wide probe: hasDeletes makes it inherit the cheapest scan's cost.

no deletes             cost=2.60..2.60
after deleting 1 row   cost=20069.00..20069.00

And with parallelism available at its defaults, that is enough to lose the plan outright:

no deletes, workers available:
    Custom Scan (ColumnarScan)
      Columnar Vectorized Aggregates: 1

after deleting 1 row, workers available:
    Finalize Aggregate
      ->  Gather
            Workers Planned: 4
            ->  Partial Aggregate

That is the #133 plan shape, back again. So one deleted row does not just disable the metadata answer, it un-does #140 for that table: the path is priced as a full scan, and the planner reasonably prefers a parallel one. (It is not wrong to prefer it. 46 ms parallel against 96 ms serial here. The defect is that the metadata path was taken off the table at all.)

Consequence for the fix: the executor and the cost model have to move together. Folding per row group in columnar_fill_native_metadata_agg alone would make the path fast and leave the planner refusing to choose it, which is the same end result for a user with one deleted row.

The per-group fold is sound, with one ordering constraint

Worth stating explicitly since it is the part that could go wrong quietly. Per aggregate:

• count(*): sum rowCount over clean groups, add the scanned count of dirty ones.
• count(col): same with the zone map's value_count.
• min/max: fold clean groups' stored extremes with the scanned extremes of dirty groups.
• sum: add stored sums to scanned sums. avg is the folded sum over the folded count, not an average of averages.

A group whose rows are all deleted is dirty, so it is scanned, produces nothing, and contributes nothing. A clean group cannot be empty.

The constraint: the per-group lookup must stay after the existing ColumnarFlushDeleteVectorForRelation call in ColumnarExecAggScan. Deletes buffered in the current transaction are not in the catalog until that flush, so a per-group ColumnarReadDeleteVectorList before it would call a group clean that this transaction has already deleted from, and answer from the zone map. The storage-wide probe has the same dependency today, so this is a constraint to preserve rather than a new one, but it is easy to lose while moving the check inside the loop.

Suggested shape

1. Per-group fold in the executor, after the flush.
2. Cost model priced the same way: clean groups as metadata reads, dirty groups at scan cost, so the planner keeps choosing the path when most groups are clean.
3. A test that pins the cliff is gone: same table, count(*) timed with zero deletes and with one, asserting the ratio rather than a millisecond figure, plus the plan shape with workers available so a regression to Gather is caught.

While the cost model is open, the divisor there is also wrong independently of this: it derives the group count from chunk_group_row_limit, but row groups are governed by stripe_row_limit. At the defaults that is 60 computed against 4 actual. Raised in review of #140 and not taken; it would be natural to fix in the same change.

[ChronicallyJD]

Correcting part of my analysis above before it gets built on.

I claimed that with parallelism available the plan degrades to Finalize Aggregate → Gather → Partial Aggregate, and that one deleted row therefore un-does #140. I cannot reproduce that and I now believe it was wrong.

Re-measuring on 18.4, 2,000,000 rows in 14 row groups, with min_parallel_table_scan_size = 0, parallel_setup_cost = 0, parallel_tuple_cost = 0 and up to 16 workers, both before and after the delete:

Custom Scan (ColumnarScan)  (cost=20109.00..20109.00 rows=1 width=8)

The plan does not change. The reason is a clamp already in the cost model — the path is priced at min(computed, cheapest->total_cost) — so once deletes exist it ties with the cheapest scan rather than losing to it, and the tie goes to the custom path. That clamp is doing the work my analysis credited to the plan shape.

What stands:

• The executor defect is real and is the whole of this issue. count(*) 95.07 ms against 0.04 ms, min/max 118.04 against 0.13, on the same table with one row of two million deleted.
• The estimate really does move, 2.60 to 20109.00, and an estimate that says 20109 for work that takes 0.15 ms is worth fixing on its own.

What does not:

• "One deleted row re-opens pgcolumnar.enable_metadata_count is an orphaned GUC, and count(*) loses to a parallel scan by default #133." It does not, at least not at any scale or setting I can produce. My point 2 above ("the executor and the cost model have to move together, or the planner refuses to choose it") was the practical conclusion drawn from that, and it was wrong too — the executor fix alone would have worked, because the clamp keeps the path chosen.

I have opened #151 with the per-group fold. It still changes the cost model, but as hardening rather than as a fix for a live planner regression, and the PR says so.

Sorry for the noise. I should have checked the plan against a range of settings before writing it up as a second defect rather than reporting the one run I had.