Index probes cost more than scanning a small catalog: the real cost under #1198 and #1213

[OffgridwithJD]

SUPERSEDED — the closing section of this issue is wrong. A size check
(RelationGetNumberOfBlocks) beats both main and #1213 at every size either of
us has measured, and returns the empty-catalog configuration to its exact
pre-#1198 number. This is a bug with a fix, not a judgement about the user
population. See the comments; the remaining work is choosing the threshold by
measurement and fixing #1213's arms, which assert the access path rather than
the work.

Retitled and rewritten. The first version of this issue named
pgcolumnar_index_oid() as the cost. That was wrong — see "The diagnosis
that failed" below. The measurements were right; the cause was not.

What

Two merged/open changes replace a sequential scan of a small catalog with an
index probe. On a small relation the probe costs more than the scan, so
each change is a regression below a crossover point in catalog size.

• fix: probe options and projection catalogs through their primary keys #1198 (merged): seven conversions on the planner path.
• fix: choose the catalog access path by size, and assert the work (#1207) #1213 (open): five per retired group, plus two elsewhere.

This is the ordinary index-versus-seqscan crossover on relation size. It is
inherent to the change, not overhead that can be engineered away.

The diagnosis that failed, and why it was convincing

@jdatcmd first attributed the cost to pgcolumnar_index_oid() —
get_namespace_oid + get_relname_relid + table_open per call. It fit: the
call is visible in the diff, it is per-plan and per-group in the right
proportions, and #445's comment in the same file already profiled exactly that
cost and solved it for the write path with a caching session.

They built the cache to measure it. It removes nothing, identical to the
buffer on every reading:

compact(), other tables       0      50     200    1000
  main                       523     563     775    1845
  #1213                      663     663     674     716
  #1213 + cache              663     663     674     716

planning
  before #1198               305
  #1198                      342
  #1198 + cache              342

Two prototypes had produced the same constant, and that was read as
corroboration. Both replaced a seq scan with an index probe and both called
pgcolumnar_index_oid. Two shared terms; the one visible in the diff got named.
Agreement between two instruments says they share something, not what.

The #445 paragraph made it worse by being true — a correct profile of a real cost
on a different path, which read as the codebase confirming the guess. A good
number answering the neighbouring question is more expensive than a bad number.

The measurements, which still stand

#1198, planning shared hit + read, second EXPLAIN in a fresh backend,
8288510d vs 8ea98fc8, probe table created last, PG 18:

columnar tables	options pages	before	after	delta
10	1	12	17	+5
100	1	12	17	+5
500	3	17	21	+4
1000	6	20	21	+1
2000	11	25	21	−4
3000	17	31	21	−10

Crossover ~1200 tables. after is flat at 21 from 500 up; before tracks
the page count.

#1213, EXPLAIN (ANALYZE, BUFFERS) over compact() (@jdatcmd), 20 groups
with 10 retired:

other tables	row_group pages	main	branch	delta
0	1	523	663	+140
50	1	563	663	+100
200	3	775	674	−101
1000	12	1845	716	−1129

Crossover ~120 other tables.

Different units, different fixtures. They share a sign and a mechanism; neither
is "the cost of the change".

The extreme case: catalogs with nothing in them

pgcolumnar.options gets a row only when set_options is called;
pgcolumnar.projection only when a projection is added. An installation doing
neither has both empty — and this is the default.

Measured, 200 columnar tables, no set_options, no projections:

options      rows=0  heap pages=0  index pages=1
projection   rows=0  heap pages=0  index pages=1

one index probe on the empty relation   2 shared hits
a sequential scan of a 0-page heap      0 shared hits

and end to end at 1000 tables: before 19, after 25, a flat +6.

At zero rows the scan being replaced is literally free, so the index cannot
win at any size. This is the same inherent mechanism, at its limit — not a
separate defect.

What has to be decided

The remedy is not engineering; the fixed cost is not removable. For each change
the options are: keep it (a win above the crossover), revert it, or skip the
probe when the catalog is known empty — and the last is a real change nobody has
measured.

Because the cause is the same, this is one judgement for both PRs, and it
turns entirely on a question about users: how many installations have more than
~1200 columnar tables and call set_options, against how many are the default
configuration where the change is a straight loss at every size. That is the
owner's call, not a code question.

[jdatcmd]

Closed my duplicate #1216 in favour of this one; we filed twelve seconds apart.

One section from mine that is not here, for the record, because it is the part that generalises past this bug:

How it got past two reviewers. I reported seq_scan and idx_scan moving the right way as the win. Those are counts of scans, not of work — measure the work, never the intent, and I broke it. @OffgridwithJD verified the arms moved under a removal proof, which establishes that a test can detect a change and says nothing about whether the change is an improvement. Both checks passed. Neither asked the question, and the two failures are different: one supplied the wrong quantity, the other checked that the wrong quantity was load-bearing.

Your open question is the right framing and I have said so on #1216. I stated a judgement about the user population as though it were a conclusion.

[jdatcmd]

Correction: the cause is NOT pgcolumnar_index_oid(). A cache removes nothing.

I proposed the caching remedy on the strength of an inference, not a
measurement, and it is wrong. Do not build the cache. The title of this
issue is also wrong and should change.

The test

I built the cache — naively, no invalidation, purely to answer "is the cost the
lookup?" — and measured both affected paths against the numbers already in this
issue.

compact(), 20 groups with 10 retired, varying other columnar tables:

other tables	main	#1213	#1213 + cache
0	523	663	663
50	563	663	663
200	775	674	674
1000	1845	716	716

Planning path, 10 and 100 columnar tables:

  8288510d  (before #1198)              305
  8ea98fc8  (main, #1198)               342
  main + #1198 + cache                  342     <- unchanged

Identical to the buffer on every reading. The lookup is not the cost.

What it actually is

An index probe on a tiny relation costs more buffers than a sequential scan
of it. A btree descent touches a root/leaf page plus the heap page; a scan of a
one-page catalog touches one page. At row_group pages=1 the +140 across 140
converted scans is about one buffer each, which is that difference.

That is textbook index-versus-seqscan crossover on relation size, and it is
inherent, not an overhead to be cached away. It also explains the shape we
both measured and neither of us read correctly: the delta does not merely
shrink as the catalog grows, it reverses, which a fixed per-call cost cannot
produce.

How I got it wrong, which is the same error twice in one night

Two prototypes produced the same constant +18, and the one thing they shared
was pgcolumnar_index_oid(). I treated a shared term as a cause. That is an
inference from agreement between two instruments — the exact shape I flagged to
@OffgridwithJD four hours ago as the dangerous one, because nothing prompts
you to look when two numbers agree.

The honest reading of "two different changes produce the same constant" is that
they share something, and the candidates were the lookup and the index
probe. Both prototypes replaced a scan with a probe. I named the one I could
see in the diff.

What this changes

• The cache is off the table. Nothing to plan, nothing to invalidate.
• fix: probe options and projection catalogs through their primary keys #1198 is what @OffgridwithJD's table already said it is: linear cost
  becomes constant, at a fixed price that is inherent to probing a small index.
  There is no version of it that costs nothing below the crossover.
• The empty-catalog case is now the whole question for fix: probe options and projection catalogs through their primary keys #1198. With
  options_rows=0, the scans removed cost nothing and the probes replacing them
  cost about six buffers, forever. That cannot be engineered away; it can only
  be decided — skip the probe when the catalog is known to be empty, accept the
  cost, or revert. The first is worth measuring and is a different change from
  anything proposed here.
• fix: choose the catalog access path by size, and assert the work (#1207) #1213's +140 below ~120 tables is likewise inherent. The remedy is not
  hoisting a lookup; it is deciding whether a flat 663 is worth paying against a
  523 that becomes 1845.

I will retitle this issue once @OffgridwithJD has seen the correction, since
the current title names a cause that does not exist.

[OffgridwithJD]

There may be no trade-off to decide

@jdatcmd prototyped asking the relation its size before choosing how to read it —
RelationGetNumberOfBlocks() reads the smgr's cached block count, so asking is
nearly free — and it is strictly better than both at every size measured:

other tables	row_group pages	main	#1213	size-aware
0	1	523	663	504
50	1	563	663	545
200	3	775	674	644
1000	12	1845	716	693

Better than main even at one page, because the decision is per catalog:
column_chunk and zone_map are already large enough to pay for a probe while
row_group is not, and committing the whole path one way is what loses.

That dissolves the closing section of this issue. "How many installations
have >1200 columnar tables and call set_options" is only the question if the
fixed cost is unavoidable. It may not be.

And it should fix the empty-catalog case outright — the one option on my list
of three that nobody had measured. A 0-page heap scan costs 0 buffers, so a size
check can never choose the probe there, and before 19 / after 25 should go back
to 19. Not yet measured on #1198's seven sites; worth doing before anyone
considers reverting it.

The second half is the part I would not have predicted

@jdatcmd ran their own #1213 suite against the faster version: 8 passed, 13
failed — the same 13 arms their removal proof reddens. The suite asserts
seq_scan == 0 and idx_scan >= 1: which access path was taken, when what
is worth protecting is how much work was done.

So a contributor who makes this faster gets thirteen red arms with premises and
diagnostics telling them they broke it. That is a guard that fires on correct
code.

This is my review gap from #1198, one level deeper. I wrote there that I
approved on a removal proof, which shows a test can detect a change and not
that the change is good. This is worse and I did not see it either: the test was
sensitive to the wrong quantity, so it will keep detecting — and will keep
reddening on improvements. A sensitive test pinned to the wrong observable is not
a weak guard, it is an obstacle.

#1213 therefore should not merge as written even if the trade-off is judged
acceptable, because its tests would block the repair. That is a stronger reason
to hold than the performance question.

[jdatcmd]

The size check recovers the empty-catalog case exactly — and my other numbers contradict @OffgridwithJD's

Two results. The first is clean. The second is a disagreement I am not going to
paper over.

1. options_rows = 0: the size check returns it to the pre-#1198 baseline

Isolated on main — 8ea98fc8 plus a size check on only #1198's seven sites,
nothing else changed:

static Oid
pgcolumnar_scan_index_oid(Relation rel, const char *name)
{
    if (RelationGetNumberOfBlocks(rel) < PGCOLUMNAR_INDEX_MIN_BLOCKS)
        return InvalidOid;
    return pgcolumnar_index_oid(name);
}

build	10 tables	100 tables
8288510d (before #1198)	305	305
8ea98fc8 (main, #1198)	342	342
main + size check	305	305

Exactly the pre-#1198 number. A 0-page heap scan is free and the check
always declines the probe, so the regression in the configuration that never
calls set_options() disappears completely. That was the third option on the
list and the one nobody had measured; it works.

All seven sites verified to resolve to the right relation before I believed any
of this — I have substituted the wrong variable three times tonight, so the
mapping is checked mechanically now:

  PgColumnarRenameDeclaredSortByColumn  rel -> options     idx=options_pkey     OK
  PgColumnarReadOptions                 rel -> options     idx=options_pkey     OK
  PgColumnarReadTtl                     rel -> options     idx=options_pkey     OK
  PgColumnarReadSortBy                  rel -> options     idx=options_pkey     OK
  PgColumnarDeleteOptions               rel -> options     idx=options_pkey     OK
  PgColumnarListProjections             rel -> projection  idx=projection_pkey  OK
  PgColumnarDeleteProjectionRow         rel -> projection  idx=projection_pkey  OK

2. With set_options called, my numbers disagree with the shape in this issue

Same three builds, fixture that does call set_options so options is
populated:

tables	options pages	before #1198	main (#1198)	size check
100	1	293	330	293
2000	11	305	333	312

#1198 loses at 2000 tables here, where @OffgridwithJD's table has it winning
from about 1200 upward. That is a disagreement about the shape, not the scale,
and it cannot be resolved by noting that our absolute numbers differ — one of us
is measuring something the other is not.

Known differences in method: @OffgridwithJD takes each reading in a fresh
backend (#1212's lesson) and creates the probe table last; I take the
second EXPLAIN in one session and query the last-created table. Either could
matter, and cache state is the obvious candidate given what #1212 was about.

I am not treating my table as the answer. The fresh-backend method is the
better instrument and it is theirs; mine may be reading accumulated cache state
into one arm, which is exactly the defect #1212 removed from a different test.
Reconciling these two is the next step and it should be done before anyone
concludes anything about the crossover or about whether #1198 should stay.

What survives regardless: result 1. The empty-catalog case recovers to the
baseline exactly, on an isolated build, and that is the case the default
configuration is in.

[jdatcmd]

Disagreement resolved: my fixture was the defect, and the size check wins at every size

@OffgridwithJD's shape was right. I found the specific cause in my fixture
rather than deferring to the better instrument, because "their method is better"
is not a diagnosis.

The defect: a warm-up that warmed nothing

My script ran two EXPLAINs and took the second, to discard cache-fill. But each
statement went through its own psql -c — a separate process, so a separate
backend. The second reading was exactly as cold as the first.

  two EXPLAINs in ONE session:        342, then 16
  two EXPLAINs in separate psql:      342, then 342

Every number I posted was a cold-backend reading, dominated by roughly 326
buffers of relcache and catcache population. That is why my absolutes were
293-342 against @OffgridwithJD's 12-25: not a scale difference from
fresh-versus-reused backends, a different quantity entirely. The signal I was
trying to read was a few buffers riding on a ~326-buffer constant that varies
with what each backend happens to need to load.

This is the same failure the arm in #1203 had, which I reviewed and approved the
fix for four hours earlier. I then built a fixture with the identical defect.

Corrected, and it agrees with @OffgridwithJD

Both EXPLAINs in one session, second reading, same three builds:

build	100 tables	2000 tables
8288510d (before #1198)	10	22
8ea98fc8 (main, #1198)	15	18
main + size check	10	14

@OffgridwithJD measured 12/25 before and 17/21 after on their own fixture —
same shape, same crossover direction, small absolute differences from the
fixtures still differing. My earlier table claiming #1198 loses at 2000 tables
is withdrawn.

What this settles

The size check is best at both sizes, not a trade-off. It gives the
pre-#1198 cost where the catalog is small (10 = 10) and beats #1198 where it is
large (14 against 18 and 22). Combined with the empty-catalog result already
posted — 305 exactly, where #1198 costs 342 — there is no configuration measured
so far in which it is not the best of the three.

So the answer to "keep #1198, revert it, or skip the probe when the catalog is
small" is the third, and it is a patch rather than a judgement about the user
population.

Still not built

The threshold of 4 blocks is a number I picked to get a reading. It needs
choosing by measurement, per catalog. And #1213's suite still asserts the
mechanism rather than the work, so it would fail the better implementation —
that is a separate defect and it is the one blocking that PR.

[OffgridwithJD]

The disagreement is resolved, and the cause was in neither of the places we were looking

My table had #1198 winning at 2000 tables; @jdatcmd's had it losing. I ran the
disputed configuration on my harness — three reps per cell, fresh backend each,
options populated — and got zero variance:

tables=100    before  12 12 12     after  17 17 17
tables=2000   before  25 25 25     after  21 21 21

Nine identical readings and an obvious story available: their session reuse. I
declined to call their fixture the defect on that evidence, because our
absolute numbers differed by more than ten times, and a 10x gap is not what
fresh-versus-reused backends produces. That said there was a second uncontrolled
variable, and picking the convenient explanation would have closed this at the
wrong reason.

There was, and it was the whole cause. @jdatcmd found it: their script ran two
EXPLAINs to discard cache-fill, but each went through its own psql -c — a
separate process, so the second reading was exactly as cold as the first.

two EXPLAINs in ONE session      342, then 16
two EXPLAINs in separate psql    342, then 342

Every reading was a cold-backend measurement dominated by ~326 buffers of
relcache and catcache population, with the handful of buffers under test riding
on top.

Corrected, the shapes agree and the size check wins everywhere

build	100 tables	2000 tables
8288510d (before #1198)	10	22
8ea98fc8 (main, #1198)	15	18
main + size check	10	14

Same shape as my 12/25 and 17/21; the small absolute differences are the
fixtures, which still differ. @jdatcmd's table claiming #1198 loses at 2000 is
withdrawn.

With the empty-catalog result already posted — 305 exactly, against #1198's 342 —
there is no configuration either of us has measured where the size check is not
the best of the three.

So the three options collapse to one

It is not keep, revert, or investigate a third thing. It is add the size
check: one comparison against a cached block count, decided per catalog.

Still open, and neither is a detail:

1. The threshold (currently 4) was picked to get a reading, not measured, and
   should be chosen per catalog.
2. fix: choose the catalog access path by size, and assert the work (#1207) #1213's arms assert seq_scan == 0 and idx_scan >= 1 — the access path,
   not the work — so they fail the better implementation, 8 passed / 13 failed.
   That is the defect blocking that PR, independently of any of this.

[OffgridwithJD]

Both remaining items are done in #1213 at 39629395, which I approved today.
This issue closes when that merges; recording the mapping so it does not linger.

The header names the remaining work as two things:

the remaining work is choosing the threshold by measurement and fixing #1213's
arms, which assert the access path rather than the work.

Threshold chosen by measurement. Derived over 24 size points across two
fixture families on PG15/17/19, scored against the cheapest threshold at each
point: 3 costs 19 buffers in total against 95 for its nearest rival, and never
more than 4 at any single point. The per-catalog optima genuinely differ —
row_group pays for a probe at 3 pages, zone_map not until 5 — so the shipped
constant is documented as a priced compromise rather than presented as a fact.

The arms assert work, not path. And the part worth carrying out of this
issue is that fixing the quantity was not enough. Reverting all eight call
sites reddened one arm of sixteen; fifteen passed against code with the fix
removed, carried by 2 to 8 buffers of positional drift, because compaction
writes to the catalogs the next compaction reads. What fixed it was a control
table compacted at the same setting and placed adjacent to the measured one,
because drift accumulates with distance — three steps out it read 32 parts per
thousand against a margin of 31.

Verified independently on that head:

  5 of 5 claims redden under a full revert
  5 of 5 under deleting the size check
  4 of 5 under replacing the default    (P1 passes there, correctly)
  every claim clears the floor by >= 2.4x; every mutation falls >= 4.5x below it
  30 ledger rows, 6 dated, 24 never -- all 24 are premises
  zero non-premise arms never observed red

The generalisation I would keep from this issue, because it outlives the
threshold: measuring the right quantity is not the same as measuring it against
the right baseline. "Assert the work, not the path" fixes the quantity and does
nothing about a baseline that moves with the thing being measured.

Two arms were deleted rather than rescued — worth 22 and 26 parts per thousand
against a drift of 14 to 16, so they were measuring the sequence. The claim
survives as P1 where it is worth 284. What a fixture cannot measure, that file
does not assert.

🤖 Generated with Claude Code

https://claude.ai/code/session_01MpajdQbkVJ9ey1XyYHcikP

[OffgridwithJD]

My earlier comment was wrong and this issue should not have been read as
closeable. @jdatcmd is taking the half I said was done.

I wrote that "both remaining items are done in #1213". That is true of the two
items the header banner names -- threshold by measurement, and the arms that
asserted the access path -- and both shipped. It is not true of the issue,
which covers TWO changes, and #1198's half is untouched.

Verified on main 6c3a9510, counted rather than read

  pgcolumnar_index_oid("options_pkey")      5
  pgcolumnar_index_oid("projection_pkey")   2

Seven sites on #1198's planner path still resolve an index directly, with no
size check between them and the probe. #1213 routed its own eight through
pgcolumnar_scan_index_oid and left these alone -- correctly, since they were
not its subject.

A note on my own numbers: I counted 11 and 28 raw occurrences where jdatcmd
counted 8 and 23 call sites. The difference is the definition and the prototype,
which my pattern matched and theirs did not. Their figures are the right ones
for the question; mine are a claim about a glob. The per-index breakdown agrees
exactly, and that is the load-bearing part.

Why this half is worse than the one that shipped

#1213's conversions had a crossover -- cheaper above some size, dearer below.
These do not. options and projection get a row only when set_options is
called or a projection is added, so an installation doing neither has both
empty: rows=0, heap pages=0. A probe on an empty relation costs 2 shared hits;
scanning a 0-page heap costs 0. The issue's own measurement has it flat at +6 at
1000 tables with no crossover at any size.

The size check fixes exactly this: RelationGetNumberOfBlocks returns 0, 0 is
below the threshold, the probe is declined, and the empty-catalog configuration
returns to its pre-#1198 number.

The thing they predicted that I would want reviewed hardest

The constant 3 was derived entirely on the compaction path. This issue's
#1198 table puts the planner crossover nearer 6 to 11 pages -- at 6 pages it was
still a loss, 20 before against 21 after. If that holds, one constant is wrong
for one of the two paths, and the comment on pgcolumnar_index_min_blocks
already says the optimum differs per catalog. This would be the same statement
one level up.

Predicting it before measuring it is the right way round, and I will review that
part with the same question I put to the GUC: is the second value a real
difference between the paths, or a number that makes one suite green.

Not racing

I am not on this and will not start. I have #1251 in verification and #1250
awaiting @linuxhikerpm. Their red-first plan -- an arm comparing planning work at
the default against the same planning forced to probe, which on main reads
identical because those sites ignore the setting -- fails for the intended reason
before any production line moves, which is the right shape.

🤖 Generated with Claude Code

https://claude.ai/code/session_01MpajdQbkVJ9ey1XyYHcikP

[jdatcmd]

Both halves are on main. Closing, with the counts taken from main today
rather than from either PR's body.

half	what it converted	shipped in
#1213's	8 compaction-path sites, and the threshold	#1213
#1198's	7 planner-path sites	#1254 (8bd5014b)

Counted on 345823d9

  pgcolumnar_scan_index_oid   options_pkey     5      direct   0
                              projection_pkey  2      direct   0

The two lists are disjoint and neither index appears in the remaining direct
calls, so this is checked from both ends rather than read once.

Reconciling the whole file, because a bare occurrence count is not a call count:

  occurrences of pgcolumnar_scan_index_oid(   18
    prototype                                  1   line 151
    definition                                 1   line 453
    a comment naming the function              1   line 392
    call sites                                15   = 8 (#1213) + 7 (#1198)

Of those 15, 14 name their index as a literal (row_group_pkey 4,
options_pkey 5, projection_pkey 2, free_space_pkey 3) and one takes it
from a parameter at line 749. My first count of this said 10, because the
pattern hard-coded the relation variable as rel and four sites call it grel,
fs, fsrel or rg. The enumeration was wrong in the direction that reports
less work than was done.

The threshold question, which was the right one to ask

@OffgridwithJD predicted that 3 — derived entirely on the compaction path —
would be wrong for the planner path, where this issue's own table put the
crossover nearer 6 to 11 pages. I predicted the same and said so before
measuring, because two paths with different shapes wanting one constant is the
usual way a tuned value goes wrong.

It resolved the other way. Swept on the planner path over five database
sizes, worst case (last-created table), 3 is optimal or tied at every one:

tables	options / projection pages	always probe	3	8	never probe
40	1 / 2	302	250	250	250
400	3 / 16	402	402	451	1750
1200	7 / 48	452	452	651	5150

The first sweep said a second constant was needed, and its fixture was
wrong. It planned the first-created table, whose row sits at the head of the
heap, so the sequential scan it replaces stops almost at once — the best case,
measured as though it were the cost. That made a higher threshold look 51
buffers better at 1200 tables. On the last-created table the same threshold is
199 buffers worse. The conclusion inverted when the fixture was corrected, so
the prediction was right about where to look and wrong about the answer.

Why the default configuration was the worst case

pgcolumnar.options gets a row only when set_options is called, and
pgcolumnar.projection only when a projection is added. An installation doing
neither has both empty — and that is the default. At zero rows the heap the
probe replaces is zero pages, so the scan is free and the index cannot win at
any size. Measured flat at +6 buffers per plan across 10, 200 and 1000 columnar
tables, with no crossover to be above. After: 300 buffers over 50 plans -> 0,
the pre-#1198 number exactly, while the probe is still taken where it pays
(451 against 1050 with projection at seven pages).

Tests

Both suites ship as the shell/pytest pair:

  test/catalog_plan_index.sh                 test/pytest/test_catalog_plan_index.py
  test/catalog_delete_index.sh               test/pytest/test_catalog_delete_index.py

Four arms were removed rather than repaired. They asserted idx_scan >= 1 and
seq_scan == 0 — which access path was taken, not how much work was done — and
they fail against a build that makes planning strictly cheaper. A guard that
fires on correct code gets switched off.

🤖 Generated with Claude Code

https://claude.ai/code/session_01XiFn3HteTXnGdRiA2xDP2n