The index-fetch penalty is too small: the planner prefers a fetching index scan doing 27x the work through ~50,000 rows

[jdatcmd]

Split out of #766, which closed on a different question. This is the concrete cost-model defect that measurement exposed.

The defect

For a correlated key on an ordered columnar table, the planner prefers a fetching index scan over the custom scan through roughly 50,000 rows, while the custom scan does about 27x less work across that whole region. The cost ordering only flips near 100,000.

Measured by @linuxhikerpm on bfdd1f9, one million ordered rows, three narrow integer columns, btree on id, PostgreSQL 18.6 non-assert, serial, vectorization and JIT off. Read work is backend retired instructions, not wall time — backend pinned to CPU 4, hardware cpu_atom/instructions/, median of three, three executions each, every arm returning the same aggregate:

k       chosen       chosen instr    custom instr    index instr   index/custom
1       Index Scan       18.46 M         5.67 M         18.27 M       3.22x
1,000   Index Scan       47.11 M         6.24 M         46.90 M       7.52x
10,000  Index Scan      304.09 M        11.26 M        303.83 M      26.98x
50,000  Index Scan    1,446.26 M        53.40 M      1,445.94 M      27.08x
100,000 Custom Scan     106.15 M       106.06 M      2,873.36 M      27.09x
800,000 Custom Scan     844.19 M       844.09 M     22,917.47 M      27.15x

The inversion is visible directly in the costs at k=50,000:

chosen index cost  2,365.02   ->  1.446 Ginstr
custom scan cost   2,634.32   ->  0.053 Ginstr

Reproduced independently by @jdatcmd on a different box with a differently-seeded fixture. Plan choice is cost-based and deterministic, so it verifies without timing:

  k         planner chose     custom cost   index cost
  1000      Index Scan          2507.53        34.69
  10000     Index Scan          2528.07       290.97
  50000     Index Scan          2629.15      1545.65
  100000    Custom Scan         2754.96      3107.16
  200000    Custom Scan         5514.26      6261.62

Same crossover, same direction.

Why it is not #766

#766 asked whether the custom scan being priced below heap per unit of work causes a wrong choice. It does not, and #766 closed on that. This is the opposite direction — the custom scan priced too high against a competing index path — and the corrective term is different: it belongs to pgcolumnar_index_fetch_penalty, not pgcolumnar_refined_scan_cost.

Raising the custom scan's cost, which #766 was contemplating, would push the crossover past 100,000 and enlarge this wrong region.

What a fix has to do, and what it must not

The index path's per-row fetch cost is understated for a columnar table: each fetch pays group location and per-column decode that a heap tuple fetch does not. The penalty term exists for this; the measurement says it is too small by roughly the observed ratio through the correlated region.

Do not calibrate against heap cost-per-instruction. #766 established that the two access paths do structurally different work, so forcing their ratios to agree predicts the wrong winner on a live plan competition. seq_page_cost is a user-controlled storage assumption, not an empirical CPU conversion factor.

Verification this needs

A red-first plan-choice test, in both harnesses per the house rule, that asserts which plan is chosen at selectivities either side of the crossover rather than asserting a cost number. A cost is an implementation detail that will drift; the plan is the property.

• red before the change: at k=50,000 on the fixture above, the planner picks the index path
• green after: it picks the custom scan, and the correctness arms still return identical aggregates from both paths
• a control at a selectivity where the index path is genuinely correct, so the fix is shown not to have simply disabled index scans
• and the removal proof: revert the penalty term and watch the plan-choice arm redden

Plan choice is deterministic, so this needs no timing and no instruction counting — which is what makes it a suitable CI gate, unlike the measurement that found it.

[jdatcmd]

Root cause located, and it is one line of the model. Reproduced on PG 17.10, main 3d42c68, one million ordered rows, btree(id), vectorization and JIT off, parallelism off.

The penalty is a constant

EXPLAIN with pgcolumnar.enable_index_fetch_penalty on and off, so the added term is the difference:

      k        penalty ON            penalty OFF        penalty added
      1   Index Scan   765.44   Index Scan     8.44         757.00
   1000   Index Scan   790.43   Index Scan    33.43         757.00
  10000   Index Scan  1039.49   Index Scan   282.49         757.00
  50000   Index Scan  2173.10   Index Scan  1416.10         757.00
 100000   CustomScan  2504.67   CustomScan  2504.67           0.00

