Default stripe_row_limit exceeds the 32MB fetch cache, making indexed point queries ~186x slower

[jdatcmd]

Summary

The default stripe_row_limit of 150,000 puts a realistically wide table over the 32 MB fetch cache cap, so every index fetch re-decodes the entire row group. Indexed point queries cost ~33 ms per row at the default and ~0.18 ms per row one step below it: a 186x difference produced by nothing but the shipped default.

Against heap on the same data with the same index and the same plan, this is roughly 1,700x slower.

Reproduction

Self-contained, 300,000 rows, 21 columns (TSBS-cpu shape). Identical data and identical query in all three arms; only stripe_row_limit differs. All three take an Index Scan.

SELECT count(*), max(usage_user) FROM t WHERE hostname='host_1' -- 600 rows.

stripe_row_limit	groups	plan	time	per row
150000 (default)	2	Index Scan	19,788 ms	32.98 ms
100000	3	Index Scan	106.5 ms	0.177 ms
50000	6	Index Scan	75.4 ms	0.125 ms

Script: /root/ct3.sh on pgcolumnar-audit (runs in a throwaway database).

Mechanism

COLUMNAR_FETCH_CACHE_MAX_BYTES is 32 MB (src/columnar_reader.c:1778). A fetch by row number decodes the row group and caches it; when the decoded group exceeds that cap the entry cannot be retained, so the next fetch decodes the whole group again. With 150,000 rows and 21 columns the decoded group is comfortably over 32 MB, so the cache never survives a single fetch and every row costs a full group decode.

The threshold behaviour is sharp, and an independent measurement during an unrelated audit put the cliff at 145,001 rows for a narrower fixture -- consistent with a byte cap rather than a row cap.

Why this matters more than it looks

It is the default. stripe_row_limit defaults to 150,000, so a user who creates a columnar table, adds an index, and runs a selective query gets the pathological path with no indication that a configuration choice caused it.

It scales with table width, not row count. The wider the table, the fewer rows fit under 32 MB, so the more likely the default exceeds it. A 21-column time-series table -- the workload this project targets -- is past it.

It is not the closed quadratic-fetch issue. #143 and #157 both addressed fetch-by-row-number cost and are closed. This is the cache being unable to hold a default-sized group at all.

Measured impact on the 100M bench

Same shape at scale, cpu_pgc (100M rows, 12 columns queried, groups averaging 149,925 rows), against cpu_heap with an equivalent (hostname, time DESC) index and an identical plan:

query	rows returned	pgColumnar	heap	per row (pgc)
one host, 1 hour	360	10,335 ms	6 ms	28.7 ms
one host, 12 hours	4,320	123,546 ms	8 ms	28.6 ms

The per-row cost is flat across a 12x change in row count, which is the signature of a fixed per-fetch cost rather than anything proportional to the result.

Options, roughly in order of appeal

1. Cache the decoded group per column rather than whole, so the cap applies to what the fetch actually needs. A point query touching 3 of 21 columns would then fit comfortably.
2. Raise or make configurable COLUMNAR_FETCH_CACHE_MAX_BYTES. Simple, but trades a silent performance cliff for silent memory growth, and the right value still depends on table width.
3. Lower the default stripe_row_limit. Cheapest, but it is the wrong lever: it degrades scan and compression to fix a fetch-path problem, and the cliff would still be there for wide-enough tables.
4. Warn when a table's configuration puts it over the cap. Not a fix, but it converts an invisible cliff into a visible one.

(1) is the only option that removes the cliff rather than moving it.

Note on scope

Found while re-measuring the cross-engine benchmark for #348. It is the dominant term in q1/q2/q3 there, and those queries cannot be honestly published without it. Filing separately because it is a product defect rather than a documentation one.

[ChronicallyJD]

Taking this, per direction. Going with option 1 (per-column decode caching) — it's the one that removes the cliff instead of relocating it, and it targets exactly what a point query needs: a fetch touching 3 of 21 columns should cache 3 columns' worth, not a whole 21-column group that can't fit under the 32 MB cap.

Plan: cache decoded state per (group, column) with the byte cap applied per column rather than to the whole group, so the retained set is bounded by what fetches actually touch; keep the whole-group decode for the scan path unchanged. I'll design the cache-structure + eviction change first (the correctness-sensitive part is making a partially-cached group serve a fetch that later needs an un-cached column), reproduce your 186x on a throwaway fixture, confirm the fix drops per-row cost to the sub-ms range, and gate on assert 18/19 + all five majors + pg18_san (decode-path change). Re-measure at 100M is yours since it's your bench.

