A projection is priced by clauses that merely mention its sort key, not by clauses it can prune on

[OffgridwithJD]

Found while re-reviewing #1107, which fixes the wider form of this. Not a blocker for
that PR — it narrows this defect rather than introducing it, and the plan choice is the
same before and after. Filing so the remainder is not lost.

The rule the planner uses, and the rule the executor uses

Both eligibility and pricing ask the same question: does this clause mention
sortKey[0]?

/* eligibility */
skips = (p->sortKeyLen > 0 &&
         bms_is_member(p->sortKey[0] - FirstLowInvalidHeapAttributeNumber, restrictCols));

/* pricing, after #1107 */
pull_varattnos((Node *) ri->clause, rel->relid, &cols);
if (bms_is_member(sortAttno - FirstLowInvalidHeapAttributeNumber, cols))
    clauses = lappend(clauses, ri);

Mentioning the sort key is not the same as being prunable by it. A single
RestrictInfo that ORs a sort-key range with a predicate on another column mentions the
sort key, is therefore counted whole, and contributes a selectivity the sort order cannot
deliver.

The executor already makes the distinction the planner does not. It reports
Columnar Usable Skip Predicates, and that counter is exactly the difference between the
two cases below.

Measured

pgcolumnar-audit, /usr/local/pg18a (assert), PR #1107 at b15fb8a. 20,000 rows,
stripe_row_limit => 1000, chunk_group_row_limit => 500, physical order scrambled so
the base scan cannot prune. Projection onsk over (sk, kind) sorted on sk.
kind = 'odd' where sk % 1000 = 0, so its 20 rows are spread across the whole sk
domain.

control   WHERE sk BETWEEN 1 AND 2000                     -- 2000 rows, contiguous
residual  WHERE sk BETWEEN 1 AND 2000 OR kind = 'odd'      -- 2018 rows, 18 of them scattered

Both are priced the same:

	control	residual
proj run cost	36.83	50.85
base run cost	368.67	504.50
ratio	0.0999	0.1008

The runtime is not the same. From EXPLAIN (ANALYZE, BUFFERS) on the residual query:

                                   projection ON    projection OFF
Columnar Pushed-Down Filters:            0                0
Columnar Usable Skip Predicates:         0                0
Columnar Vectors Skipped:                0                0
Columnar Chunk Groups Read:             20               20   (of 20)
Columnar Vector Decodes:               120               80
Execution Time:                      2.653 ms         1.996 ms

The projection prunes nothing — no pushed-down filter, no usable skip predicate, no
zone-map probe — reads every chunk group, decodes 50% more vectors, and finishes 33%
slower. It is quoted at 10% of the base.

The control, for contrast, earns its discount: Usable Skip Predicates: 2,
Vectors Skipped: 20, and Rows Removed by Filter falls from 18000 to 8000.

Why it is not a regression

Before #1107 the constant 0.5 priced this query at half the base, which also beats the
base and is also chosen. So the planner picks the projection for this shape either way;
#1107 changes how confidently. I am recording that explicitly so the entry is not read as
something that PR broke.

The shape of a fix

Key the test on usability as a skip predicate on the sort key, not on the sort key being
mentioned — the property the executor already computes and prints. A clause that is an OR
with a non-sort-key disjunct is not usable, and should contribute selectivity 1.0 to the
projection's price even though it mentions the key.

A guard arm belongs with it, because both wrong versions of this rule are silent: an
over-inclusive one quotes a discount nothing delivers, and an under-inclusive one declines
a projection that would have won. The existing suite's prsk fixture already has the two
columns needed; this needs one more query shape.

Reproduction

test/projection_scan_cost.sh's fixture with one query added. I ran it as a standalone
probe rather than a suite edit, since the suite belongs to #1107 while that is open.

CREATE TABLE pror (sk int, kind text) USING pgcolumnar;
SELECT pgcolumnar.set_options('pror', stripe_row_limit => 1000, chunk_group_row_limit => 500);
INSERT INTO pror SELECT sk, CASE WHEN sk % 1000 = 0 THEN 'odd' ELSE 'usual' END
  FROM generate_series(1, 20000) sk ORDER BY md5(sk::text);
