Planner chooses an Index Scan on columnar for ordering; per-row fetch cost is not modelled and the plan is orders of magnitude slower

[jdatcmd]

Summary

The planner will choose an Index Scan over a columnar table to obtain ordering, because a columnar index scan is costed as if per-row fetch were as cheap as heap's. It is not, and the resulting plans are catastrophically slow. Related to #353 but not fixed by it.

Reproduction

cpu_pgc, 100M rows, with a (hostname, time DESC) btree. A grouped aggregate over a 12-hour window (~17.8M rows):

SELECT date_trunc('hour',time), hostname, avg(usage_user)
FROM cpu_pgc
WHERE time >= '2024-01-01' AND time < '2024-01-01 12:00'
GROUP BY 1,2;

Serial (max_parallel_workers_per_gather=0):

GroupAggregate  (cost=802.80..3692838.88)
  ->  Incremental Sort  (cost=802.80..3430210.01 rows=17825280)
        Presorted Key: hostname
        ->  Index Scan using cpu_pgc_hostname_time_idx  (cost=0.57..2126891.87 rows=17825280)

Cancelled after 79 minutes, incomplete.

Parallel, same query, same data:

GroupAggregate  (cost=1179516.40..3576448.75)
  ->  Gather Merge -> Sort
        ->  Parallel Custom Scan (ColumnarScan)  (cost=0.00..525045.25)

7,322 ms.

The cost model is wrong by orders of magnitude

The index scan is priced at 2,126,891 against the columnar scan's 525,045 -- about 4x apart. Measured, they are at least 70,000x apart, and the index-scan arm did not finish.

The planner picks it because the index supplies hostname presorted, so an Incremental Sort replaces a full sort. That is sound reasoning for heap, where fetching a row costs a page access. On a columnar table a fetch by row number decodes a row group, which is a different order of cost entirely, and nothing communicates that to the planner.

Not the same as #353, though they compound

#353 is that the default stripe_row_limit puts a group over the 32 MB fetch cache, making each fetch ~28.7 ms instead of ~0.18 ms. Fixing it would take this query from "days" to roughly:

17,825,280 rows x 0.177 ms = about 53 minutes

against a 7.3-second sequential scan. Still the wrong plan by a factor of about 430. So #353 makes this catastrophic, and fixing #353 leaves it merely bad.

The two need separate fixes:

• Default stripe_row_limit exceeds the 32MB fetch cache, making indexed point queries ~186x slower #353: the per-fetch constant is far larger than it should be.
• this: the planner does not know that a columnar per-row fetch is expensive relative to a scan, at any constant.

Why it is easy to miss

It only appears when the planner has a reason to prefer the index that is not selectivity, such as ordering for a sort or a merge join. Selective point lookups take the index for the obvious reason and are merely slow (#353). This shape reads most of the table through the index and is unbounded in practice.

It also disappears under parallelism, because the parallel path has no index alternative, which is why the same query is 7.3 seconds parallel and unfinished serially. Anything that disables parallelism -- max_parallel_workers_per_gather=0, a parallel-unsafe function, a small max_parallel_workers -- re-exposes it.

Suggested direction

Cost a columnar index scan with a per-row fetch penalty reflecting what a fetch by row number actually costs: a group decode amortised over the fetch cache, not a page access. That should make the ordering benefit lose except when the row count is genuinely small, which is the case where index scans on this storage make sense.

Provenance

Found while re-measuring the cross-engine benchmark for #348. It is why the published table's serial methodology cannot be reproduced today: the fixture now has indexes it did not have then, and serial plan choice on a columnar table with an index is pathological.

[ChronicallyJD]

The analysis is correct — I confirmed the fetch path and the cost gap. The columnar index scan is priced as if per-row fetch were as cheap as heap's (2,126,891 vs the columnar scan's 525,045, ~4×), while measured they are 70,000×+ apart, so the planner takes the index-scan-for-ordering plan and it is catastrophic.

Relationship to #353 (fix in #357): #353 cuts the per-fetch decode cost ~200×, but it does not fix this. The fetch cache holds four entries; an index scan in (hostname, time) order over a load-ordered table touches groups in a scattered order, so it thrashes the cache regardless of the per-fetch speedup. The real fix is the mis-costing — pricing the columnar per-row fetch like heap's — which is your cost-model area (the sibling of #350). Valid and still needed after #353; I'd only suggest re-taking the headline numbers once #357 has landed so the "70,000×" reflects the fixed fetch path.

[ChronicallyJD]

Taking this, per direction.

Plan: penalize columnar heap-fetching index-scan paths in ColumnarSetRelPathlist (where the seqscan is already replaced and index paths are kept at core cost) by the real per-row fetch cost — a row-group decode, not a page access. The penalty must scale with rows fetched so the ordering-benefit scan loses for large row counts (your 17.8M case) while a selective lookup still takes the index (#171 / #159 — a point lookup must not regress to a full scan). Index-only scans are exempt: they never call index_fetch_tuple, so no decode.

Costing on current main (23bd094, so #353/#357 is in): a fetch by row number now decodes a group once and reuses it while it stays cached, so the honest per-fetch cost is a group decode amortised over the fetch cache — cheap when the index order is group-clustered, ~a decode per fetch when scattered (index order != storage order, which is exactly the pathological shape). I'll design the amortisation + the crossover so the index wins only when the row count is genuinely small, and prove both directions (large-R ordering query picks scan+sort; small-R selective picks the index) before touching the default. Designing now.