SQL Indexes and EXPLAIN

A query that feels instant on 500 rows can crawl on 50 million. The usual reason is that the database reads every single row to find the few you asked for. An index fixes this, and EXPLAIN lets you see whether it is working.

What an Index Does

Think of the index at the back of a textbook. Instead of reading every page to find a topic, you look it up and jump straight to the right page. A database index works the same way. It keeps a sorted lookup structure for one or more columns, so the database can find matching rows without scanning the whole table.

PostgreSQL creates an index for you on every primary key and UNIQUE column. You have to add everything else yourself.

Reading a Query Plan with EXPLAIN

Put EXPLAIN in front of any query, and PostgreSQL shows the plan it would use, without running the query. Here is an orders table with 100,000 rows, searched by customer.

EXPLAIN
SELECT * FROM orders WHERE customer_id = 4821;

Output (sample):

Seq Scan on orders  (cost=0.00..2084.00 rows=1 width=40)
  Filter: (customer_id = 4821)

Seq Scan means a sequential scan. The database is reading the entire table, row by row, just to find a handful of matches. Now add an index on that column and ask again.

CREATE INDEX idx_orders_customer ON orders (customer_id);

EXPLAIN
SELECT * FROM orders WHERE customer_id = 4821;

Output (sample):

Index Scan using idx_orders_customer on orders  (cost=0.42..8.44 rows=1 width=40)
  Index Cond: (customer_id = 4821)

Code Explanation:

• CREATE INDEX builds the lookup structure on customer_id and gives it the name idx_orders_customer.
• The plan changes from Seq Scan to Index Scan, so the database now jumps straight to the matching rows.
• cost is an internal estimate of the work involved, and it dropped from about 2084 to about 8. The numbers in your own plans will differ, but the pattern is what matters.
• rows is the estimated number of matching rows, and width is the estimated size of each row in bytes.

EXPLAIN ANALYZE goes one step further. It actually runs the query and adds real timings, which is more accurate but also means a DELETE or UPDATE really happens. In MySQL, EXPLAIN works too, but the output is laid out as a table of columns instead.

When Indexes Help and When They Hurt

Indexes are not free. They take disk space, and every INSERT, UPDATE and DELETE has to update them too.

• Good candidates: columns used often in WHERE, in JOIN ... ON, or in ORDER BY, especially on large tables.
• Poor candidates: tiny tables, columns with only a few distinct values (like a yes/no flag), and tables that are written to far more often than they are read.
• Composite indexes: an index on two columns, such as (customer_id, order_date), helps queries that filter on both, or on the first one alone.

To remove an index you no longer need, use DROP INDEX idx_orders_customer;.