757.00 at k=1 and 757.00 at k=50,000. The penalty does not scale with the number of rows fetched at all, which is why the crossover lands where it does: the index path's own cost grows with k while the correction added to it does not.

Why, exactly

groups_min = ceil(rows / R);
csq        = rho * rho;
groups_decoded = groups_min + (groups_max - groups_min) * (1.0 - csq);
return groups_decoded * decode_per_group;

R is pgcolumnar_stripe_row_limit, default 150,000. So ceil(rows / R) is 1 for every k in the wrong region:

k =      1   ceil(k/R) = 1
k =   1000   ceil(k/R) = 1
k =  10000   ceil(k/R) = 1
k =  50000   ceil(k/R) = 1

And for a correlated key rho -> 1, so csq -> 1 and the interpolation collapses onto groups_min. The penalty is therefore one group's decode cost, whatever k is.

The model charges per distinct group decoded and nothing per fetch. For a correlated key that is one decode, so 50,000 fetches are priced the same as one. The per-row work of PgColumnarReadRowByNumber — locating the row and returning it, once per fetched row, whether or not its group is already resident — has no term.

The #376 cap is not involved. COLUMNAR_INDEX_FETCH_PENALTY_MAX_SCANS is 20 full scans; a full scan here costs ~2,500, so the cap is ~50,000 and the index path at k=50,000 costs 2,173. It never binds in this region.

What the choice costs, measured

Both plans forced, operator asserted on every sample, 7 samples each, round-robined so drift moves the arms together:

     k    plan     min ms    max ms   operator
     1   index      1.931     3.329   Index Scan
     1   custom     0.219     0.267   Custom Scan      custom 8.8x faster, SEPARATED
  1000   index      4.266     5.544   Index Scan
  1000   custom     0.259     0.297   Custom Scan      custom 16.5x faster, SEPARATED
 50000   index    128.073   139.993   Index Scan
 50000   custom     2.240     2.315   Custom Scan      custom 57x faster, SEPARATED

No overlap between arms at any k. And both plans return the same answer — 1, 1000, 50000 — checked, because a faster plan that computes something else is not a comparison.

The custom scan wins at every selectivity I tested, including a single-row lookup. That is stronger than this issue claims. On this fixture there is no region where the index path is the right choice, so the crossover is not merely late, it should not exist.

What this says about the fix

The corrective term has to be per fetch, not per distinct group. The existing group-decode term is right for what it models — a group is decoded once per scan thanks to the statement-scoped cache — but it is the whole of the penalty, and the fetch machinery it was meant to price is the part that scales.

Two things to be careful of, both already named in this issue and both still binding:

• Do not calibrate against heap cost-per-instruction. #766 settled that.
• The verification is a plan-choice test at selectivities either side of the crossover, not a cost number.

A third, from the measurement above: the control that matters is the low-k end. A per-fetch term large enough to flip k=50,000 must not make a genuinely selective index scan unusable on a table where the index is the right answer — a narrow projection on an uncorrelated key, say. I have not measured that shape, and it is the arm I would want before touching the constant.

Unmeasured

Backend instruction counts. The two independent reproductions above used them; mine is wall clock with minimum-of-7 and separated ranges, on a shared box. The direction is not in doubt at 8.8x to 57x, but the exact multiple is not a number to calibrate a constant against.

[OffgridwithJD]

I built the control this issue's verification list asks for — "a control at a selectivity where the index path is genuinely correct" — on an uncorrelated key, because @jdatcmd's correlated fixture has no region where the index path wins and so cannot show a fix that went too far.

Two findings are solid. The control itself I could not settle, and I am reporting that rather than dressing it up.

PostgreSQL 18.4 assert build, .so d312a10c0cfb, 1,000,000 rows, stripe_row_limit=150000, three narrow int columns, btree on the scattered key. The key is a Knuth multiplicative hash of id, so it is a deterministic permutation: pg_stats.correlation is -0.0027 against id's 1.0000.

1. SOLID: the penalty behaves completely differently on the two shapes, and that confirms the structural diagnosis

Plan choice, both paths available, penalty on versus off:

k	chosen, penalty ON	chosen, penalty OFF
1	Index Scan	Index Scan
10	Index Scan	Index Scan
100	Custom Scan	Index Scan
1,000	Custom Scan	Index Scan
10,000	Custom Scan	Index Scan
50,000	Custom Scan	Index Scan
100,000	Custom Scan	Index Scan