[ChronicallyJD]

Measured: option 1 (per-column caching) alone does not remove the cliff — the deferred decode reads a prefix, not the touched columns

I implemented option 1 (cache decoded state per column instead of the whole group; keep the whole-group buffer only for allColumns fetches). It's correct — passes native_fetch_cache/position/projection, native_index, native_ios, native_dml, alter_column_type, native_lazy_slot on assert — and it does reduce the cached footprint from whole-group to the fetched columns. But it does not clear the cliff on the headline repro, and the reason is worth recording before building further on option 1.

Instrumented columnar_fetch_row on your exact shape (21-col table, default stripe, SELECT count(*), max(usage_user) FROM t WHERE hostname='host_1'):

allcol=0  hit=0  over=1   cx=33,860,752   (every fetch)

allcol=0 confirms the per-column path is taken, but the entry is 33.8 MB — just over the 32 MB cap — so it's reset on every fetch (hit=0), exactly the cliff.

Why 33.8 MB for a query that touches 2 columns: the index-scan fetch is deferred (#157) and materialised by columnar_slot_decode_upto, which decodes the prefix {0 .. max attno accessed} because that's slot_getsomeattrs' contract. max(usage_user) is attribute 13, so the fetch decodes 13 columns, not the 2 the query reads. Thirteen columns' decoded+raw at 150k rows is 33.8 MB. So per-column caching shrinks whole-group→prefix, but a 13-wide prefix at the default stripe still exceeds the cap.

Ruled out: reserved-vs-used block inflation — rebuilding entry->cx with ALLOCSET_SMALL_SIZES gave the same 33.86 MB, so it's genuine data, not aset overhead.

Corrected direction

The root is the prefix width, so the fix that actually removes the cliff is to decode only the query's projected columns, not the {0..maxattno} prefix — intersect the deferred slot's needed with the scan's projected set (the colWanted plumbing from #338/#339), materialising the rest as null (they're never read). On this repro that's 2 columns, ~4 MB, comfortably under the cap. That touches the #157 deferred-slot / #339 projection design, which is yours, so I'd rather align than barge in.

Per-column caching (option 1) is still worth keeping as the complementary half — it bounds the retained set once the prefix is narrowed, and helps allColumns-adjacent cases — but on its own it moves the cliff rather than removing it for wide tables.

Fallback if narrowing the prefix is more than we want here: option 1 + a larger/GUC cap (your option 2) — 33.8 MB fits under 64 MB — or the honest small-hammer, lowering the default stripe (option 3). Your call on the trade; I've got option 1 implemented and can carry whichever direction you pick.

[jdatcmd]

Correction: my mechanism was wrong, and so was my recommendation

#357 instruments the allocation and finds the actual cause. Amending this issue so the wrong recommendation is not left standing for someone to act on.

What I wrote: that a 150,000-row group of a 21-column table decodes to more than 32 MB, so the cache cannot hold it.

What is actually true: the decoded result is about 11 MB. The cache entry was inflated to ~34 MB by the decode's scratch -- the decompressed encoded region and each vector's decoded buffer -- allocated in the caller's context and never freed. On the scan path that context is a group context reset every group, so it never mattered. On the fetch path it is the statement-scoped cache entry, which is not reset, so the scratch stayed live and roughly tripled the entry.

Why the difference matters: I recommended per-column caching as "the only option that removes the cliff rather than moving it". That would not have worked. The deferred index fetch decodes a prefix rather than the touched columns, so the entry would still not fit under the cap. Anyone following my recommendation would have built something substantial and still had the bug.

The fix is to stop putting transient scratch in a cached allocation: 19 lines, one function, no change to the cap or the default stripe size.

What in this issue still stands: the measurements. The 186x cliff between 150,000 and 100,000, the flat ~28.7 ms per row across a twelve-fold change in row count, the 100M-bench figures, and the reproduction script are all accurate and were what located the defect.

What did not: the mechanism I inferred from them, and everything I recommended on the strength of that inference. The numbers said "the entry does not fit". I assumed that meant the data was too big, without measuring what was in the entry.

Verified after the fix, same script and fixture:

