Query Optimization
Query optimization is the practice of rewriting queries and schema so that PostgreSQL's planner can pick a cheap execution path instead of an expensive one. Most real-world slowdowns come from a handful of recurring patterns — this page walks through the ones worth checking first.
Technique | Why it helps |
|---|---|
Add indexes on filter/join/order columns | Without an index, PostgreSQL must scan every row of a table to find matches. See <a href="/postgresql/indexes">Indexes Overview</a> and <a href="/postgresql/index-types">Index Types</a>. |
Avoid wrapping indexed columns in functions |
|
Prefer EXISTS over IN for large subqueries | EXISTS can stop as soon as it finds one matching row; a large IN (subquery) may materialize the full subquery result first. The planner sometimes optimizes both the same way, but EXISTS is the safer default for large or correlated subqueries. |
Avoid SELECT * in production code | Fetching columns you do not need wastes I/O and network bandwidth, and can prevent an Index Only Scan that would otherwise satisfy the query from the index alone. |
Batch large INSERT/UPDATE operations | A single statement touching millions of rows holds locks longer, generates a burst of WAL, and bloats tables faster than the same work done in smaller batches with brief pauses between them. |
Use LIMIT with an indexed ORDER BY for "top N" queries | ORDER BY created_at DESC LIMIT 10 on an indexed column lets PostgreSQL read the index in order and stop after 10 rows, instead of sorting the entire table. |
Function-wrapped columns: a closer look
This is one of the most common ways an index silently goes unused. Compare these two queries against a table with a plain index on email:
Index is unusable
-- Index on email exists, but this cannot use it: SELECT * FROM users WHERE lower(email) = 'jane@example.com';
Fix: create a matching expression index
CREATE INDEX idx_users_email_lower ON users (lower(email)); -- Now this uses idx_users_email_lower: SELECT * FROM users WHERE lower(email) = 'jane@example.com';
EXISTS vs. IN
Same result, different execution characteristics
-- IN materializes the full subquery result before comparing SELECT * FROM customers c WHERE c.id IN (SELECT customer_id FROM orders WHERE total > 1000); -- EXISTS can stop at the first match per outer row SELECT * FROM customers c WHERE EXISTS ( SELECT 1 FROM orders o WHERE o.customer_id = c.id AND o.total > 1000 );
Efficient "top N" with LIMIT
CREATE INDEX idx_orders_created_at ON orders (created_at DESC); -- Reads the index in order and stops after 10 rows — -- no full sort of the table required. SELECT * FROM orders ORDER BY created_at DESC LIMIT 10;