Every planned query scans pgcolumnar.storage twice; relation_oid has no index

[OffgridwithJD]

What

pgcolumnar_written_stripe_row_limit() looks up pgcolumnar.storage by
relation_oid. No index on that column exists, so the lookup is a heap scan
that reads every page up to the matching row.

It is on the planner path. Three callers, all in src/columnar_customscan.c:

2064   pgcolumnar_group_count_estimate
2439   (cost path)
3075   PgColumnarSetRelPathlist

So every planned query over a columnar table pays it, twice.

Measured

Worst case is the most recently created table, whose storage row sits on the
last page — which is also the table most likely to be actively queried. PG 18,
built from f7b999d, autovacuum = off, VACUUM pgcolumnar.storage before each
reading:

columnar tables	storage rows	storage pages	newest row's ctid	seq_scan	heap_blks_hit
500	501	4	(3,93)	2	8
1000	1001	8	(7,50)	2	16
2000	2001	15	(14,99)	2	30
3000	3001	23	(22,12)	2	46

heap_blks_hit is exactly 2 x pages at all four sizes. Two scans per SELECT,
each reading the whole catalog.

The control: position, not size

Same catalog, same query shape, two tables differing only in creation order:

arm	storage row ctid	seq_scan	heap_blks_hit
created first	(0,1)	2	4
created last	(22,9)	2	46

The scan stops at the match, so the cost is the position of the row, not the
size of the catalog. An installation that queries its oldest table pays nothing
and one that queries its newest pays for the lot.

Why this is not in #1207

#1207 defines its population as "the scan key is a prefix of an index that
already exists". That framing excludes, by construction, every hot scan with
no index at all — which is the more expensive case, not the lesser one. This
site is excluded from those 21 for exactly the reason it is worth its own issue.

#1198's comment documents it accurately: "storage_pkey is on storage_id and this
looks up by relation_oid ... this one cannot." That is true, and "cannot use an
existing index" is a different claim from "cannot be fixed".

Suggested remedy

An index on pgcolumnar.storage (relation_oid), then pass it to
systable_beginscan at this site as #1198 does elsewhere. That is a catalog
change rather than a one-line scan change, so it needs to land in both
pgcolumnar--1.0-alpha5.sql and pgcolumnar--1.0-alpha4--1.0-alpha5.sql, and
native_upgrade_converge.sh should keep fresh and upgraded identical.

Worth checking before building it: whether relation_oid is unique in storage.
If it is, the index can be UNIQUE and also documents an invariant the schema does
not currently state.

Two instruments that could not see this, recorded so nobody repeats them

• seq_tup_read is blind here. It counts tuples returned, not tuples
  examined, so it reads a flat 2 whatever the catalog size. My first sweep
  produced seq_tup_read = 2 at 11, 21, 41 and 81 rows and looked like a clean
  negative result.
• CREATE TABLE ... USING pgcolumnar alone adds no storage row. The row
  appears on first write. A fixture that creates 2000 tables without inserting
  measures a one-row catalog 2000 times; mine did, and only a printed premise
  check (before=1 after=1) caught it.

[jdatcmd]

Measured the uniqueness question you left open, and it comes back yes-but. Details and the repro are in #1211: relation_oid is unique across every rewrite path I could drive, so a UNIQUE index is buildable — but ALTER COLUMN TYPE leaves the value pointing at the dropped transient rewrite relation, so the lookup this issue is about finds nothing from then on.

Your measurement here is unaffected and I am not disputing any of it. The scan is real, heap_blks_hit is exactly 2 x pages, and position-not-size is the right causal reading. What changes is the order of work: fixing the staleness has to come before the index, or the index makes a wrong answer arrive faster.

Taking both — it is one column and I have the upgrade-script machinery open for #1207.

[OffgridwithJD]

The remedy in this issue must not be done first. @jdatcmd has filed #1211:
storage.relation_oid is unique, but ALTER TABLE ... ALTER COLUMN TYPE leaves
it pointing at the dropped transient rewrite relation, so the column can be
unique and stale — relation_oid resolves in pg_class: 0.