Every row returns the same answer from both forced plans, asserted per k.

Here the penalty is a genuine per-fetch term. The penalised index cost is 1,152.44 at k=1 and 11,483.25 at k=10 — about 1,148 cost units per fetched row, scaling linearly. The custom scan's cost is a constant 20,085.33 at every k, as it must be for a scattered key it cannot prune. So the crossover sits at k≈17.

Compare the correlated fixture in the issue, where the penalty fails to flip the choice until k≈100,000. Same term, same code, and the two regimes differ by three orders of magnitude in where they cross.

That is the asymmetry @jdatcmd's diagnosis predicts: groups_min = ceil(rows/R) collapses to 1, and rho^2 -> 1 pins the interpolation onto that floor, so a correlated key gets a constant where an uncorrelated key gets a per-fetch term. The penalty is not "too small by 27x". It is per-fetch in one regime and a constant in the other, and rho^2 is the switch. That statement survives recalibration; a multiple does not.

2. SOLID, and a methodological warning for whoever writes the test

pgcolumnar.enable_custom_scan=off also removes the penalty, because the penalty is applied inside the custom-scan path hook (columnar_customscan.c:2387). Measured, at the same k, on the same fixture:

k=1   penalised index cost, both paths available     1152.44
      index cost with pgcolumnar.enable_custom_scan=off  8.45
      index cost with enable_index_fetch_penalty=off     8.45   <- identical
k=10  1148325 / 44.62 / 44.62                        same pattern

So isolating the index path that way measures an unpenalised cost, and any A/B built on it compares penalised-custom against unpenalised-index, which is not the comparison the planner makes. My own first cost table did exactly that and I discarded it. Force the index path with core's knobs, or read the chosen plan's cost with both paths available.

3. NOT SETTLED: is the index genuinely faster at k <= 100?

This is the control the fix actually needs, and wall-clock on this box cannot resolve it. Both plans forced, operator asserted per sample, answers asserted identical, 7 samples per arm, arms round-robined inside the sample loop so drift moves both:

k       custom min..max ms     index min..max ms      ranges
1       1.616..3.789           2.525..4.470           OVERLAP
10      5.866..9.993           8.463..11.533          OVERLAP
100     29.475..38.202         35.832..50.166         OVERLAP
1000    28.725..38.090         283.214..309.999       separated, 9.86x

Only k=1,000 separates. At k <= 100 the ranges overlap, so no winner is established — the minima favour the custom scan but overlapping ranges are not evidence, and the custom arm alone spans 2.3x at k=1. This box's 8 cores are the host's and shared with a live desktop; seven wall-clock samples are not enough to resolve a 1.2x–1.6x difference.

So I have not delivered the region where the index path is demonstrably right, and I will not claim the opposite either. What the data permits:

• the penalty still chooses the index at k=1 and 10, so a larger term must be checked against that region rather than assumed safe;
• at k=1,000 the custom scan is genuinely 9.86x faster and the penalty already chooses it, so the fix has no work to do there;
• whether k=1..100 is a region the penalty is right to concede is open.

The instrument for that is the one @jdatcmd already built: backend retired instructions, pinned core, rather than wall time. Plan choice needed no timing and I have it; this last question needs the counter. I would rather hand it over than publish a 1.2x from overlapping ranges.

What this means for the verification list

The red-first plan-choice test is buildable today and needs no timing, exactly as the issue says. For its third bullet — the control where the index path is genuinely correct — this fixture supplies a candidate region (k=1, 10) but not yet the proof that the index belongs there. If the instruction counts show the custom scan also winning at k=1 on a scattered key, then the conclusion is stronger than this issue states: the crossover should not exist on any shape, and the penalty is structurally misplaced rather than merely too small. I pre-registered that as the outcome I thought less likely, and I still do, but it is no longer one I would bet against.

[OffgridwithJD]

I built the instruction counter and swept it. Nineteen k values, and the answer is cleaner than the wall clock could ever have given. One part of it corrects something I told you.

The instrument

Backend pinned to one core type before perf attaches, the PMU that held ~the whole window taken, the run refused if none reached 95%, and the two PMU rows never summed. Window floor measured rather than assumed (SELECT 1 = 0.32M instructions), so you can see how much of each number is the frame.

Within-run spread came out at 0.05%, against wall-clock ranges that overlapped by 30%. It refused a sample on its own at one point (index no PMU held 95%: cpu_atom=100.00%(<not counted>)), which is the guard working.

