A content list filtered by an explicit id list still gets ORDER BY, and SQLite answers it with a table scan

What happens

ContentRepository.findMany applies its ordering unconditionally:

if (indexedOrderFilter?.kind !== "null") {
  query = query.orderBy(dbField as any, safeOrderDirection === "ASC" ? "asc" : "desc");
}
query = query.orderBy("id", safeOrderDirection === "ASC" ? "asc" : "desc").limit(limit + 1);

That is right for a page of a collection. It is wrong when the caller has already named the rows it wants with an in filter on id, because SQLite then has a choice between an index that satisfies the IN and one that satisfies the ORDER BY, and it picks the second:

SELECT * FROM "ec_listings"
WHERE deleted_at IS NULL AND "status" = ? AND "id" IN (?, ?, ...18 values)
ORDER BY "created_at" DESC, "id" DESC LIMIT ?

SEARCH ec_listings USING INDEX idx_ec_listings_deleted_created_id (deleted_at=?)

It walks the whole collection in created_at order looking for the 18 ids. On our production site that is 9,437 rows read to return 18 rows, about 280 times an hour.

Why a consumer-side index cannot fix it

Worth stating, because it is the first thing anyone will try. We created (deleted_at, status, id, created_at DESC) on a copy, ran ANALYZE, and the planner still chose the scan: with LIMIT 18 its cost model believes the ordered scan will stop early, and it prefers that to sorting 18 rows. The fix has to be in the query, not in the schema.

Suggested fix

When the filter set pins id to an explicit list, drop the ORDER BY (and the LIMIT, which is then also redundant), or make it conditional. A caller that asked for specific ids has its own ordering already: ours re-sorts the result through a position map immediately after, and we would guess most such callers do.

This is a small change, but it needs care about which callers rely on the current ordering, which is why this is an issue rather than a PR. Happy to write the PR if you would like it, with a test asserting the plan contains no full scan for the id-list case; we are at the six open non-draft PR limit, so it would come after one of ours merges.

Environment

• EmDash 0.38.0 on Cloudflare Workers with D1; code above is current main
• Collection of 9,676 entries
• Measured against production D1 on September 17, 2026, by my colleague working on query performance; I confirmed the unconditional ORDER BY in packages/core/src/database/repositories/content.ts on main. Ask if you want any figure re-run, we have the commands and can reproduce on request.

Screenshots

Not applicable.