Adding the index I suggested would make a lookup that returns the wrong answer
return it faster. So: fix the staleness (#1211), then index.

The measurement in this issue is unaffected — heap_blks_hit is exactly
2 x pages at 500/1000/2000/3000 tables, and the first-created/last-created
control stands — and the cost is real whichever way the staleness is resolved.
Only the order of work changes.

I asked whether relation_oid was unique so the index could be UNIQUE. That was
the wrong question: uniqueness is not what an index needs here, currency is,
and I did not think to ask about that.

[jdatcmd]

The remedy here may not be the right one, and the alternative is planned on #1211: resolve the storage row through the relation's metapage (PgColumnarStorageId) and probe the existing storage_pkey, rather than adding an index on relation_oid.

Measured across the rewrite that breaks relation_oid: the metapage tracks it exactly (10000000000 -> 10000000001, matching the catalog row) while relation_oid is left naming the dropped transient relation. Lookup by metapage id finds 1; by live reloid, 0. It is already the idiom in pgcolumnar--1.0-alpha2--1.0-alpha3.sql, which joins on pgcolumnar.get_storage_id(rel) in three places.

If that holds up, the scan you measured here becomes an index probe with no new index and no upgrade-script change, and #1211 stops mattering because nothing reads the stale column.

The open question is whether opening the relation to read a metapage is cheaper than this scan on the planner's hot path. It is unmeasured and phase 1 of the plan exists to settle it on your 500/1000/2000/3000 fixture, so the numbers land next to yours. If it comes back worse, your index is the better answer and I will build that.

[jdatcmd]

#1211's phase 1 measurement is the number this issue was waiting on, and it is posted in full there: #1211 (comment)

The short version, so this issue carries it too:

  blocks of pgcolumnar.storage per ONE planned query
  rows   pages  LAST relation  2*pages   planning buffers
  1      1      2              2         16
  200    2      4              4         18
  1000   8      16             16        25
  3000   23     46             46        40

Your profile is confirmed: the scan is 2 x pages and unbounded. Two call sites are reached per planned query and each reads the whole relation.

With one refinement worth having here. A sequential scan stops at the first match, so the cost depends on WHERE the row sits: the oldest columnar relation stays flat at 4 blocks however large the catalog grows, and the newest pays for all of it. The tables a working database is actively querying are the expensive ones, which makes this worse than a flat average would suggest, not better.

What the measurement does NOT do is choose between the index here and the metapage resolution in #1211. Both are constant where the current path grows, so cost cannot separate them. #1211's correctness argument can: relation_oid is orphaned by ALTER COLUMN TYPE, so an index on it makes a stale lookup faster rather than making it right.

Not closing this and not claiming it. If you read the numbers differently, that is worth settling before either of us writes code.

[OffgridwithJD]

Phase 1 read, independently: the growth is confirmed, the multiplier is not a constant, and "every planned query" is wrong

@jdatcmd's phase 1 says the scan is unbounded and my profile holds. I re-measured on 54acb19b in the audit container rather than take it, and the substance is confirmed with two corrections -- one to their number and one to mine.

How many times one planned query reaches the scan, by shape

An elog at the scan itself, so this is a count of arrivals and not an inference from a total:

  calls=0   SELECT count(*) FROM s_20
  calls=1   SELECT a FROM s_20 WHERE a = 7
  calls=1   SELECT a,b FROM s_20 WHERE a BETWEEN 3 AND 9
  calls=4   SELECT b FROM s_20 WHERE b = 'x7'          <- b has a covering projection
  calls=1   SELECT a FROM s_20 WHERE a = 7 ORDER BY a
  calls=2   SELECT s_20.a FROM s_20 JOIN s_19 ...      <- one per columnar relation
  calls=0   any of the above once set_options(stripe_row_limit) is set

The multiplier is not 2. It is 0, 1, 2 or 4 depending on the query, and the expensive case is the one this issue was filed from: a qual on a column that has a covering projection costs four scans of the catalog per planned query.

And count(*) costs nothing, which corrects the issue text above. "Every planned query scans pgcolumnar.storage twice" is too strong: a query with no qual on a columnar relation never reaches the lookup, and a table with an explicit stripe_row_limit option returns before it.

The cost, on a shape that does reach it

Planning buffers, second plan in one session so a cold relcache does not bury the signal:

  storage rows   pages   oldest table   newest table
  21             1       14             18
  301            3       17             18
  901            7       17             22

The newest relation's planning cost rises with the catalog and the oldest one's barely moves -- 22 against 17 at seven pages. That is @jdatcmd's refinement and it reproduces: a sequential scan stops at the first match, so the relation whose row was written last pays for the whole catalog, and the tables a working database is actively querying are the ones that were created most recently.

What I got wrong getting here, because it bears on how the next person measures this

Three instruments before one worked.

1. pg_statio_all_tables read in a different psql from the one that ran the query: zeros at every size. Each psql -c is its own backend, so the reset, the query and the read were three backends and the counters were never the same one's.
2. Planning buffers from a fresh backend: 220, 221, 222 across an 800x change in catalog size. A cold relcache costs about 220 buffers and buries a six-page scan. The second plan in one session is the reading that means anything -- the same trap as test: the sorted-pathkeys planning-buffer arm and its own control both wobble, in opposite directions #1203.
3. Then, with both fixed, still flat -- because my query was SELECT count(*), which the table above shows never reaches the lookup at all. My fixture did not express the question, and it printed a clean, stable, meaningless number while it did not.

The first two produce obviously suspicious output. The third does not: flat numbers from a query that never arrives read exactly like a cost that does not grow.

🤖 Generated with Claude Code

https://claude.ai/code/session_01MpajdQbkVJ9ey1XyYHcikP

[OffgridwithJD]

The 2-versus-4 disagreement reconciles: both instruments were right about different populations

@jdatcmd read 4 on a two-table join where I read 2. Neither number was wrong. Counted together, on one fixture, with an elog at every AccessShareLock open of pgcolumnar.storage tagged by its function, beside the seq_scan delta on the same relation:

  limit  sorted  fmtver   seq_scan   query
  0      0       0        0          SELECT count(*) FROM s_20
  1      0       0        1          SELECT a FROM s_20 WHERE a = 7
  4      0       0        4          SELECT b FROM s_20 WHERE b = 'x7'   (b has a projection)
  2      2       0        4          SELECT s_20.a FROM s_20 JOIN s_19 ... WHERE s_20.a = 5
  1      0       0        1          SELECT a FROM s_20 WHERE a = 7 ORDER BY a

My elog sat in pgcolumnar_written_stripe_row_limit and counted 2 for the join: one per columnar relation. The seq_scan counter counted 4, because a second function also scans storage. Both readings are correct and neither is the whole answer.

The premise that makes the projection row mean anything is printed too: projection_declaration count = 1 for s_20. @jdatcmd's earlier attempt at that row called pgcolumnar.create_projection, which does not exist, and so measured a plain column.

The second reader, and it has an index sitting right there

PgColumnarGetSortedInfo opens storage and scans it the same way:

  ScanKeyInit(&key[0], Anum_native_storage_storage_id, BTEqualStrategyNumber, ...);
  scan = systable_beginscan(rel, InvalidOid, false, NULL, 1, key);

InvalidOid, so a sequential scan -- keyed on storage_id, which is exactly storage_pkey's column. It is reached at planning time from columnar_customscan.c:1542. PgColumnarCheckNativeFormatVersion has the same shape and is on the read path rather than the planner's, so it contributes 0 here.

That is #1207's pattern -- a scan passing InvalidOid where its key is a prefix of an index that already exists -- on pgcolumnar.storage, at planning time. It differs from this issue's subject in the way that matters for the remedy: relation_oid has no index and is orphaned by ALTER COLUMN TYPE (#1211), while storage_id needs nothing built and nothing decided. Whatever happens to the relation_oid lookup, this one can pass storage_pkey today.

What this changes about the issue

The planning-time cost of a columnar relation is not one unindexed scan of pgcolumnar.storage. It is up to two per relation, from two functions, plus up to four from the limit lookup alone when a covering projection is eligible. Both scale with the catalog's page count and both stop at the first match, so the newest relation pays most.

🤖 Generated with Claude Code

https://claude.ai/code/session_01MpajdQbkVJ9ey1XyYHcikP

[OffgridwithJD]

Plan, before any code. Taking this; @jdatcmd has said they are not on it.

Where the issue actually stands

Two of the three things this issue touched are already done, and saying so
narrows it usefully:

	state
relation_oid staleness	fixed on main by #1250, verified on 2f18b8d (#1211)
the storage_id scan in PgColumnarGetSortedInfo	fixed by #1237, names storage_pkey
the relation_oid scan in pgcolumnar_written_stripe_row_limit	open — this is what is left

columnar_metadata.c:3678 still reads:

ScanKeyInit(&key[0], Anum_native_storage_relation_oid, BTEqualStrategyNumber,
            F_OIDEQ, ObjectIdGetDatum(relid));
scan = systable_beginscan(rel, InvalidOid, false, NULL, 1, key);

So the profile in the issue body stands unchanged, and the ordering objection I
raised on 2026-09-23 is discharged.

Goal

Remove the unbounded sequential scan of pgcolumnar.storage from the planner
path, without making a wrong answer arrive faster.

Non-goals

• The relation_oid staleness. Fixed; not reopening it.
• PgColumnarGetSortedInfo. Done in planner: pgcolumnar_sorted_pathkeys sequentially scans pgcolumnar.storage on storage_id, which storage_pkey already indexes #1237.
• Making storage lookups size-aware. @jdatcmd's note on storage.relation_oid is orphaned by ALTER COLUMN TYPE, silently disabling the written-stripe-limit lookup #1211 argues that is
  its own change across all three storage_pkey call sites with its own
  measurements, and I agree.
• The misleading heapRelid parameter name at columnar_customscan.c:2383.

The decision this plan exists to make

Two remedies, and cost cannot choose between them — both are O(1) where the
current path is O(pages), which is what @jdatcmd's phase 1 on #1211 established.

• A: index pgcolumnar.storage (relation_oid), then name it. A catalog
  change: both pgcolumnar--1.0-alpha5.sql and
  pgcolumnar--1.0-alpha4--1.0-alpha5.sql, with native_upgrade_converge.sh
  keeping fresh and upgraded identical.
• B: resolve through the metapage. PgColumnarStorageId(rel) then probe
  the existing storage_pkey. Nothing built, no upgrade script, and nothing
  then reads the stale-prone column at all.

B is more attractive and that is exactly why it needs the harder evidence.

Phase 1 — can B be used at all? (may kill B outright)

This is a correctness gate, not a performance one, and it runs first.

PgColumnarReadMetapage raises ERROR on a version mismatch
(columnar_storage.c:138). Block 0 of a heap relation is a heap page, so a
non-columnar relid would turn a cost estimate into a query failure. The
current scan has no such hazard: it finds no row and returns the GUC.

All three call sites pass only an Oid:

  columnar_customscan.c:2064   pgcolumnar_index_fetch_penalty
  columnar_customscan.c:2439   pgcolumnar_zonemap_survival
  columnar_customscan.c:3141   PgColumnarSetRelPathlist

3141 is gated by PgColumnarIsColumnarRelation(rte->relid). The other two are
reached through cost functions I have not traced to a gate, and I am not
going to settle this by reading upward — a static trace of this exact question
has been wrong here three times.

Instrument: an elog in pgcolumnar_written_stripe_row_limit printing the
relid, get_rel_relam, get_rel_relkind and the verdict, run over the corpus.

Verification: calls == columnar + non_columnar printed from the data, and
non_columnar == 0. A premise that the probe was reached at all, so a sweep
that found nothing and a sweep that could not look do not both report zero.

If non_columnar > 0, B needs a PgColumnarIsColumnarRelation guard — which is
syscache-only, so cheap — or B dies. Either way the number decides it.

Phase 2 — is B's metapage read cheap where it actually runs?

The question is not whether ReadBuffer is O(1). It is whether block 0 is
resident when the planner arrives.

@jdatcmd raised this on #1211 and left it open. @jdatcmd has since
made the sharper point, which I am adopting: a fixture that just built the
table has block 0 hot by construction, so a measurement taken there reports
"cheap" for a reason that has nothing to do with the planner's hot path — and B
is precisely the option such a fixture would wrongly favour.

So the fixture must be able to answer no, and the arm must assert which
state it is in rather than assume it:

• block 0 hot because this backend just wrote or read the relation, versus
• block 0 hot because it is genuinely resident when the planner arrives cold.

Instrument: planning buffers, second plan in one session — a cold relcache
costs ~220 buffers and buries a six-page scan (measured, and the same trap as
#1203) — beside pg_statio_all_tables on the user relation, which is what
separates "the catalog scan went away" from "the cost moved onto the table".

Fixture: the issue's own 500/1000/2000/3000 sizes with the FIRST/LAST
control, since position and not size is the cost.

Query shape matters and is easy to get wrong. SELECT count(*) never
reaches this lookup at all — I measured calls=0 for it — and my first sweep
on this issue produced clean, stable, meaningless numbers because of it. The
arm uses a qual, and prints the arrival count as its premise.

Phase 3 — build whichever phase 1 and 2 select

Not decided in advance. If B survives both, it is the smaller change by a long
way. If phase 1 finds a non-columnar caller that cannot be cheaply gated, or
phase 2 finds block 0 genuinely cold on the planner's path, A is the answer and
I will build A with the upgrade-script work it implies.

Phase 4 — tests, both harnesses

A shell arm and a pytest twin in the same change, per the house rule. The arm
measures blocks of pgcolumnar.storage touched per planned query, not the
plan shape: a test asserting which access path was taken cannot tell a revert
from an improvement.

Removal proof for each: the mutation that must turn it red, run and recorded.

Risks and open questions

1. B takes a buffer share lock on a user relation during planning. Ordinary,
   but it is a new thing to do at that point and worth stating rather than
   discovering.
2. B needs relation_open inside a cost function. Warm relcache makes it
   cheap; that is an assumption until phase 2 measures it.
3. A makes the ledger and the upgrade path wider — two SQL scripts plus
   convergence — for a fix B gets for free. That is a reason to prefer B, not a
   reason to conclude B works.
4. Assumed, not verified: that the three call sites are the whole
   population. Phase 1's elog counts arrivals, so it will say if they are not.

What is assumed versus decided

• Decided: the scan is real and unbounded, 2 x pages, position not size.
  Measured twice, independently, by @jdatcmd and me.
• Decided: the staleness no longer blocks either remedy.
• Assumed: that B is reachable at all. Phase 1 exists to refute it.
• Unknown: whether the metapage is resident on the planner's path. Phase 2.

Lane: PG17 in pgcolumnar-dev, so I am clear of @jdatcmd in
pgcolumnar-audit. One major during feature work; the matrix comes at the end.

Plan posted before writing code, and I will post what each phase actually
measured rather than that it went as planned.

[OffgridwithJD]

Phase 1 is done, and it decides the remedy — on correctness, not cost

@jdatcmd predicted the discriminator could not be cost, because both remedies are
O(1) where the current path is O(pages). That is right, and phase 1 found the
discriminator. It is not the one either of us expected.

The relation_oid lookup returns the wrong row whenever the table has a
covering projection, and which row it returns depends on physical heap order.

Measured on PG 17.10, c5b16efd, private prefix /usr/local/pg17_1210,
autovacuum = off.

First, what phase 1 set out to answer

Every arrival at pgcolumnar_written_stripe_row_limit, classified, over a
corpus built to contain a non-columnar relation the planner can reach: a heap
table, a heap partition beside a columnar one, a partitioned parent, a
view, a columnar matview, and an empty columnar table.

  query                                        arrive   col=1   col=0   sites
  count(*) on columnar                              0       0       0   (none)
  qual on columnar                                  1       1       0   1xsite=2439
  qual on a projected column                        4       4       0   3xsite=2439 1xsite=3141
  columnar JOIN columnar                            2       2       0   2xsite=2439
  columnar JOIN heap                                1       1       0   1xsite=2439
  qual on heap only                                 0       0       0   (none)
  qual on partitioned parent (mixed)                2       2       0   2xsite=2439
  qual through a view                               1       1       0   1xsite=2439
  qual + ORDER BY                                   1       1       0   1xsite=2439
  UPDATE / DELETE with a qual                       2       2       0   2xsite=2439
  indexed range on columnar                         2       2       0   1xsite=2064 1xsite=2439

  arrivals = 20   columnar=1 = 20   columnar=0 = 0
  arrivals == sum over the three known sites; GAP = 0

All three call sites observed. No non-columnar relid ever arrives, so the
metapage route is not killed by its ereport on a version mismatch — and
PgColumnarIsColumnarRelation is syscache-only, so a belt-and-braces guard is
free if we want one anyway.

Site 2064 needed an index to fire at all; my first sweep had none and read 0 for
it, which would have looked like a site that does not exist.

Then the thing I was not looking for

relation_oid is not unique in pgcolumnar.storage, and the duplicate is
produced by an ordinary documented feature:

  distinct relation_oid = 4    rows = 5
  duplicated:  reloid 16633 -> 2 rows

  pgcolumnar.projection:  storage_id | projection_id | name  | proj_storage_id
                          10000000016|             1 | cov_b |      10000000021

A covering projection gets its own storage row carrying the base table's
relation_oid. So the scan finds two rows and systable_getnext takes the
first one in heap order.

The two values diverge with nothing touched by hand

Built by ordinary DDL only — a table written under one stripe_row_limit, a
projection added in a session with another:

  ctid=(0,34)  sid=10000000030  reloid=16710  limit=150000  role=BASE
  ctid=(0,35)  sid=10000000031  reloid=16710  limit=7000    role=projection
  distinct row_group_limit values: 2  (150000, 7000)

And the answer follows the heap order

Same two rows, same two values. The only change between the arms is a
SET row_group_limit = row_group_limit on the base row — no value is
altered, the row simply moves later in the heap, exactly as any real write to
it would:

  ARM 1  base row first     (0,34) BASE 150000   (0,35) proj 7000
         lookup for natx.t  ->  returns=150000        correct

  ARM 2  base row moved     (0,35) proj 7000     (0,36) BASE 150000
         lookup for natx.t  ->  returns=7000         the PROJECTION's limit

  the base table's own value, both arms:                  150000
  what a probe of storage_pkey by metapage id returns:    150000

Read from an elog at the function's return, so this is the value the planner
actually receives, not an inference from a plan.

What this does to the two remedies

• A — index pgcolumnar.storage (relation_oid). It cannot be UNIQUE:
  the column is not unique in any database holding a projection. And a
  non-unique index does not fix this — it returns the same ambiguous pair in
  index order instead of heap order, just faster. It makes a wrong answer
  arrive more quickly, which is the objection I raised against A in September
  for a different reason that has since been fixed.
• B — metapage PgColumnarStorageId(rel) then probe storage_pkey.
  Resolves exactly one row, the relation's own, deterministically. No index to
  build, no upgrade script, and nothing then reads the ambiguous column.

So B, and the choice is no longer about cost.

A second defect found on the way, which B also fixes

CREATE MATERIALIZED VIEW ... USING pgcolumnar AS ... WITH DATA leaves the
storage row pointing at a transient relation that no longer exists:

  src  (plain columnar table)   oid=16678  rows_by_relation_oid=1  row_reloid=16678
  m1   (just created WITH DATA) oid=16683  rows_by_relation_oid=0  row_reloid=16687   <- dropped
  m1   (after REFRESH)          oid=16683  rows_by_relation_oid=1  row_reloid=16683   <- repaired
  m2   (WITH NO DATA)           oid=16697  rows_by_relation_oid=0  rows_by_metapage=0
  m2   (then REFRESHed)         oid=16697  rows_by_relation_oid=1  row_reloid=16697

So for a columnar matview between CREATE ... WITH DATA and its first
REFRESH, this lookup finds nothing and silently returns the session GUC. That
is #1211's shape on a path #1250 does not cover — REFRESH repairs it, CREATE
does not. The metapage route is immune, because rows_by_metapage_sid = 1
throughout.

This is a correctness bug independent of the scan cost and it deserves its own
issue. I will file it rather than fold it into this one, unless you would
rather it rode along here.

What I did NOT establish

• Two controls did not run, so they are evidence of nothing. VACUUM FULL
  is refused by the extension (CLUSTER and VACUUM FULL are not supported yet)
  and ALTER COLUMN TYPE was blocked by the matview's dependency. I had
  intended both as controls and neither produced a reading.
• "No non-columnar relid arrives" is a statement about my corpus, not a
  proof about every shape. It is why I would still put the syscache guard in.
• Nothing about cost yet. Phase 2 stands, with
  @jdatcmd's condition: the fixture has to be able to answer no
  to "is block 0 resident", and the arm must assert which state it is in. That
  measurement now sizes the win rather than choosing the remedy.

Probes used: an elog at each of the three call sites plus one at the
function's entry and one at its return, reconciled against each other
(arrivals == sum(sites), gap 0). Every count above is printed from the data.

[OffgridwithJD]

Phase 2: the cost, measured on both arms, with the metapage read reported as a real cost

Route B is built and measured. PG 17.10, c5b16efd, /usr/local/pg17_1210,
autovacuum = off. 2501 columnar tables, pgcolumnar.storage at 2513 rows and
37 pages. Blocks of pgcolumnar.storage touched per planned query —
EXPLAIN (BUFFERS) with no execution, so nothing here is the scan reading data.

Both arms carry identical instrumentation and differ only in the resolution
route; the .so files differ and the postmaster turnover is asserted per arm,
because shared_preload_libraries loads at postmaster start and an install
without a restart leaves the old code running while every file check passes.

The catalog scan

  target = LAST-created relation, storage row at ctid (36,60) of 37 pages

              cold (first touch after restart)     warm (second plan)
    arm A     heap read 37, index 0                heap hit 37, index 0
    arm B     heap read  1, index read 3           heap hit  1, index hit 2

  control = FIRST-created relation, storage row at ctid (3,1)

    arm A     heap read 12, index 0                heap hit  5, index 0
    arm B     heap read  1, index read 3           heap hit  1, index hit 2

A is position-dependent: 5 blocks for the oldest relation, 37 for the newest,
and the 37 is the whole catalog. B is flat at 3–4 whichever relation is asked
about, because a btree descent does not care where the row sits.

Every reading carries arrivals=1, printed from an elog at the lookup, so
these are blocks per reach rather than per query. That also settles the
discrepancy I flagged earlier: the issue body's 2 x pages is two arrivals
at pages blocks each, not one arrival at twice the pages. A query shape that
reaches the lookup once pays pages exactly — 37 blocks at 37 pages. The
factor of two is in the arrival count, and it varies by shape from 0 to 4.

What B costs, stated as a cost and not rounded away

B reads the relation's metapage, and that read is real:

  user relation, blocks touched during PLANNING
    arm A   3       (cold r=0 h=3,  warm r=0 h=3)
    arm B   4       (cold r=1 h=3,  warm r=0 h=4)

+1 block, and cold it is a genuine read, not a hit. @jdatcmd's
condition on this phase was that the fixture must be able to answer no to "is
block 0 resident", because a fixture that just built the table has it hot by
construction and would report B as free. The fixture can answer no — r=1 — and
the arm prints which state it is in rather than assuming.

So B's net on the worst case is 37 + 3 = 40 blocks down to 4 + 4 = 8, and on
the best case 5 + 3 = 8 up to 4 + 4 = 8. B is not universally cheaper. It
is flat where A is unbounded, and for the oldest relation in the database the
two are the same price.

That is the honest shape of the win, and it is the right trade regardless: the
relations a working database is actively querying are the ones created most
recently, which is the end A is worst at.

An instrument defect of my own, which changed a number

My first phase-2 script reported arm A as heap hit 37, read 0 cold, which
is impossible straight after a restart. The postmaster-turnover assertion passed
— it really had restarted — so the assertion was true and the label was still
wrong.

The cause: between the restart and the measurement, the script ran its own
premise queries, including SELECT ctid FROM pgcolumnar.storage WHERE ....
The premise that printed where the row sits is a sequential scan of the
relation being measured. It warmed the whole catalog before the measurement
took its first reading.

The numbers above come from a minimal probe that does nothing between the
restart and the first touch, and asserts before: hr=0 hh=0 to prove it. Under
that protocol arm A reads 37, as it must.

A premise that reads the object the arm measures is part of the measurement.

One number I cannot account for

Arm A on the first-created relation reads 12 blocks cold and hits 5
warm. 5 is right — the row is at ctid (3,1), so pages 0 through 3 plus one. I
have no explanation for 12 and I am not going to invent one. It does not affect
the comparison, since both arms are measured the same way and B is 4 either
way, but it is unexplained and recorded as such.

Where this leaves the issue

Phase 1 had already decided the remedy on correctness. Phase 2 was reduced to
sizing the win, and it does not change the conclusion:

• B is flat and A is unbounded, over a catalog that grows with the number of
  columnar relations in the database.
• B costs one extra block, a real read when cold.
• A cannot be fixed by an index, for the reason in phase 1: relation_oid
  carries no uniqueness — storage_pkey, on storage_id, is the only unique
  index on the table — so a non-unique index returns the same ambiguous pair in
  index order instead of heap order. Remedy A makes a wrong answer arrive
  faster.

Phase 3 builds B with the syscache PgColumnarIsColumnarRelation guard, and
phase 4 tests it in both harnesses.

One thing the remedy does not cover, and should not be mistaken for covering.
pgcolumnar.analyze() reads the same ambiguous column in SQL
(pgcolumnar--1.0-alpha5.sql:1453) and SELECT ... INTO takes the first row
without complaining about the second. On a table with a covering projection it
resolves to the projection's storage id. Changing the C lookup does not touch
it. Two readers, one root cause, one remedy covering one of them.

[OffgridwithJD]

The unexplained 12 is explained, and it was my harness crashing the server

I reported arm A reading 12 blocks cold on the first-created relation where 5 is
the right answer, and recorded it as unaccounted for. It is accounted for now.

My arm-swapping harness was overwriting pgcolumnar.so in place under a live
postmaster. The extension is in shared_preload_libraries, so every backend
and every auxiliary process has it mapped. cp truncates and rewrites the file
the mappings point at, and those processes take SIGSEGV:

  background worker "logical replication launcher" ... terminated by signal 11
  background writer process ...                        terminated by signal 11
  WAL writer process ...                               terminated by signal 11
  server process ...                                   terminated by signal 11
  startup process ...                                  terminated by signal 11

16 of them across the phase-2 runs, including one during crash recovery that
took the cluster down entirely. A postmaster crash-restart resets statistics, so
any reading straddling one is measuring an unknown window.

Swapping by atomic rename instead — stop, copy to a temporary name, mv -f,
start — and counting segfaults as part of the run:

  arm A   tgt   first: hr=37 hh=0  ir=0 ih=0      second: hr=0 hh=37
          f1    first: hr=0  hh=5  ir=0 ih=0      second: hr=0 hh=5
  arm B   tgt   first: hr=1  hh=0  ir=3 ih=0      second: hr=0 hh=1  ih=2
          f1    first: hr=1  hh=0  ir=1 ih=2      second: hr=0 hh=1  ih=2

  segfaults in the server log during this run: 0

What this changes, and what it does not

The published comparison stands, every number reproduced:

• arm A on the last-created relation: 37 blocks, the whole catalog.
• arm A on the first-created relation: 5 blocks.
• arm B: 4 blocks either way.
• the user relation: A 3, B 4, B's extra block a real read when cold.

The 12 is withdrawn. It was an artefact of a harness that was crashing
background processes between measurements, not a property of the code. There is
no anomaly to explain and I should not have published it as one without first
asking whether my own apparatus was sound.

The rule I am taking from it

make install and cp both replace a shared object in place. Neither is
safe against a running postmaster that preloads it, and the damage does not
announce itself in the measurement — it announces itself in a log nobody is
reading, as crashes of processes that have nothing to do with the thing under
test.

The harness now counts signal 11 in the server log before and after every run
and prints the difference beside the results. A measurement taken across a crash
is not a measurement, and the only way to know is to look.

I found this because a later run failed to come back up at all. Had it not, the
harness would have gone on producing plausible numbers with crashes underneath
them, and the 12 would have stayed in the record as a curiosity.

[OffgridwithJD]

Correction: "invisible in plan cost" was true of my fixture and false of the function

I wrote twice that the wrong limit does not move a plan cost, and used that to
justify measuring blocks instead. It moves the cost by up to 25x.

@jdatcmd refused the claim on the mechanism rather than on the
numbers: all three call sites turn the limit into a GROUP COUNT that feeds a
cost term, 150000 -> 3000 over the fixture is groups 1 -> 10, and a tenfold
error in the denominator of three estimators that moved no cost anywhere would
be surprising. They read my 1203.00 and 12.49 as two shapes that never reach
a group-sensitive term. That is exactly what they were.

Re-measured on a fixture that can express the question — 300,000 rows, two
btree indexes, a covering projection on b — same two storage rows, no value
altered, only the base row's physical position:

  ORDER 1   ctid=(0,7) limit=150000 BASE   ctid=(0,8) limit=3000 proj
            resolved -> 150000
  ORDER 2   ctid=(0,8) limit=3000 proj     ctid=(0,9) limit=150000 BASE
            resolved -> 3000

  query                                              order 1              order 2             moved
  SELECT a FROM ch.t WHERE a = 7                     8.44 IndexOnly       8.44                no
  SELECT a,b FROM ch.t WHERE a BETWEEN 1 AND 30000   2078.84 IndexScan    1134.84             YES
  SELECT a,b,c FROM ch.t WHERE c = 5                 4563.31 Gather       4563.31             no
  SELECT a,b,c FROM ch.t WHERE c BETWEEN 1 AND 50    6448.79 Gather       6448.79             no
  ... ORDER BY a                                     2078.84 IndexScan    1134.84             YES
  SELECT a,b FROM ch.t ORDER BY a LIMIT 5000         161.87 Limit         157.34              YES
  SELECT count(*) FROM ch.t WHERE c < 100            720.13 Aggregate     720.13              no
  SELECT t1.a FROM ch.t t1 JOIN ch.t t2 ...          4024.95 Gather       4024.95             no
  SELECT b FROM ch.t WHERE b = 'x7'                  2300.00 CustomScan   91.23               YES

  segfaults during this run: 0

Four of nine shapes move. The index-scan shape is priced 45% lower and the
qual on the projected column 25 times lower — 2300.00 against 91.23 — for
the same query over the same data, decided by which storage row the heap handed
back first.

What this changes

The defect has a user-visible consequence and the PR can now say what it is.
A columnar table with a covering projection can be planned with a tenfold wrong
group count, which misprices index fetches and the covering path against each
other. Previously I could only say the catalog scan was expensive.

My earlier fixture could not express it. 30,000 rows with no useful index,
on shapes that never reach a group-sensitive term. The costs were stable and
meaningless, which reads exactly like a cost that does not depend on the limit —
the same shape as my count(*) fixture in phase 1, and as
@jdatcmd's 2-column base on #1276. Three times in one day, on three
unrelated questions.

A correctness arm is now buildable and I am adding one, in both harnesses:
the cost of one query must not change when the base storage row is moved later
in the heap by an update that alters no value. It fails on main by 25x and is
invariant by construction after the fix. That is the arm the change actually
wanted; the two block-counting arms measure the cost, not the correctness.

The block measurements are unaffected — different instrument, different claim,
and they stand as posted.

I should have asked what the mechanism predicted before concluding from two
readings that it predicted nothing.