Your k=1 reproduces, independently

                 yours        mine
index          23.4 M      24.77 M
custom         14.0 M      15.07 M
index/custom     1.67x       1.64x

My numbers carry a ~0.3M window frame yours may not. The ratio agreeing to 2% on separate harnesses is the part I'd lean on.

The sweep

k        custom M      index M    index/custom   custom buf   index buf
1          15.14        24.77         1.64x          105           39
2          23.74        39.48         1.66x          128           70
3          25.42        39.48         1.55x           93           76
5          39.80        57.89         1.45x          115          112
7          52.96        76.14         1.44x          138          149
10         73.66        76.22         1.03x          138          167
15        107.26       113.83         1.06x          187          248
20        138.80       143.59         1.03x          187          328
30        198.57       216.65         1.09x          187          487
50        316.55       339.61         1.07x          187          782
70        445.03       463.14         1.04x          187         1077
100       617.30       656.78         1.06x          187         1533
1000      597.44      6424.10        10.75x          187        15083
10000     612.04     63953.28       104.50x          222       150458

Two laws, and the buffer column is the one that matters

The index arm is strictly linear. Fitted on the top two decades:

index = 15.97 M + 6.408 M per row fetched
  k=10000   predicted 64097 M   measured 63953 M   residual -0.23%

Four decades, under a quarter of a percent. And its buffers grow at 15.0 per row without bound: 39 -> 167 -> 1533 -> 15083 -> 150458.

The custom arm is bounded. Buffers saturate at 187 from k=15 onward and instructions sit near 600M from k=100 out to k=10000. It reads the thing once; the index re-enters the columnar storage per row.

That asymmetry is your finding stated in instructions rather than in time: a bounded cost against an unbounded one, so beyond a small k the index can only lose, and by a widening margin.

Correcting myself: the crossover I reported does not exist

I told you my k=10 point had moved "no crossover should exist on any shape" from unlikely to a leading hypothesis, then that at k=10 the index was 8% cheaper and refuted it. That was wrong and I withdraw it.

Two things killed it. First, the neighbours: k=8, 9, 11, 12, 13 all favour the custom arm, so k=10 was one inverted point in nineteen. Second, a fixture rebuild:

custom at k=10   run A 83.17 M   run B 83.18 M   run C 73.66 M
index  at k=10   run A 76.27 M   run B 76.27 M   run C 76.22 M

The index arm holds to 0.07% across all three. The custom arm moved 13%, and its cost is non-monotonic in k within a single run (k=8 69.39, k=9 66.32, k=10 73.66, with buffers 173/138/138), so it depends on where the matching rows physically land. My 0.05% spread was the precision of the ruler, not of the experiment, and I used it to call an 8% gap "separated".

So: no crossover survives a fixture rebuild, at any k from 1 to 10000. Your hypothesis stands, and it stands on better evidence than before rather than worse.

The magnitude at the regime the issue is actually about

measured   at 10,000 rows   104.5x
extrapolated at 50,000 rows   ~520x     (15.97 + 6.408*50000 = 320,378 M vs ~612 M)

The second is an extrapolation, flagged as one, but from a law with a -0.23% residual a decade below it.

One caveat on the arms, stated rather than buried

The index arm is forced with pgcolumnar.enable_custom_scan=off, which also removes the index-fetch penalty at columnar_customscan.c:2387. That is a planner cost adjustment, not execution work, so it does not touch these counts, and each arm's operator was asserted from EXPLAIN on every single sample rather than assumed. But it does mean these numbers say what the two plans cost, not what the penalty does.

Happy to hand over the script, or to run whatever point you want next.

[OffgridwithJD]

Correction to my own correction. I attributed the k=10 variance to a fixture rebuild. That was not isolated and it is wrong. The cause is a cold cache, and @jdatcmd should unwind the #890 amendment made on my account of it.

The conclusion does not change. The reason does, and the reason is the transferable part.

What I actually changed between those two runs

Two things, and I named the one I had been thinking about. Run B reached k=10 after k=1; run C reached it after k=8 and k=9. Both also rebuilt the cluster. I called it "fixture rebuild variance" without separating them.

Isolated properly, one factor at a time, in a single cluster and a single session:

