Taxonomy-filtered collection queries do a full table scan (75k+ D1 row reads) on selective terms

[MA2153]

Taxonomy-filtered collection queries do a full table scan (75k+ D1 row reads) on selective terms

Summary

The loader's SQL for a taxonomy-filtered collection listing (collection?taxonomy=… style query, ordered + paginated) is structured so that SQLite cannot use the selective taxonomy filter to drive the scan. Instead it walks the entire collection table in ORDER BY order, running the taxonomy filter as a correlated EXISTS per row. When the chosen term matches few entries, the LIMIT never fills, so the query scans the whole table.

On a collection of ~26k entries this produced ~75,000 D1 rows read for a query returning a single row (≈295 ms). On D1, rows-read is the billed/throttled unit, so a routine category page can read the whole table.

Two independent causes, both fixable in the framework:

1. Missing composite index on the content↔taxonomy join table.
2. Query shape — the collection table is the scan driver instead of the taxonomy join table.

Environment

• Single-locale site (e.g. defaultLocale: "de"), so the locale predicate is not selective — every published entry shares the one locale.
• A collection with a taxonomy that has many terms, each mapping to few entries (e.g. a fine-grained category/tag).
• Backend: Cloudflare D1 (SQLite).

The generated query (shape)

For a listing filtered to a taxonomy term, ordered by published_at DESC with a LIMIT, the loader emits roughly:

SELECT *,
  (/* _emdash_terms correlated subquery */),
  (/* _emdash_bylines correlated subquery */)
FROM "ec_<collection>"
WHERE deleted_at IS NULL
  AND (status = 'published' OR (status = 'scheduled' AND scheduled_at <= <now>))
  AND locale = ?
  AND EXISTS (
    SELECT 1 FROM content_taxonomies ct
    WHERE ct.collection = ?
      AND ct.entry_id = "ec_<collection>".id
      AND ct.taxonomy_id IN (
        WITH RECURSIVE sub(grp) AS (
          SELECT COALESCE(translation_group, id) FROM taxonomies
          WHERE name = ? AND slug IN (?)
          UNION
          SELECT COALESCE(c.translation_group, c.id)
          FROM taxonomies c JOIN sub ON c.parent_id = sub.grp
        )
        SELECT grp FROM sub
      )
  )
ORDER BY published_at DESC, id DESC
LIMIT ?;

Root cause

EXPLAIN QUERY PLAN for the above:

SEARCH ec_<collection> USING INDEX idx_ec_<collection>_deleted_published_id (deleted_at=?)
CORRELATED SCALAR SUBQUERY …   -- terms
LIST SUBQUERY … / MATERIALIZE sub   -- recursive taxonomy CTE (materialized once, fine)
CORRELATED SCALAR SUBQUERY …   -- bylines

The outer driver is idx_ec_<collection>_deleted_published_id (deleted_at, published_at DESC, id DESC). The planner picks it to satisfy ORDER BY published_at DESC, id DESC without a sort — but it constrains only deleted_at. locale and status are not selective on a single-locale site, so this walks essentially the whole table in published_at order, evaluating the taxonomy EXISTS on every row.

Because the term is selective (few matching entries) and they are scattered through published_at order, the LIMIT is never satisfied early and the scan runs to the end of the table.

The EXISTS itself is indexed fine (covering seek on the content_taxonomies PK (collection, entry_id, taxonomy_id)), but it is invoked once per scanned row. Total reads ≈ (rows scanned) + (per-row EXISTS reads).

Reproduction / measurements

Collection ec_<collection>: 26,224 rows (all one locale, 24,383 published). Join table content_taxonomies: 76,606 rows. Filtering by a term that maps to 1 entry:

Variant	Plan	Rows read	Duration
Generated query (as above)	scan ec_<collection> by (deleted_at, published_at), EXISTS per row	75,454	~295 ms
Rewrite: id IN (SELECT entry_id FROM content_taxonomies …)	scans join table by collection prefix (76k)	102,210	~40 ms
Rewrite: drive from join table, join to collection, no hint	still scans join table by collection prefix	75,988	~23 ms
Same rewrite forced onto (taxonomy_id) index	seek by taxonomy_id, join by id, temp b-tree sort	10	~1 ms

The last row is the target behaviour: seek the selective side first.

Fix 1 — add a composite index on the join table

content_taxonomies currently has:

• PK (collection, entry_id, taxonomy_id) — supports "terms of an entry", not "entries with a term".
• idx_content_taxonomies_term (taxonomy_id) — the right idea, but the planner won't choose it when collection = ? is also present: it's non-covering for collection/entry_id, so the cost model prefers the PK's (collection=?) covering scan (whole-collection scan of the join table). This is why the un-hinted rewrite above still read ~76k.

Proposed index:

CREATE INDEX idx_content_taxonomies_term_lookup
  ON content_taxonomies (taxonomy_id, collection, entry_id);

This makes the seek by taxonomy_id covering for collection and entry_id, so the planner picks it without a hint. (Belongs in the framework's migration that creates content_taxonomies / its indexes.)

Fix 2 — restructure the taxonomy-filtered query

Even with the index present, the current shape still invites the planner to scan the collection table in ORDER BY order because the taxonomy filter is a correlated EXISTS. The query for a taxonomy-filtered listing should drive from content_taxonomies seeking by taxonomy_id and join to the collection table by entry_id, e.g.:

SELECT r.*, /* terms + bylines subqueries */
FROM content_taxonomies ct
JOIN ec_<collection> r ON r.id = ct.entry_id
WHERE ct.collection = ?
  AND ct.taxonomy_id IN (/* recursive CTE */)
  AND r.deleted_at IS NULL
  AND (r.status = 'published' OR (r.status = 'scheduled' AND r.scheduled_at <= <now>))
  AND r.locale = ?
ORDER BY r.published_at DESC, r.id DESC
LIMIT ?;

With the composite index this seeks the small set of matching entries, joins by primary key, and sorts a tiny result set — the 10-rows-read plan above. The tradeoff (a temp b-tree sort instead of a pre-ordered index walk) is negligible because the candidate set is small, and it is vastly cheaper than scanning the whole collection whenever the term is selective.

Impact

• Any single-locale (or otherwise low-locale-selectivity) site paginating a collection filtered by a selective taxonomy term reads the entire collection table per request on D1, where rows-read drives cost and throttling.
• Worst case scales with collection size × requests, not with result size.

Suggested resolution

1. Add content_taxonomies (taxonomy_id, collection, entry_id) to the schema/migrations.
2. Change the taxonomy-filtered collection query to drive from content_taxonomies (seek by taxonomy_id) and join to the collection table, rather than scanning the collection with a correlated EXISTS.

---

Relevant discussion: #1851