Drills: Postgres Performance

It's a Seq Scan reading all 2M rows to keep 40 — see Rows Removed by Filter: 1999960. The filter is highly selective (40 of 2M) but there's no index on document_id, so Postgres reads the whole table. The 312 ms is almost entirely wasted scanning.

CREATE INDEX idx_chunks_document_id ON chunks (document_id);

After this it flips to an Index Scan and drops to a fraction of a millisecond.

The huge gap between estimate (10) and actual (480,000) means the planner's statistics are stale — it picked a nested loop because it expected almost no rows. Refresh the stats:

ANALYZE the_table;

With accurate row estimates the planner will likely switch to a hash join. Stale stats are a top cause of suddenly-bad plans after a big data change.

• (a) 5 rows → Index Scan — very selective, few heap fetches.
• (b) 900K rows → Seq Scan — not selective; reading the whole table once beats millions of random index lookups.
• (c) ~20K rows → Bitmap Heap Scan — the middle ground: build a bitmap from the index, then read the heap in physical order.

The lesson: selectivity drives the choice. An index doesn't help a non-selective filter, and the planner is right to ignore it.

CREATE INDEX idx_docs_owner_created
ON documents (owner_id, created_at DESC);

Equality column (owner_id) first, sort column (created_at DESC) second. The index seeks straight to owner 42's rows already in date order, so Postgres returns the top 20 with no sort step and no extra rows read. Bonus: add INCLUDE (title) for an Index Only Scan that never touches the heap.

-- (a) JSONB containment → GIN
CREATE INDEX ON documents USING GIN (metadata);

-- (b) vector nearest-neighbour → HNSW
CREATE INDEX ON chunks USING hnsw (embedding vector_cosine_ops);

-- (c) function on the column → index the EXPRESSION
CREATE INDEX ON users (lower(email));

(c) is the trap: a plain index on email can't serve lower(email) — you must index the same expression you filter on.

By the leftmost-prefix rule, an index on (chunk_index, document_id) is sorted by chunk_index first — so it can't efficiently filter by document_id alone (that's the second column, only ordered within each chunk_index). Swap the order:

CREATE INDEX idx_chunks_doc_idx
ON chunks (document_id, chunk_index);

Now it seeks to the document, with chunks already ordered by chunk_index — filter and sort both satisfied. Equality column first, always.

from sqlalchemy import select
from sqlalchemy.orm import selectinload

stmt = select(Document).options(selectinload(Document.chunks))
docs = session.scalars(stmt).all()      # 2 queries total
for doc in docs:
    summary[doc.id] = len(doc.chunks)   # no query — already loaded

selectinload is right for a one-to-many (a document's many chunks): the second query is … WHERE document_id IN (…), with no parent-row duplication. Use joinedload instead when loading a single related object (a chunk's one document).

SELECT id, title, created_at FROM documents
WHERE (created_at, id) < (:last_created_at, :last_id)
ORDER BY created_at DESC, id DESC
LIMIT 20;

With an index on (created_at DESC, id DESC), the database seeks straight past the last-seen row — constant time per page, no matter the depth. The id tie-breaker keeps rows with equal timestamps ordered deterministically. Trade-off: next/previous only, no jump-to-page-500.

Step 1 — capture the before plan:

EXPLAIN (ANALYZE, BUFFERS) /* the query above */;
-- you'll see: Seq Scan on chunks (computes distance for every row),
-- Seq Scan on documents (metadata filter), then a Sort. Hundreds of ms.

Step 2 — add the right indexes and refresh stats:

-- HNSW for approximate nearest-neighbour (match the cosine operator)
CREATE INDEX idx_chunks_embedding
ON chunks USING hnsw (embedding vector_cosine_ops);

-- GIN for the JSONB containment filter
CREATE INDEX idx_docs_metadata
ON documents USING GIN (metadata);

ANALYZE chunks;
ANALYZE documents;

Step 3 — capture the after plan:

EXPLAIN (ANALYZE, BUFFERS) /* the same query */;
-- now: Index Scan using idx_chunks_embedding (HNSW, no full sort),
-- Bitmap Index Scan on idx_docs_metadata. Single-digit ms.
-- tune recall vs speed: SET hnsw.ef_search = 100;

The before/after pair — a Seq Scan + Sort at hundreds of ms becoming an HNSW Index Scan at single-digit ms — is the whole story. That comparison is what you show to prove the optimization worked.