[Cypher] the two count push-downs disagree on their preconditions and their entry points: a dropped SKIP/LIMIT, and three shapes that scan when they need not

[lvca]

Five findings about the two Cypher count push-downs, collected while closing #5686 (PR #5706) and all measured against ee136e494. They are filed together because they are five consequences of the same thing: tryCreateTypeCountOptimization and tryOptimizeCountStar are two independent detectors, reached from different places, agreeing on neither their preconditions nor their entry points.

Section 1 is a wrong answer and is the only one that is not a performance note.

Measurements are readRecord deltas from database.getStats(), which separate the two plans exactly: a push-down answers from the cached type counter or the CSR arrays and reads nothing, the ordinary pipeline reads one record per vertex. Fixture: 300 :Big vertices, a 3-vertex :Q chain with 2 :LINKS edges, 100 :Lonely vertices with no edges at all.

---

1. The count-star push-down discards SKIP and LIMIT

rows=1  MATCH (a:Q)-[:LINKS]->(b:Q) RETURN count(*) AS c            => [{"c":2}]
rows=1  MATCH (a:Q)-[:LINKS]->(b:Q) RETURN count(*) AS c LIMIT 0    => [{"c":2}]   <-- expected 0 rows
rows=1  MATCH (a:Q)-[:LINKS]->(b:Q) RETURN count(*) AS c SKIP 1     => [{"c":2}]   <-- expected 0 rows
rows=1  MATCH (a:Q)-[:LINKS]->(b:Q) RETURN count(*) AS c SKIP 5     => [{"c":2}]   <-- expected 0 rows

The other push-down, and the ordinary pipeline, both get it right, so the engine currently disagrees with itself:

rows=0  MATCH (m:Big) RETURN count(m) AS c LIMIT 0                  => []
rows=0  MATCH (m:Big) RETURN count(m) AS c SKIP 1                   => []
rows=0  MATCH (m:Big) RETURN m.k AS c LIMIT 0                       => []

Neo4j returns no rows for all of these: LIMIT 0 and SKIP 1 are applied to the one-row aggregate result.

Why. SKIP/LIMIT/ORDER BY are statement-level fields, not ClauseEntry.ClauseType values, so hasOnlyMatchAndReturnClauses() - which only iterates getClausesInOrder() - cannot see them, and no tryOptimizeCountStar detector checks getSkip(), getLimit() or getOrderByClause() (0 occurrences across all five). tryCreateTypeCountOptimization rejects all three explicitly, which is why only one of the two is wrong. The push-down returns a step that replaces the whole chain, so the SkipStep/LimitStep are never built.

ORDER BY is unchecked by the same omission. It cannot change a single-row result today, so it is latent rather than wrong - but it is unchecked for the same reason and would become wrong the moment a push-down returned more than one row.

Fix. Reject a statement carrying any of the three in tryOptimizeCountStar, the way tryCreateTypeCountOptimization already does. Better: make hasOnlyMatchAndReturnClauses() answer for the whole statement rather than for the clause list, so the two detectors cannot drift again - it reads like a whole-statement predicate and is used as one.

---

2. tryCreateTypeCountOptimization is unreachable from a top-level query

The same body is O(1) inside a subquery and a full scan written on its own:

reads=300  MATCH (m:Big) RETURN count(m) AS c
reads=0    RETURN COLLECT { MATCH (m:Big) RETURN count(m) } AS c

Why. execute() calls tryOptimizeCountStar before the optimizer dispatch, but never tryCreateTypeCountOptimization; that one is only reached from buildExecutionStepsWithOrder, which a top-level query reaches only when canUseOptimizedPhysicalPlan() is false. A single-label MATCH ... RETURN count(v) satisfies the optimizer, so it takes buildExecutionStepsWithOptimizer and scans. A subquery body always goes through buildExecutionStepsWithOrder (executeWithSeedRow), so it gets the fast path the plain query does not.

This is the wrong way round: the cheap O(1) counter is available to the harder case and not to the simplest one anybody writes.

Fix. Attempt tryCreateTypeCountOptimization next to tryOptimizeCountStar in execute(), under the same "before the optimizer dispatch" comment that already explains why the other one is there.

---

3. count(*) does not get what count(v) gets

reads=0    RETURN COLLECT { MATCH (m:Big) RETURN count(m) } AS c
reads=300  RETURN COLLECT { MATCH (m:Big) RETURN count(*) } AS c