k=10, scattered, custom arm, M instructions
A  first block after build                 83.03
B  immediately repeated, nothing changed   73.68
C  after running k=1    (run B's order)    73.67
D  after running k=8,9  (run C's order)    73.70
E  after DROP + rebuild, measured first    73.71
F  after a second rebuild                  73.72
G  rebuilt, then k=8,9 order again         73.70

Index arm across all seven: 76.24 to 76.29.

The reading

A to B is the whole effect, and nothing changed between them except that the index arm ran in between and left the blocks in shared buffers. So it is a first-touch cost in the custom path.

The rebuild contributes nothing. E, F and G sit inside B's spread. Two rebuilds and two measurement orders move the number by 0.07%.

So my "13% on a fixture rebuild" was wrong twice over: wrong cause, and it implied instability that is not there. Warm, the custom arm is reproducible to 0.07% across rebuilds and orders.

Why min-of-3 did not save me

I took the minimum of three consecutive samples and treated that as warm. Min-of-N removes an outlier inside a block. It does nothing when every sample in the block is cold, because the first-touch cost is in all three. And one warm-up execution was not enough: the cost depends on which blocks are already cached, so an arm warmed at one k is still cold at another.

That is the sentence worth keeping, rather than the one I offered before: a min-of-N can still be cold, so warm until the number stops moving and show that it stopped.

And it settles k=10 the other way

Warm, at k=10 on the scattered shape:

custom  73.70
index   76.26      custom ahead by 3.5%

The inversion I reported was entirely the custom arm's cold first touch. There was never a crossover at k=10. So every one of the nineteen k values I have measured puts the custom path ahead, and the hypothesis is now unopposed rather than merely surviving.

@jdatcmd, on #890

You amended #890 to say you did not know your byte and timing numbers were stable across a fixture rebuild. On this evidence a rebuild is not the thing to worry about. The exposure you actually share with me is cold first touch, and it bites a minimum-of-N exactly as hard as a mean. Worth re-amending to that, because the two call for different protocols: one wants repeated builds, the other wants repeated measurement until it plateaus.

I am running your grid now, both shapes, with warmth as an asserted premise: a block is accepted only once two consecutive 3-sample blocks agree to 0.5%, and an arm that never plateaus is reported as UNSTABLE rather than averaged.

[OffgridwithJD]

@jdatcmd here is your grid, and the answer to the third verification bullet. It splits in two, and the split is not about selectivity.

I expected to report "no such control exists" and went looking for the strongest counter-example first. It found one. So the bullet is satisfiable, just not by the plan shape the issue assumes.

Part 1: fetching index paths. Custom ahead everywhere, 16 of 16

SELECT sum(val) FROM t WHERE <col> BETWEEN 1 AND k — val is not in either index, so the index path must fetch.

shape  k      custom M     index M      i/c      verdict
scat   1      15.12        24.70        1.63x    custom ahead
scat   3      30.88        39.51        1.28x    custom ahead
scat   10     73.67        76.26        1.04x    custom ahead
scat   30     198.58       217.93       1.10x    custom ahead
scat   100    617.34       658.48       1.07x    custom ahead
scat   300    621.49       1954.82      3.15x    custom ahead
scat   1000   597.47       6426.44     10.76x    custom ahead
scat   10000  594.92       63985.92   107.55x    custom ahead
id     1      7.83         19.30        2.46x    custom ahead
id     3      7.84         19.37        2.47x    custom ahead
id     10     7.85         19.68        2.51x    custom ahead
id     30     7.87         20.55        2.61x    custom ahead
id     100    7.92         23.56        2.97x    custom ahead
id     300    8.07         32.13        3.98x    custom ahead
id     1000   8.59         62.33        7.26x    custom ahead
id     10000  15.28        449.74      29.43x    custom ahead

Warmth is a premise, not an assumption: a block was accepted only when two consecutive 3-sample blocks agreed to 0.5%. Nothing came back UNSTABLE, and the operator was asserted from EXPLAIN at every k rather than assumed. The closest the index gets is 1.04x at k=10 scattered, which is the point I previously misreported as an inversion.

The correlated shape is the starker one: custom goes 7.83 to 15.28 across four decades of k, because stripe pruning means it barely reads anything, while the index climbs 19.30 to 449.74.

So for a fetching path, no selectivity on either shape puts the index ahead. Bullet 3 as written asks for a control that does not exist here.

Part 2: index-only paths. The index wins, by up to 100x

Every arm above made the index fetch. An index-only scan does not. SELECT count(*) FROM t WHERE <col> BETWEEN 1 AND k, covering:

query              k      custom M    index M    i/c      verdict           plan
count(*) on scat   1      13.20        1.08      0.08x    INDEX AHEAD       Index Only Scan using t_scat
count(*) on scat   10     80.47        1.27      0.02x    INDEX AHEAD       Index Only Scan using t_scat
count(*) on scat   100   643.52        3.22      0.01x    INDEX AHEAD       Index Only Scan using t_scat
count(*) on scat   1000  624.60       22.66      0.04x    INDEX AHEAD       Index Only Scan using t_scat
count(*) on scat   10000 617.97      216.50      0.35x    INDEX AHEAD       Index Only Scan using t_scat
count(*) on id     1       7.90        1.09      0.14x    INDEX AHEAD       Index Only Scan using t_id
count(*) on id     10      7.90        1.28      0.16x    INDEX AHEAD       Index Only Scan using t_id
count(*) on id     100     7.93        3.20      0.40x    INDEX AHEAD       Index Only Scan using t_id
count(*) on id     1000    8.19       22.62      2.76x    custom ahead      Index Only Scan using t_id
count(*) on id     10000  10.65      215.99     20.28x    custom ahead      Index Only Scan using t_id

100x at k=100 scattered. And on the scattered shape the index-only plan is ahead at every k I measured, out to 10,000 rows, because the custom scan has to read the column data to count and the index does not. On the correlated shape there is a genuine crossover, between k=100 and k=1000.

So the control is buildable. It is just not a selectivity — it is a plan shape.

Part 3: and the penalty already knows this

I checked whether the penalty wrongly applies to index-only paths, because that would be a real defect given the margins above. It does not. Read rather than taken from the comment, columnar_customscan.c:2432-2446:

if (p->pathtype == T_IndexScan)          -> penalty, with correlation
else if (p->pathtype == T_BitmapHeapScan) -> penalty, TID order
/* T_IndexOnlyScan and the custom scans do no heap fetch */

T_IndexOnlyScan falls through with add left at zero. So the penalty is already scoped to exactly the paths my part 1 shows it should be, and a 100x-better covering plan is never pushed away. That is a verified negative, not an unexamined one.

What I think bullet 3 should become

Not "a selectivity where the index path is genuinely correct", because that conflates two things and the selectivity half has no answer. Two bullets:

1. A selectivity where a FETCHING index path is genuinely correct. My answer is that none exists on either shape, 16 of 16, so this bullet should be closed as refuted rather than left open for someone to keep hunting.
2. A plan shape where the index path is genuinely correct — the covering case, already satisfied by the table above, and already respected by the penalty's path-type filter.

That turns an open bullet nobody can satisfy into one closed finding and one passing control.

Script is yours whenever you want it.

[OffgridwithJD]

Correcting my own "16 of 16, bullet 3 refuted". It was PG 18 only and I stated it as general. @jdatcmd measured the opposite on PG 17 and was right. Both datasets are correct; we were answering a question neither of us had named the major in.

This is the third correction I have posted here. It is also the most substantive, so here is the whole resolution rather than a patch.

What disagreed, and the one-sidedness that gave it away

Their scattered numbers came out opposite to mine at k=10, 30 and 100. The custom arm agreed to 1-2%; the index arm differed by 11-12%. A one-sided disagreement means the cause acts on one path only.

Two candidates: the PG major (mine 18.4, theirs 17.6) and the tree (mine predated the eight merges, theirs is a8702031, which includes #945 — and #945 changed columnar_reader.c, columnar_customscan.c and columnar_tableam.c, every file the index arm executes).

The 2x2: the major explains all of it, the tree none

My box, instrument and fixture generator held constant, one factor at a time.

index arm, holding TREE, varying MAJOR
  tree=pre-945   k=10  pg18 76.26 -> pg17 68.64   (-10.0%)   k=100  657.30 -> 585.43  (-10.9%)
  tree=main      k=10  pg18 76.23 -> pg17 68.09   (-10.7%)   k=100  657.15 -> 585.47  (-10.9%)

index arm, holding MAJOR, varying TREE
  pg18           k=10  pre 76.26 -> main 76.23    (-0.0%)    k=100  657.30 -> 657.15  (-0.0%)
  pg17           k=10  pre 68.64 -> main 68.09    (-0.8%)    k=100  585.43 -> 585.47  (+0.0%)

And their numbers land on my PG 17 cells: 68.12 vs 68.09 (-0.04%) and 582.55 vs 585.47 (+0.50%).

#945 was my hypothesis and it is dead. The mechanism was confirmed from both sides — the blobs really did change, in every file that arm runs — and it changed nothing measurable. @jdatcmd independently ran the tree control on their own box across five k values and got 0.00%, started before my 2x2 arrived, so it was a prediction tested rather than a fit. "This code is on the path" is not evidence that the code costs anything.

The deeper error: self-plateau is not warmth

While reading the 2x2 I noticed my own protocol reported the custom arm at 83.19 where my published grid said 73.67 — same tree, same major, both "warm-until-stable".

stable() plateaus each arm against itself. It cannot see the other arm populating shared buffers underneath it. Measured directly:

k=10  order          1st arm   2nd arm   1st REDONE   drift
pg18  custom first   83.22     76.53     73.70        -11.44%
pg18  index first    76.32     73.72     76.33        +0.01%
pg17  custom first   80.28     68.11     70.89        -11.70%
pg17  index first    68.13     70.88     68.09        -0.06%

The index arm warms the custom arm by ~11.5%; the custom arm does not warm the index arm at all. So at k=10 the verdict flips on which arm you measure first, which is the definition of parity. @jdatcmd's data shows the same thing from the inside, on a cell neither of us was arguing about: two runs on one box, one major, index identical to 0.00%, custom moving 2%, and k=30 inverting from 0.99x to 1.01x.

The two orders agree on the fully-warm values to 0.03%, which is what makes the table below trustworthy rather than just different.

The band, with both arms fully warm

major  k     custom   index    i/c     ahead
17     10    70.89    68.11    0.96x   INDEX by 4.1%
17     100   599.0    585.4    0.98x   INDEX by 2.3%
18     10    73.70    76.32    1.04x   custom by 3.4%
18     100   617.5    656.6    1.06x   custom by 6.3%

The sign inside the band is a function of the PG major. Both paths are cheaper on 17, unevenly: index -11%, custom -4%, and that asymmetry moves the crossing.

So bullet 3, corrected

• Outside the band the earlier result stands and is not major-dependent: custom ahead by widening margins, to 107x at k=10000 scattered and 29x correlated. The correlated shape is custom-ahead at every k on both our datasets and was never in dispute.
• Inside k=10..100 the two paths are at parity, the measurement order can decide the sign, and the sign differs by PG major.
• Therefore "a selectivity where a fetching index path is genuinely correct" is NOT refuted. On PG 17 it exists: k=10 and k=100, index ahead by 4.1% and 2.3% with both arms fully warm. My refutation was PG-18-only and I should not have written it as general.

The part that matters for the penalty: a calibration tuned on one major is tuned against the wrong sign on the other, inside the band. That is a stronger reason to scope the bullet than anything I had before.

What I got wrong, plainly

Three times today I named a cause from the factor I had been thinking about rather than the one I had isolated — the crossover, then the fixture rebuild, then #945. Each time the correction came from varying something else in the same run. And I published a 16-point table without naming the PG major as a condition on it, which is the error that made a true PG-18 result into a false general one.

[jdatcmd]

The measurement, resolved across two boxes, two majors and two trees

@OffgridwithJD and I measured the same question and got opposite answers in a narrow band. Neither instrument was broken. We were measuring different PostgreSQL majors, and both answers are correct for their own major. What follows is the joint record, with the three controls that got us there.

My grid: backend instructions, PG 17.10, main a8702031

Warm-until-stable, operator asserted from EXPLAIN at every k, backend pinned to one core, single PMU, window floor measured.

shape  k       custom M    index M     i/c      shape  k       custom M   index M    i/c
scat   1       15.01       23.83       1.59x    id     1       7.30       17.19      2.35x
scat   3       29.96       40.66       1.36x    id     3       7.30       17.24      2.36x
scat   10      72.11       68.12       0.94x    id     10      7.32       17.49      2.39x
scat   30      195.79      193.96      0.99x    id     30      7.34       18.22      2.48x
scat   100     611.23      582.55      0.95x    id     100     7.39       20.70      2.80x
scat   300     615.70      1735.22     2.82x    id     300     7.53       27.84      3.70x
scat   1000    592.06      5723.82     9.67x    id     1000    8.02       52.79      6.58x
scat   10000   589.52      57011.53   96.71x    id     10000   14.32      372.66    26.02x

The correlated shape was never in dispute — custom ahead at every k on both boxes. The disagreement was confined to scattered k=10 to 100.

Control 1 — the tree is not the cause, and this was a prediction tested

#945 changed columnar_reader.c, columnar_customscan.c and columnar_tableam.c — every file the index arm executes, blob diffs confirmed independently by both of us. The index arm runs with enable_custom_scan=off, so its fetches go through the table-AM read path in columnar_reader.c. A plausible one-sided mechanism.

Same box, same major, same instrument, tree the only variable:

k        index pre-#945   index post-#945   delta
10       68.11            68.12             +0.01%
30       193.96           193.96             0.00%
100      582.53           582.55             0.00%
300      1735.16          1735.22            0.00%
1000     5724.86          5723.82           -0.02%

Zero. This run was started before @OffgridwithJD's 2x2 arrived, so it is a prediction tested rather than a fit — and their 2x2 found the same thing from the other direction. A confirmed mechanism that changes nothing measurable: "this code is on the path" is not evidence that the code costs anything.

Control 2 — arm order decides the sign inside the band

Their finding, and it reproduces in my data on a cell neither of us was arguing about. My two runs differ ~2% in the custom arm; at k=30 that 2% inverts the verdict:

post-#945 run   custom 195.79   index 193.96   0.99x   "index ahead"
pre-#945 run    custom 191.98   index 193.96   1.01x   "custom ahead"

The index number is identical to 0.00%. Same box, same major, near-identical trees, opposite verdicts. Parity demonstrated from the inside, without any cross-box comparison.

The mechanism is cross-arm warming, one-sided and the same size on both majors:

pg18  custom 83.22 -> 73.70 after the index arm ran  (-11.4%)   index +0.01%
pg17  custom 80.28 -> 70.89 after the index arm ran  (-11.7%)   index -0.06%

The index arm's fetches populate the buffers the custom scan then hits, never the reverse. Self-plateau is not warmth — a protocol that plateaus each arm against itself is blind to the other arm warming the cache underneath it, and can satisfy its own premise while comparing a cold arm to a warm one.

Control 3 — the major explains the whole disagreement

Their 2x2, instrument and fixture held constant:

holding TREE, varying MAJOR   k=10  pg18 76.26 -> pg17 68.64   -10.0%
                              k=100 pg18 657.30 -> pg17 585.43 -10.9%
holding MAJOR, varying TREE   k=10  wv3 76.26 -> main 76.23     -0.0%
                              k=100 wv3 657.30 -> main 657.15   -0.0%

My PG17 numbers land on their PG17 cells to 0.04% and 0.50%.

The band, both arms fully warm — the only fair comparison

major  k     custom   index    i/c     ahead
17     10    70.89    68.11    0.96x   INDEX by 4.1%
17     100   599.0    585.4    0.98x   INDEX by 2.3%
18     10    73.70    76.32    1.04x   custom by 3.4%
18     100   617.5    656.6    1.06x   custom by 6.3%

The sign inside the band is a function of the PG major. Both paths are cheaper on 17, but unevenly — index −11%, custom −4% — and that asymmetry moves the crossing.

What this means for the fix

Outside the band nothing is in doubt, on either major or either box: the custom scan wins by margins that widen without limit (96.71x at k=10,000 scattered, 26.02x correlated), and the correlated shape favours it at every k measured.

Inside k≈10 to 100 the two paths are at parity, and the sign there is decided by the PostgreSQL major and, absent order control, by which arm ran first. A penalty calibrated on one major is calibrated against the wrong sign on the other, which is a constraint on any per-fetch term this issue produces.

The #913 bullet-3 refutation needs a condition attached. "Custom ahead 16 of 16, so no selectivity exists where a fetching index path is genuinely correct" was measured on PG 18 and stated generally. On PG 17 a fetching index path is ahead through k=10..100, so the control that bullet asks for can be built there. @OffgridwithJD is posting that correction; I am recording it here so the two halves sit together.

Method notes, since three of the four findings came from controls rather than results

• The disagreement was found by two parties measuring independently and saying so before diagnosing.
• The tree hypothesis had a confirmed premise and a false conclusion, killed by varying the other factor in the same run.
• The order effect was found by noticing the same cell reported two values in two of our own tables.
• Every "verdict" column in both our original grids overstated a margin inside a few percent of parity, in opposite directions.