foldedHydrationSelects term subquery drives from taxonomies by locale on SQLite/D1, scanning all locale terms per emitted row #1722

Summary

The folded per-entry term-hydration subquery in foldedHydrationSelects
(packages/core/src/loader.ts) is a plain JOIN that, on SQLite/D1 without
statistics, the query planner drives from taxonomies by locale. It enumerates
every term in the locale and probes the pivot for each emitted row, so a
single collection-list page reads tens of thousands of rows even with no filter.

Affected version

Observed on 0.26.0; the subquery shape is unchanged across recent versions.

The query

foldedHydrationSelects emits (SQLite branch), roughly:

SELECT *,
  (SELECT json_group_array(json_object(...))
     FROM content_taxonomies AS ct
     JOIN taxonomies AS t ON t.translation_group = ct.taxonomy_id
    WHERE ct.collection = ?
      AND ct.entry_id   = <outer>.id
      AND t.locale       = <outer>.locale) AS _emdash_terms,
  ( ... bylines ... ) AS _emdash_bylines
FROM <collection>
WHERE deleted_at IS NULL AND (status = 'published' OR ...) AND locale = ?
ORDER BY <date> DESC, id DESC
LIMIT 25

Mechanism

D1 does not maintain sqlite_stat1 (ANALYZE never runs, and D1 doesn't run it
for you). Stats-blind, SQLite drives the correlated subquery from the
taxonomies side:

EXPLAIN QUERY PLAN (the _emdash_terms subquery)
|--SEARCH t  USING INDEX idx_taxonomies_locale (locale=?)      <-- ALL terms in the locale
`--SEARCH ct USING COVERING INDEX sqlite_autoindex_content_taxonomies_1

For each of the 25 emitted entries it walks every term in the locale
(t.locale = ?) and probes the pivot. On a taxonomies table with ~1,540 terms
in the active locale, one unfiltered page reads ≈ 25 × 1,540 ≈ 38.5K rows —
the floor for every list page, paid on every cache miss. Cost scales with
taxonomy_size × page_size, not with the handful of terms an entry actually has.

Fix

Drive the join from the pivot (content_taxonomies) by (collection, entry_id)
and probe taxonomies by translation_group. SQLite honours CROSS JOIN
ordering, so this is stats-independent and deterministic:

FROM content_taxonomies AS ct
CROSS JOIN taxonomies AS t ON t.translation_group = ct.taxonomy_id
WHERE ct.collection = ? AND ct.entry_id = <outer>.id AND t.locale = <outer>.locale

after which:

`--SEARCH ct USING COVERING INDEX sqlite_autoindex_content_taxonomies_1 (collection=? AND entry_id=?)
`--SEARCH t  USING INDEX idx_taxonomies_translation_group (translation_group=?)

— a handful of reads per entry instead of ~1,540, dropping per-page hydration
from ~38.5K rows to a few hundred.

Notes

• Gate to the SQLite dialect. foldedHydrationSelects already branches on
  pg for the aggregate (json_agg vs json_group_array); Postgres has
  statistics and CROSS JOIN … ON is not valid Postgres syntax, so apply the
  CROSS JOIN only on the SQLite path.
• The byline hydration subquery in the same function has the same structure
  (JOIN _emdash_bylines AS b ON b.translation_group = cb.byline_id … AND b.locale = <outer>.locale) and can pick the same locale-driven plan; it merits
  the same treatment.
• Verified via EXPLAIN QUERY PLAN on a D1-shaped SQLite DB (no sqlite_stat1);
  the ~38.5K figure matches production OTel rows_read for an unfiltered page.