PostgreSQL Query Optimization Tips

A grab-bag of practical habits that keep PostgreSQL queries fast as your data grows.

Index Columns You Actually Filter, Join, or Sort By

-- if this WHERE clause runs constantly, it needs an index:
SELECT * FROM orders WHERE customer_id = 42;

CREATE INDEX idx_orders_customer ON orders (customer_id);

Foreign key columns are especially easy to forget — Postgres does not automatically index them, unlike the primary key side of the relationship.

Avoid SELECT * in Application Code

-- fetches every column, even ones your app never uses:
SELECT * FROM employees;

-- fetches only what's needed — less data over the wire, less work for Postgres:
SELECT id, name, email FROM employees;

Prefer EXISTS Over IN for Large Subqueries

-- Postgres must potentially materialize the entire subquery result:
SELECT * FROM departments d
WHERE d.id IN (SELECT department_id FROM employees);

-- can often stop as soon as ONE match is found:
SELECT * FROM departments d
WHERE EXISTS (SELECT 1 FROM employees e WHERE e.department_id = d.id);

Keep Table Statistics Fresh

The query planner relies on statistics about your data's distribution to choose a good plan — stale statistics lead to bad plans.

ANALYZE employees;

autovacuum normally handles this automatically, but running it manually after a huge bulk load is good practice.

Use LIMIT When You Don't Need Every Row

SELECT * FROM logs ORDER BY created_at DESC LIMIT 100;
-- instead of pulling millions of rows into your app just to show the latest 100

Avoid Functions on Indexed Columns in WHERE

-- can't use a plain index on created_at, since the column is wrapped in a function:
SELECT * FROM orders WHERE DATE(created_at) = '2024-06-01';

-- rewrite as a range so the index CAN be used:
SELECT * FROM orders
WHERE created_at >= '2024-06-01' AND created_at < '2024-06-02';

Use Connection Pooling

Each PostgreSQL connection has real memory/CPU overhead. For apps that open many short-lived connections, a pooler like PgBouncer (or your framework's built-in pool) reuses connections instead of constantly opening new ones.

Common Errors & Troubleshooting

• Adding an index and seeing no improvement — verify with EXPLAIN ANALYZE (previous lesson) that the planner is actually choosing to use it; sometimes wrapping the column in a function, or comparing incompatible types, silently prevents index use even after you've added one.
• Optimizing a query that isn't actually the slow one — profile first (check pg_stat_statements or application-level timing) before spending time optimizing a query that turns out not to be the real bottleneck.
• Over-indexing "just in case" — every extra index slows down writes and costs disk space; only add indexes backed by an actual slow query you've measured, not speculative ones.

Key Takeaways

• Foreign key columns aren't indexed automatically — add that index yourself if you join or filter on them often.
• Wrapping an indexed column in a function (like DATE(created_at)) usually defeats the index — rewrite as a range instead.
• EXPLAIN ANALYZE (from the previous lesson) is how you verify whether any of these changes actually helped.