Why Does Your PostgreSQL Query Suck?

I've written slow queries. You've written slow queries. We all pretend EXPLAIN ANALYZE is fine and then close the tab. But at some point you hit a table with 5 million rows and suddenly your app feels like it's running on a potato.

Indexes are the answer. The problem is most people add them randomly, hope for the best, and then wonder why nothing changed.

The Problem ( You Already Know It )

Sequential scans. PostgreSQL reads every single row in the table to find what you need. On a table with 10,000 rows you don't notice. On a table with 5 million rows your endpoint times out.

Here's what a slow query looks like in the wild:

EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 42;

-- Seq Scan on orders  (cost=0.00..154321.00 rows=5000000 width=72)
--   Filter: (user_id = 42)
-- Planning Time: 0.123 ms
-- Execution Time: 847.321 ms

847 milliseconds for a single lookup. That's not a database, that's a punishment.

B-Tree Is Your Default for a Reason

PostgreSQL creates B-tree indexes by default when you run CREATE INDEX. Good. B-trees handle equality, range, and ORDER BY queries. That covers 90% of what you need.

The basic index:

-- Create an index
CREATE INDEX idx_orders_user_id ON orders (user_id);

-- Now check the same query
EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 42;

-- Index Scan using idx_orders_user_id (cost=0.42..8.44 rows=1 width=72)
--   Index Cond: (user_id = 42)
-- Execution Time: 0.038 ms

From 847ms to 0.038ms. That's a 22,000x improvement from one line of SQL.

Composite Indexes ( Stop Adding Five Single-Column Ones )

I see this all the time. Someone queries by user_id AND status and creates two separate indexes. PostgreSQL can only use one per table access ( mostly ), so it picks the better one and filters the rest.

Use a composite index instead:

-- Bad: two separate indexes
CREATE INDEX idx_orders_user ON orders (user_id);
CREATE INDEX idx_orders_status ON orders (status);

-- Good: one composite index
CREATE INDEX idx_orders_user_status ON orders (user_id, status);

-- This also covers queries filtering only by user_id
-- Because B-tree is left-to-right, user_id alone still uses the index

Column order matters. Put the column you filter by most often first. If you always filter by user_id and sometimes by status too, user_id goes first.

When NOT to Index

Not every column needs one. Indexes slow down writes. Every INSERT, UPDATE, and DELETE has to update every index on that table. Add 10 indexes and your writes crawl.

Rules I actually follow:

- Don't index columns with low cardinality ( boolean, gender, status with 3 values )

- Don't index tiny tables ( under a few thousand rows, Seq Scan is faster than Index Scan )

- Don't index columns you never filter or sort by

- Drop unused indexes. Query pg_stat_user_indexes:

SELECT schemaname, relname AS table, indexrelname AS index,
       idx_scan AS index_scans
FROM pg_stat_user_indexes
ORDER BY idx_scan ASC;

Any index with idx_scan = 0 or close to it is dead weight. Drop it.

Partial Indexes ( The Trick Nobody Uses )

You don't always need the whole column indexed. If you only ever query active orders, index just those:

-- Instead of indexing all rows
CREATE INDEX idx_orders_active ON orders (user_id)
WHERE status = 'active';

-- This index is smaller, faster to scan, and faster to update
-- PostgreSQL skips it entirely for queries that don't match the condition

I use partial indexes for soft-deleted rows ( WHERE deleted_at IS NULL ), pending states, and anything where 90% of queries hit 10% of the data. The index shrinks, the planner gets faster, and writes are cheaper.

Covering Indexes ( Index-Only Scans )

If your query only needs columns that are in the index, PostgreSQL can skip the table lookup entirely. This is an index-only scan.

-- Include extra columns so the index covers the whole query
CREATE INDEX idx_orders_covering ON orders (user_id, status)
INCLUDE (created_at, total);

-- This query never touches the table:
SELECT status, created_at, total
FROM orders
WHERE user_id = 42;

-- Index-Only Scan using idx_orders_covering
-- Execution Time: 0.012 ms

The INCLUDE clause adds columns to the index without making them part of the sort key. You get the data from the index alone. The table is never touched.

3 Indexing Mistakes I've Made

1. Adding an index and not running ANALYZE. PostgreSQL's planner needs table statistics. After big data changes, run ANALYZE manually or wait for autovacuum.

2. Using functions in WHERE clauses that break indexing. WHERE LOWER(email) = 'x' can't use a standard B-tree index on email. Use an expression index instead:

CREATE INDEX idx_users_email_lower ON users (LOWER(email));

3. Forgetting about UNIQUE constraints. They create indexes automatically. Don't double-index a column that already has a UNIQUE constraint.

Conclusion

PostgreSQL indexes are not complicated. Create them for the columns you filter and sort by. Use composites when you filter by multiple columns together. Use partial indexes when you only care about a subset. Check pg_stat_user_indexes to find the dead ones.

And for the love of everything, run EXPLAIN ANALYZE before and after. Numbers don't lie. Your intuition about what's slow does.

:)