Content Drive: use a materialized folder-first CTE for the candidate-scan query on large folders

Description

Spike #37183 validated a concrete direction for the folder-listing candidate-scan query's planner instability on large folders (564ms on a ~21,000-child folder vs <20ms on folders up to ~4,000).

Validated on a live instance (raw SQL, not yet as an application-level change):

• A usable index already exists (identifier_parent_path_asset_name_host_inode_key, unique on parent_path, asset_name, host_inode) — no schema change needed.
• Wrapping the folder lookup in WITH folder_ids AS MATERIALIZED (...) before joining out to contentlet_version_info/contentlet/structure forces a stable, fast access path. Plain (non-materialized) CTEs get inlined by Postgres and can still hit the bad plan — materialization is what matters.
• Measured: 21,383-child folder 470-490ms → 120-136ms. A folder that was already fast (39ms, 6,061 children) picks up a small, bounded cost (~63-68ms) — not the 3x regression an earlier, differently-shaped rewrite showed.
• Root cause is not folder size — two folders of near-identical size (4,252 vs 6,061 children) showed opposite baseline behavior (227ms vs 39ms) depending on how their content is distributed relative to mod_date order, not their child count. A size-based special case would misroute both. Full detail: #37183.
• At production scale in the reference dataset, 106 folders exceed 500 children — the population this affects.

Scope

• Prototype the materialized-CTE approach as an actual BrowserAPIImpl change (not the simplified raw SQL tested in the spike) — the real query has additional dynamic predicates (structure exclusions, variant_id, language, etc.) not present in the spike's test query.
• Preserve permission filtering exactly as today — untested in the spike.
• Verify against every existing filter combination this query serves (tag, workflow, field filters), not just the unfiltered case.
• Re-run the full size matrix from the original investigation (empty, small, medium, large, extreme folders) plus deep pagination, both roles (admin and permission-limited).

Explicitly out of scope

• The prepared-statement plan-cache mechanism flagged in #37183 as the probable cause of latency bimodality (~4x swing reproduced directly via PREPARE/EXECUTE, but not confirmed reachable in production given CACHE_PREPARED_STATEMENTS defaults to false). Documented as an open question in #37183; may warrant its own follow-up if the team wants to pursue it, but does not block this fix — the CTE fix's benefit stands independently.
• #37184's field-filter chunk multiplier (separate issue, shares this same underlying query — coordinate before either merges).

Acceptance Criteria

• The materialized-CTE approach is implemented in BrowserAPIImpl and produces identical result sets to the current query, for empty/small/medium/large/extreme folders and at least one deep-pagination case
• The largest tested folder drops from the current ~470-490ms baseline to within the same order of magnitude as the spike's ~120-136ms finding
• No folder in the test matrix regresses by more than a small, bounded amount versus its current baseline (spike observed ~25-30ms on an already-fast folder; a large regression anywhere is a signal something differs from the spike's simplified query)
• Permission filtering is verified unchanged for both an administrator and a permission-limited user
• Behavior is verified unchanged when combined with tag, workflow, and field filters
• Tests written and confirmed failing first (Constitution Principle V) before the implementation change
• Coordination with #37184 confirmed: the single-scan-per-request assumption that fix depends on still holds

Related

• Spike: #37183 (full investigation, measurements, and the folder-size-vs-match-density finding)
• Coordination: #37184 (shares this query)
• Parent epic: #36814

Start in BrowserAPIImpl and compare the real candidate-scan query with the materialized-CTE SQL validated in #37183, including its dynamic predicates and permission filtering. Write the required failing tests first, then run the full folder-size, pagination, role, tag, workflow, and field-filter matrix while coordinating with #37184. Done means identical results, improved large-folder latency, and only small bounded regressions.