Why. tryCreateTypeCountOptimization requires the argument to be a VariableExpression naming the MATCH variable, and every tryOptimizeCountStar detector requires at least one relationship. So a single-node pattern with count(*) - the spelling most users reach for, and the one Neo4j documents - falls between the two detectors and is claimed by neither.

For a single-node pattern the two are the same count, since the pattern produces exactly one row per node.

Fix. Accept StarExpression alongside the matching variable in tryCreateTypeCountOptimization. Combined with section 2, MATCH (m:Big) RETURN count(*) becomes O(1).

---

4. COUNT { MATCH (m:Label) } always scans

reads=300  RETURN COUNT { MATCH (m:Big) } AS c

Why. A bare-pattern body is normalised to MATCH (m:Big) RETURN 1 and CountExpression counts the rows it produces, so the count is a row count over a materialised scan. No push-down applies, because the RETURN is a literal.

Neo4j answers COUNT { (m:Label) } from its counts store. COUNT { MATCH (m:Label) } with no WHERE and no relationship is exactly Type.count(), and it is the natural way to ask "how many of these are there" inside a larger query - the shape #5686's reporter had in mind, though it turned out not to be the shape that issue was about.

Fix. Recognise the normalised bare-pattern body in CountExpression, or teach the push-down that a RETURN of a constant makes the row count equal to the match count.

---

5. The CSR chain push-down scans both endpoint label sets even when the edge type is empty or absent

reads=200  MATCH (a:Lonely)-[:LINKS]->(b:Lonely) RETURN count(*) AS c        => [{"c":0}]
reads=200  MATCH (a:Lonely)-[:NOSUCHTYPE]->(b:Lonely) RETURN count(*) AS c   => [{"c":0}]

100 :Lonely vertices, no edges: 200 records read to return 0, and the same 200 for an edge type that does not exist in the schema at all. The push-down is applied unconditionally, with no cost check and no early-out.

Not a correctness problem, and pre-existing rather than introduced by anything recent, but the non-existent-type case is free to fix and the empty-type case is a plain waste of a scan.

Fix. Answer 0 immediately when no declared edge type in the chain exists; consider skipping the push-down when the edge type has no edges, since the ordinary pipeline reads one endpoint set rather than two.

---

Also worth doing, larger

Both push-downs are all-or-nothing about correlation: #5686 restored them for an uncorrelated body under a correlated query, and a genuinely correlated body still falls back to materialisation. But MATCH (q)-[:LINKS]->(x:Q) RETURN count(*) with q already bound is an O(degree) read of the CSR adjacency arrays, not a scan. A seed-aware CSRCountStep that starts from the bound anchor instead of from a label scan would keep the fast path for correlated bodies too, which is strictly more than #5686 recovered.

That one is a design change rather than a fix and probably deserves its own issue if anyone picks it up.

---

Sections 1 to 3 are the ones I would do first: 1 is a wrong answer, and 2 and 3 together are what make MATCH (m:Label) RETURN count(*) - about the most common counting query there is - a full scan.

[lvca]

Fixed by #5737 (merged a997e5a82).

All five sections were reproduced against 57b73c226 before anything was changed, and all five are fixed. Two of them landed differently from what this issue proposed:

• Section 1 proposed rejecting a statement carrying SKIP/LIMIT. They are applied to the single row a push-down produces instead, so RETURN count(*) LIMIT 1 keeps the fast path while LIMIT 0/SKIP 1 correctly return nothing. ORDER BY still falls back to the ordinary pipeline.
• Section 5 proposed answering 0 when a declared edge type does not exist. It is generalised to any node label or relationship type of a non-optional pattern that is undeclared or empty, which also covers the empty-edge-type case this issue called "a plain waste of a scan". A LIGHTWEIGHT edge type is excluded, since it writes no edge record and so has a 0 counter while its edges exist.

Three defects not described here were found while fixing them, and are fixed in the same PR:

query	before	correct
MATCH (a)-[:LINKS]->(b) RETURN count(*)	0	2
MATCH (a:Q)-[:LINKS]->(b:NoSuchLabel) RETURN count(*)	2	0

Both come from CSRCountUtils.buildValidBuckets returning null for two different things - "no label was given, so do not filter" and "the label is declared but does not exist, so nothing matches" - with the anchor code reading the first as "no anchors" and the hop code reading the second as "no filter". Neither shape had a test.

The third: EXPLAIN never consulted the push-downs at all, and PROFILE printed the optimizer's physical plan even when a push-down had replaced the chain. Both described a plan the engine does not run. That predated this issue, which widened it.

The "Also worth doing, larger" section - a seed-aware CSRCountStep for a genuinely correlated body - is not done and is filed separately.