pgcolumnar_index_oid() costs a catcache pair on every call, on two hot paths

[jdatcmd]

pgcolumnar_index_oid() does get_namespace_oid() plus get_relname_relid()
on every call, and it is now called on two hot paths. It is the single cause
behind a measured planning regression on main (#1198) and a measured maintenance
cost in #1213, and neither of us spotted it in review because we both checked
that scan counts moved, not what they cost.

The cost is already profiled, in this file

The #445 comment above the metadata flush session in columnar_metadata.c says
it outright:

Each open ran get_namespace_oid plus get_relname_relid (catcache probes)
and table_open (a relcache lookup, a lock, a resource-owner remember), and
the matching close undid it. A profile of the numeric write path (#445) put
that per-row open and close cycle, not the encoding, at the top of the
profile.

That path solved it with a caching session. The planner and the maintenance path
did not get the same treatment, and both have since grown callers.

#1198, on main: linear becomes constant, at a price

Measured by @OffgridwithJD. PG18, 8288510d vs 8ea98fc8, planning
shared hit + read from the second EXPLAIN in a fresh backend per reading,
probe table created last:

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

after is flat at 21 from 500 upward; before grows with pages. So #1198 is a
real optimisation above ~1200 tables. It should not be reverted.

But there is a configuration where it never wins

pgcolumnar.options gets a row only when set_options() is called. For an
installation that never calls it, both catalogs are empty, the two sequential
scans #1198 removes cost nothing, and the seven lookups replacing them cost six
buffers — at every size, with no crossover to reach:

1000 columnar tables, no set_options, no projections
  before   options_rows=0  projection_rows=0   19 hits
  after    options_rows=0  projection_rows=0   25 hits   +6

That is the default configuration, and it is the case that makes this an
issue rather than a trade-off note.

#1213: same cause, different scaling

Measured here. EXPLAIN (ANALYZE, BUFFERS) over compact(), 20 groups with 10
retired, varying the number of other columnar tables sharing the catalogs:

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

Crossover near 120 tables. The +140 is 140 lookups at about one buffer each
— seven per retired group across twenty groups.

Why the two need different remedies

• fix: probe options and projection catalogs through their primary keys #1198 calls it seven times per PLAN. A cached lookup fixes it completely.
• fix: choose the catalog access path by size, and assert the work (#1207) #1213 calls it five times per RETIRED GROUP, plus two more. Caching only
  bounds that; the lookup wants to come out of the loop entirely.

So this should not be one bundled fix, which is what I was about to propose
before @OffgridwithJD separated the scalings.

Two measurements, not one number

@OffgridwithJD measured +5 at 10 tables on planning buffers with a fresh backend
per reading. I measured +16 and +37 on a different fixture and a different query.
Same sign, different quantity. Neither confirms the other and averaging them
would be wrong. Quoting a single "cost of an index_oid lookup" from this issue is
a misreading of it.

How this 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 — the house rule is 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.

Proposed order

1. This issue holds the cause and both tables.
2. Plan the hoist here, with the Citus columnar loads the same data 3x faster than we do, which contradicts why #300 was closed #445 comment as its design note.
3. Re-measure fix: choose the catalog access path by size, and assert the work (#1207) #1213 on top of it rather than against today's baseline.
4. fix: probe options and projection catalogs through their primary keys #1198 stays on main meanwhile.

🤖 Generated with Claude Code

https://claude.ai/code/session_01XiFn3HteTXnGdRiA2xDP2n

[jdatcmd]

Duplicate of #1217 — filed twelve seconds apart, same cause, same two tables. Closing this one and keeping #1217, which is the better issue: it carries the open question mine stated as a conclusion.

I wrote that #1198 stays on main because reverting it "would cost the installations it helps to spare the ones it does not". @OffgridwithJD is right that this smuggles in an unmeasured premise — it assumes the population helped is worth more than the population hurt, when what we actually know is that it helps above ~1200 columnar tables with set_options called and costs at every size without, and the configuration it hurts is the default one. How many real installations sit on either side is not something either of us has measured. That is the owner's judgement with both numbers in front of them, not a sentence I get to write in passing.

Everything else here is in #1217, with my compact() numbers kept in their own table and their own units so nobody averages a planning-buffer delta with a maintenance one.