Why is my PostgreSQL query still slow after adding an index?

Either Postgres chose not to use the index (check EXPLAIN ANALYZE — if you see Seq Scan, it didn't), the index doesn't match the query (wrong column order, wrong type), or the table is small enough that a sequential scan is faster. Indexes on tables under a few thousand rows often go unused.

What is EXPLAIN ANALYZE in PostgreSQL?

EXPLAIN ANALYZE runs the query and shows the actual execution plan with real timing. You see whether Postgres used the index, how many rows it scanned, and where the time was spent. Always read this before and after adding an index.

When should I use a composite index in PostgreSQL?

When you frequently query multiple columns together (WHERE status = 'active' AND created_at > '2026-01-01'). A composite index on (status, created_at) handles both conditions in one index scan. Single-column indexes on each would require Postgres to scan both and combine results.

Do indexes slow down writes in PostgreSQL?

Yes. Every insert, update, and delete has to update every index on the table. A table with 10 indexes can be 5x slower to write to than a table with 2. Measure write performance before and after adding indexes, and drop the ones you don't use.

PostgreSQL Indexes That Actually Speed Up Your Queries (Most Don't)

80% of indexes I see in production databases are useless or actively harmful. They were added because a query was slow, the developer guessed at a fix, the query got faster (or didn't), and nobody validated. The index sat there forever, slowing down every write.

PostgreSQL indexes are not free. Every insert, update, and delete has to update every index. A table with 10 indexes writes 11 rows on every insert. That's the trade — read speed for write speed.

Here's how I actually do it.

The single most important tool: EXPLAIN ANALYZE

Not EXPLAIN. EXPLAIN ANALYZE. The difference matters.

EXPLAIN ANALYZE
SELECT * FROM orders WHERE user_id = '123';