stripe_row_limit	before	after
150,000 (default)	32.98 ms/row	0.201 ms/row
100,000	0.177 ms/row	0.140 ms/row
50,000	0.125 ms/row	0.119 ms/row

The cliff is removed rather than relocated: the three sizes now differ by a smooth factor reflecting group count, not by 186x across a threshold.

The workaround this issue recommended -- creating point-query tables with a smaller stripe_row_limit -- is no longer needed once #357 lands, and docs/benchmarks.md should drop that advice when it does. Noted on #356.

[jdatcmd]

Fixed by #357, verified on merged main

Closing. #357 merged as 23bd094; the bench was rebuilt from merged main and the library md5 confirmed against the installed one before measuring, so these numbers are from main, not from the branch.

The issue's own reproduction, re-run

Same script, same 300,000-row 21-column fixture, same three group sizes, all taking an Index Scan:

stripe_row_limit	when filed	on merged main
150,000 (the default)	32.98 ms/row	0.198 ms/row
100,000	0.177 ms/row	0.143 ms/row
50,000	0.125 ms/row	0.141 ms/row

167x at the default. More important than the ratio: the cliff is gone rather than relocated. The three sizes now differ by a small factor that tracks group count, where before 150,000 was 186x worse than 100,000 across a threshold.

At 100M scale

Same fixture and method as docs/benchmarks.md, on merged main:

query	rows returned	when filed	on merged main
q1, one host, 1 hour	360	10,335 ms	274 ms	37.7x
q2, one host, 12 hours	4,320	123,546 ms	1,959 ms	63x

(q3 was still running when this was written; it is the same shape as q2 with five aggregates and is expected to follow.)

What the fix was, and why my analysis here was wrong

The cause was not what this issue claimed. I wrote that a 150,000-row group of a 21-column table decodes to more than 32 MB. It does not; the decoded result is about 11 MB. The entry was inflated to ~34 MB by the decode's scratch, allocated in the caller's context and never freed. That context is reset per group on the scan path, which is why nothing showed there, and is the statement-scoped cache entry on the fetch path, which is not reset.

#357 puts the scratch in a transient child context and deletes it. Nineteen lines, no change to the cap and no change to the default stripe size.

My recommended fix in this issue -- per-column caching -- would not have worked. #357 showed why: the deferred index fetch decodes a prefix rather than the touched columns, so the entry would still not have fit under the cap. That correction is recorded above in this thread so the recommendation is not left standing.

What this issue got right was the measurement: the cliff, the flat per-row cost across a twelve-fold change in row count, and the reproduction that located it. What it got wrong was the mechanism inferred from those numbers, which is the part that needed instrumenting rather than reasoning about.

Follow-ups, not part of this issue

• Planner chooses an Index Scan on columnar for ordering; per-row fetch cost is not modelled and the plan is orders of magnitude slower #355 remains open and is unaffected: the planner still prefers an index scan over a columnar table for ordering without modelling per-row fetch cost. That is a separate defect that this fix reduces in magnitude but does not remove.
• docs/benchmarks.md still carries the q1 to q3 figures from before this fix, attributes them to this issue, and advises a smaller stripe_row_limit as a workaround. All three are now wrong and are being corrected.

[jdatcmd]

Correction: "the cliff is gone rather than relocated" was wrong

I wrote that here and in my review of #357. It does not hold. Filed as #359.

On merged main, same rows, same Index Scan, varying only the aggregated column count:

aggregate columns	time
4	2,833 ms
5	134,147 ms

A 47x jump, flat either side. The 32 MB cap is still a cliff; #357 moved it outward by roughly 3x, which is exactly the entry inflation it removed.

How I got it wrong: my verification used count(*), max(usage_user), a narrow projection, across three group sizes. Within that shape the cliff genuinely is gone. I generalised that to the cliff being gone, when the supported claim was "gone at this projection width".

The counter-evidence was already in hand. The same run that gave q1 274 ms and q2 1,959 ms also gave q3 135,911 ms against 161,972 before, a 1.19x improvement where the others were 38x and 63x. q2 and q3 differ by one aggregate versus five. I reported the two large improvements and did not ask why the third barely moved.

What stands: this issue is still correctly closed. Its specific instance, the default stripe_row_limit putting a narrow query over the cap, is fixed, and #357 is a real fix worth 63x on q2. What does not stand is my characterisation of it as removing the cliff rather than moving it. #359 covers the general form.