How do indexes affect SQL query performance?

Instruction: Explain when an index helps, what it costs, and how you would verify its effect.

Context: Learn to discuss database indexes, query plans, and the trade-off between read speed and write cost.

Updated

Example Answer

An index helps the database find relevant rows without necessarily scanning the whole table. It can speed up filters, joins, and ordering, but it takes storage and adds work to inserts, updates, and deletes. I choose indexes around actual query patterns, then check the query plan and measure the result.

Small example

Assume an orders table with customer_id and created_at:

CREATE INDEX orders_customer_created_idx
ON orders (customer_id, created_at);

SELECT customer_id, created_at
FROM orders
WHERE customer_id = 42
ORDER BY created_at;

This composite index is a candidate for the query, not a guarantee of faster execution. A planner may prefer a scan on a small table or when many rows match. Review existing indexes before adding another, and use representative data when measuring.

Reference

PostgreSQL: indexes

Related Questions