1. Quick Verdict Poor. The biggest issue is EXTRACT(YEAR FROM created_at), which forces PostgreSQL to scan all 40M orders and compute the function on every row, then sort the survivors. 2. Bottleneck Analysis # Problem Where Why it’s slow Impact 1 Non-sargable predicate EXTRACT(YEAR FROM created_at) = 2025 The function wraps the column, so no index on created_at could be used even if one existed. The engine reads every row and evaluates the function 40M times. High 2 Missing index on filter/sort column ORDER BY created_at DESC Without an index, matching rows must be sorted in memory or spilled to disk. High 3 SELECT * SELECT * Pulls wide columns like notes through the sort and over the network, and prevents index-only scans. Medium 4 IN subquery customer_id IN (...) Usually planned as a semi-join (fine), but EXISTS states the intent more clearly and plans more predictably. Low 3. Optimized Query sql SELECT o.id, o.customer_id, o.created_at, o.total -- CHANGE 3: explicit columns FROM orders o WHERE o.created_at >= '2025-01-01' -- CHANGE 1: sargable range AND o.created_at < '2026-01-01' AND EXISTS ( -- CHANGE 4: EXISTS semi-join SELECT 1 FROM customers c WHERE c.id = o.customer_id AND c.country = 'US') ORDER BY o.created_at DESC; Rows returned are identical to the original; the columns are not (see Risks). 4. Change-by-Change Explanation Before: EXTRACT(YEAR FROM created_at) = 2025 → After: created_at >= '2025-01-01' AND created_at < '2026-01-01'. The half-open range lets the planner do an index range scan instead of calling a function on every row. Before: SELECT * → After: four named columns. Less data moved per row, and the narrow column list makes index-only scans possible. Before: IN (subquery) → After: EXISTS. Equivalent here (customer ids are non-null and unique), and it gives the planner a clean semi-join to work with. 5. Index Recommendations sql CREATE INDEX CONCURRENTLY idx_orders_created_at_cust ON orders (created_at DESC) INCLUDE (customer_id, total); CREATE INDEX CONCURRENTLY idx_customers_country ON customers (country) INCLUDE (id); The first serves the range filter and the ORDER BY with no sort step. INCLUDE columns allow index-only scans (keep the table vacuumed). The second makes the US-customer lookup an index scan. Trade-offs: the first index adds write overhead on a 40M-row table and a few GB of storage. CONCURRENTLY avoids blocking writes during the build. 6. Expected Improvement Sequential scan with per-row function evaluation and a sort → index range scan in sorted order. Assumption: the improvement depends on what fraction of orders fall in 2025. If it is under ~10-15% of the table, expect a large gain. If it is a majority, the planner may still choose a scan and the gain will be modest. 7. Verification Steps Run EXPLAIN (ANALYZE, BUFFERS) on the old and new query. Compare execution time, Buffers: shared hit/read, and rows removed by filter. Look for Index Only Scan or Index Scan replacing Seq Scan, and the absence of Sort. 8. Trade-offs, Risks & Alternatives Risk: replacing SELECT * changes the returned columns. Confirm the application doesn’t depend on the others. Risk: year boundaries follow the session time zone. Set it explicitly if your app and DB differ. Alternative: if the dashboard only needs aggregates, a materialized view refreshed periodically beats any index. Alternative: range-partition orders by month if the table keeps growing.