Repository navigation
D1 full nested loop on taxonomy-filtered listings after 068 dropped the pivot sort indexes #3299
Description
Summary
Migration 068_content_taxonomy_entry_groups (#2461) drops the four content_taxonomies sort indexes created by 051_content_taxonomies_denorm (#1962) while rewriting buildTaxonomyPivotQuery to sort on r.<sort> (the ec_* column) through a CROSS JOIN. The result is a taxonomy-filtered listing that must materialize every assignment of the term before LIMIT can apply — on a stats-blind D1 deployment this is a nested loop over the whole collection.
The docstring for the indexed-sort branch still says "LIMIT lives in picked", but after 068 no index can satisfy (taxonomy_id, collection) plus r.published_at ordering. The early-LIMIT that #1834/#1962 introduced is gone.
Reproduction
Schema/data (Cloudflare D1, single-locale site, post-068):
ec_posts: 978 rows (931status='published' AND deleted_at IS NULL)content_taxonomies: 8,117 rows- Largest term: category
Newswith 907 assignments - Indexes on
content_taxonomies: PK,idx_content_taxonomies_term,idx_content_taxonomies_group_lookup— the fouridx_content_taxonomies_{pub,crt,loc_pub,loc_crt}sort indexes are gone
Query (the shape buildTaxonomyPivotQuery emits for where: { category: "News" }, orderBy: { published_at: "desc" }, limit: 31):
EXPLAIN QUERY PLAN
WITH picked AS (
SELECT r.id AS entry_id, r.published_at AS sortval
FROM content_taxonomies ct
CROSS JOIN ec_posts AS r ON r.translation_group = ct.entry_id
WHERE ct.collection = 'posts' AND ct.taxonomy_id = ?
AND r.deleted_at IS NULL AND r.status = 'published'
ORDER BY sortval DESC, r.id DESC
LIMIT 31
)
SELECT r.id FROM picked JOIN ec_posts AS r ON r.id = picked.entry_id;Plan (note the join order and the temp B-TREE — r is probed via idx_ec_posts_deleted_status, i.e. a full nested loop over the collection per pivot row):
CO-ROUTINE picked
SEARCH ct USING COVERING INDEX idx_content_taxonomies_group_lookup (taxonomy_id=? AND collection=?)
SEARCH r USING INDEX idx_ec_posts_deleted_status (deleted_at=? AND status=?)
USE TEMP B-TREE FOR ORDER BY
SCAN picked
SEARCH r USING COVERING INDEX sqlite_autoindex_ec_posts_1 (id=?)
Measured cost on production D1 for that one listing:
| shape | rows_read |
|---|---|
current (CROSS JOIN, sort r.published_at) |
847,162 |
same, plain JOIN instead of CROSS JOIN |
96 |
EXISTS semi-join driving from ec_posts (the non-pivot path) |
102 |
847,162 ≈ 907 pivot rows × 931 published posts — the CROSS JOIN pins content_taxonomies as outer and the stats-blind planner picks idx_ec_posts_deleted_status for r (it matches the deleted_at/status equalities) instead of idx_ec_posts_tg_locale (the join column), producing a nested loop.
After upgrading 0.30 → 0.38 (which applied 054–077, including 068) this site's D1 rowsRead went from ~80 rows/query to ~4,851 rows/query overnight (~170M rows/day), tracking the growth of the largest term. Roughly 140 listing-page hits/day are enough to account for the whole increase.
Suggested fix
In buildTaxonomyPivotQuery, drop the SQLite CROSS JOIN pin (const pivotContentJoin = isPostgres(db) ? sqlJOIN : sqlCROSS JOIN``) so the planner can drive from ec_* via `(deleted_at, published_at DESC, id DESC)` and probe the pivot by primary key. That restores early-`LIMIT` for free, for both common and rare terms, without re-adding the denormalized sort columns:
CO-ROUTINE picked
SEARCH r USING INDEX idx_ec_posts_deleted_published_id (deleted_at=?)
SEARCH ct USING COVERING INDEX sqlite_autoindex_content_taxonomies_1 (collection=? AND entry_id=? AND taxonomy_id=?)(The CROSS JOIN pin is still appropriate for the folded hydration subqueries in foldedHydrationSelects, where it guards a different hazard — #1722. This report is only about pivotContentJoin in buildTaxonomyPivotQuery.)
Alternative, closer to #1962: keep the pivot sorted on ct.<sort> and restore a (taxonomy_id, collection, deleted_at, <sort> DESC, entry_id DESC) index keyed by entry_id = content.translation_group. That needs the denorm columns re-stamped for the group-keyed rows, so it is the more invasive of the two.
Secondary: Archives.astro limit prop is display-only
components/widgets/Archives.astro calls getEmDashCollection("posts", { orderBy: { published_at: "desc" } }) with no limit, then applies groupEntriesByPublishedAt(posts, { type, limit }) in JS. The widget's limit prop (and settings.limit from _emdash_widgets) only slices the already-fetched group list. On the site above, one cache miss pulled all 931 published posts with the folded terms/bylines hydration subqueries (~25k rows) to render 6 links. A GROUP BY date-bucket aggregate (or at least pushing a fetch bound) would be a better default.
Environment
emdash/@emdash-cms/cloudflare0.38.0, SQLite/D1 backend (session: "auto")- Triggering migration:
068_content_taxonomy_entry_groups(introduced in 0.34.0 via Fix taxonomy assignments across content translations #2461) - D1 has no
sqlite_stat1(same constraint fix(loader): seek taxonomy-filtered listings via a denormalized pivot #1962 and 074's comments call out)
Activity
emdashbot commentedon Sep 23, 2026
Completed
Triage issue #3299: performance regression in taxonomy-filtered listings after migration 068 dropped pivot sort indexes.
Outcome: Confirmed. Migration `068_content_taxonomy_entry_groups` rewrote `buildTaxonomyPivotQuery` to sort on `ec_posts.published_at`/`created_at` and dropped the four denormalized `idx_content_taxonomies_*` sort indexes that `051_content_taxonomies_denorm` had added. On SQLite/D1 the builder still uses a `CROSS JOIN` for the `picked` CTE (`packages/core/src/loader.ts:951`), which forces `content_taxonomies` to be the outer table. The stats-blind planner then chooses an `ec_posts` index on `(deleted_at, status)` for the inner side and sorts the result in a temp B-tree, so `LIMIT` cannot short-circuit. The measured cost on the reporter’s site is ~907 pivot rows × 931 published rows ≈ 847k rows read per category listing.
The fix is bounded: change the SQLite branch of `pivotContentJoin` in `buildTaxonomyPivotQuery` from `CROSS JOIN` to a plain `JOIN` (PostgreSQL already uses `JOIN`). That lets the planner drive from `ec_posts` using the existing `idx_ec_posts_deleted_published_id`/`idx_ec_posts_deleted_created_id` indexes and probe `content_taxonomies` by its PK/`idx_content_taxonomies_group_lookup`, restoring early-`LIMIT`. The `CROSS JOIN` pin in `foldedHydrationSelects` should be left untouched, as noted in the report.
The existing plan test (`packages/core/tests/integration/loader-taxonomy-pivot-plan.test.ts`) didn’t catch the regression because it only asserts `not.toContain("SCAN r")`; a useful regression test would additionally assert that the content side uses the deleted-published/created index and that ordering avoids a temp B-tree for the common indexed-sort case.
Secondary: `packages/core/src/components/widgets/Archives.astro` currently fetches all published dates via `getPublishedDates` and only applies `limit` during JS grouping, so large blogs still over-fetch. That is a separate widget-scope optimization and should be handled after the main pivot fix.
Mode: triage
View live dashboard · Run:
6e6a9fce-7ede-44a0-ba18-9080964efc76emdashbot commentedon Sep 23, 2026
Completed
Fix D1 taxonomy-filtered listing nested loop regression by changing CROSS JOIN to JOIN in buildTaxonomyPivotQuery.
Outcome: Fixed the D1 taxonomy-filtered listing nested-loop regression (#3299) by changing the SQLite `pivotContentJoin` in `buildTaxonomyPivotQuery` from `CROSS JOIN` to a plain `JOIN`. This lets the stats-blind SQLite/D1 planner drive from the content table's `(deleted_at, published_at/created_at DESC, id DESC)` index and probe the pivot by primary key, restoring early-LIMIT and avoiding the full nested loop introduced by migration 068. The `CROSS JOIN` in `foldedHydrationSelects` is unchanged (guards a different byline-join hazard, #1722).
Added focused regression assertions to `loader-taxonomy-pivot-plan.test.ts` that isolate the `picked` CTE plan and verify indexed sorts use the deleted-sort index without a temp B-tree.
Verification: pivot-plan tests 4/4 pass; all loader tests 105/105 pass; `pnpm typecheck`, `pnpm lint:json` (0 diagnostics), `pnpm format`, and `pnpm build` all pass. Added an `emdash` changeset.
Mode: work
View live dashboard · Run:
ce45cf65-ead9-4d19-951a-ad9b4e1e11a6emdashbot commentedon Sep 23, 2026
A candidate change is ready to preview.
Fixed the D1 taxonomy-filtered listing nested-loop regression (#3299) by changing the SQLite
pivotContentJoininbuildTaxonomyPivotQueryfromCROSS JOINto a plainJOIN. This lets the stats-blind SQLite/D1 planner drive from the content table's(deleted_at, published_at/created_at DESC, id DESC)index and probe the pivot by primary key, restoring early-LIMIT and avoiding the full nested loop introduced by migration 068. TheCROSS JOINinfoldedHydrationSelectsis unchanged (guards a different byline-join hazard, #1722).Added focused regression assertions to
loader-taxonomy-pivot-plan.test.tsthat isolate thepickedCTE plan and verify indexed sorts use the deleted-sort index without a temp B-tree.Verification: pivot-plan tests 4/4 pass; all loader tests 105/105 pass;
pnpm typecheck,pnpm lint:json(0 diagnostics),pnpm format, andpnpm buildall pass. Added anemdashchangeset.Try the change against your own site:
Or try the candidate in a ready-to-use playground:
Open the playground preview
@merrickma could you try this? Reply naturally to say whether it works or explain what still needs to change.
A maintainer can accept on the reporter's behalf. Acceptance opens a draft PR; requested changes start another candidate revision.
Fix branch:
bot/fix-3299· Artifacts branch:bot/artifacts-3299fix(loader): restore early LIMIT for taxonomy listings on D1 (#3299) (#…