Incrm - Angular Admin Template & Starter

AI Chat

Today

Zoneless change detection migration Pricing page copy for the Team plan

Yesterday

Postgres index not being used Q3 board update outline

Previous 7 days

Dockerfile build cache is thrashing Rewriting the onboarding email sequence Explaining OAuth PKCE to the team Hiring plan for the platform team

Postgres index not being used

I added a btree index on orders(customer_id, created_at) but the planner still does a seq scan. Why?

A seq scan on a table that fits comfortably in cache is often the correct plan, so the first question is whether the planner is wrong at all. Run the query with timing and buffers before assuming it is.

sql

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, total
FROM orders
WHERE customer_id = 4821
  AND created_at >= now() - interval '30 days';

If the estimated row count is wildly off from the actual, the statistics are stale and ANALYZE orders will fix more than any index will. If the estimate is accurate and the planner still declines the index, it usually means the predicate is not sargable, most often because created_at is wrapped in a cast or a function on the left-hand side.

Estimates look fine and there is no cast. Table is about 40 million rows.

At that size the likely culprit is the cost of the heap fetches. The index gives you the rows, but the planner still expects a random read per row to get total, and past roughly 5 to 10 percent selectivity a sequential scan wins that arithmetic.

Adding the selected columns to the index turns it into an index-only scan and removes the heap trips entirely.

sql

CREATE INDEX CONCURRENTLY orders_customer_recent_idx
  ON orders (customer_id, created_at)
  INCLUDE (id, total);

Use CONCURRENTLY so the build does not take a write lock, and be aware it is slower and can leave an invalid index behind if it fails. Check indisvalid afterwards.

AI can make mistakes. Check important info.