SELECT pgcolumnar.add_projection('pror', 'onsk', ARRAY['sk','kind'], ARRAY['sk']);
ANALYZE pror;

SET max_parallel_workers_per_gather = 0; SET jit = off;
SET pgcolumnar.enable_ungrouped_vector_agg = off;
SET pgcolumnar.enable_group_vectorization = off;
EXPLAIN (ANALYZE, BUFFERS)
  SELECT sk FROM pror WHERE sk BETWEEN 1 AND 2000 OR kind = 'odd';

One note on the fixture, because my first version of it was vacuous: with the range at
sk BETWEEN 1 AND 100 both arms land on the one-stripe floor (20 stripes, so 0.05) and
the two cases are indistinguishable. The sort-key range has to be well above the floor for
the question to be askable at all.

🤖 Generated with Claude Code

https://claude.ai/code/session_012RSw4qMHS7ByE7PY8Ns4cs

[OffgridwithJD]

Taking this, now that #1107 has merged and the file is free.

@linuxhikerpm if you would rather carry it on from #1107 say so and I will hand it straight back — you have the context on that costing path and I would not be picking it up if the PR were still open.

Plan, before I write anything:

1. Find what makes a predicate usable on the sort key, rather than merely mentioned. The executor already computes this and prints it as Columnar Usable Skip Predicates, so the first question is whether the planner can see the same notion or needs its own.
2. Failing arm first, on the OR shape in the issue — confirm it is red for the stated reason before touching columnar_customscan.c.
3. A false-positive budget over the tree: the narrow risk here is declining a projection that would have won, which is silent, so the guard needs both directions.

I will post what I find at step 1 before building, since it decides whether this is a small change or a structural one.

[OffgridwithJD]

Step 1, before building. The planner already has the notion; it just is not being asked.

`pgcolumnar_clause_to_scankey` is the function that decides whether a clause can drive chunk-group skipping. It is static in `columnar_customscan.c:947`, it sits above the pricing site, and it answers exactly the question the price needs:

static int
pgcolumnar_clause_to_scankey(Node *clause, Index scanrelid, TupleDesc tupdesc,
                             ScanKey key, bool *exact)

• returns 0 for anything it cannot use — and if (!IsA(clause, OpExpr)) return 0; is the line that rejects the BoolExpr in my OR case
• writes key[i].sk_attno = var->varattno, so which column the key prunes on is readable rather than inferred
• handles ScalarArrayOpExpr (IN) and the anchored-LIKE bound, which a hand-rolled shape test would have got wrong in both directions

So the predicate becomes "this clause yields at least one scan key, and every key it yields is on sortAttno", replacing "this clause mentions sortAttno".

There is a precedent for the exact loop, which matters more to me than the idea being right — PgColumnarQualsExactlyKeyed does this already, for the batch fold:

ScanKeyData scratch[2];  /* an anchored LIKE writes two keys */
n = pgcolumnar_clause_to_scankey((Node *) lfirst(lc), scanrelid, tupdesc, &scratch[0], &exact);

Two slots, because one clause can produce a lower and an upper bound. I will use the same shape rather than a second convention.

So this is a small change, not a structural one. One extra condition in pgcolumnar_sortkey_selectivity, and a TupleDesc threaded in — pgcolumnar_choose_projection already does table_open(relid, AccessShareLock) two lines away, so the relation is at hand.

Two things I want to get right rather than fast:

• exact is not the test. The fold needs exactness; pruning does not. An anchored LIKE or an IN range prunes honestly and must keep its discount, so gating on exact would decline projections that genuinely win — the silent direction.
• A clause yielding keys on two different columns. I have not yet found one that can, but "I did not find one" is not "none exists", so the loop will require every key to be on the sort key rather than just the first.

Next: the failing arm, on the OR shape, confirmed red for the stated reason before I touch the C.