Index Performance

Creating indexes is only half the battle. To truly optimize your database performance, you need to understand how PostgreSQL uses indexes and how to make them work better. This lesson covers practical techniques for improving index performance.

Understanding EXPLAIN

The EXPLAIN command shows you how PostgreSQL executes a query:

EXPLAIN SELECT * FROM students WHERE email = 'alice@example.com';

Look for key indicators:

Seq Scan — Sequential scan. PostgreSQL is reading the entire table. Bad for large tables.
Index Scan — Using an index. This is what you want.
Bitmap Index Scan — Using multiple index entries. Good for complex conditions.

For more detailed information including actual execution times:

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM students WHERE email = 'alice@example.com';

Covering Indexes

A covering index includes all the columns needed by a query, so PostgreSQL never has to look up the actual table rows:

CREATE INDEX idx_students_email_name ON students (email)
INCLUDE (first_name, last_name);

Now this query can be answered entirely from the index:

SELECT first_name, last_name FROM students WHERE email = 'alice@example.com';

This is called an "index-only scan" and is extremely fast because everything is read from the index without touching the table.

Index Selectivity

Not all indexes are equally useful. Selectivity measures how many distinct values a column has compared to the total number of rows:

High selectivity — Many unique values (like email, UUID). Excellent index candidates.
Low selectivity — Few unique values (like boolean, status with 3 options). Indexes help less.

A column with 1 million unique values out of 1 million rows is perfectly selective. A column with only 2 possible values (like is_active) is not very selective.

Index Maintenance

Indexes can become bloated over time. Reindexing cleans them up:

-- Reindex a specific index
REINDEX INDEX idx_students_email;

-- Reindex all indexes on a table
REINDEX TABLE students;

-- Reindex the entire database
REINDEX DATABASE my_database;

For production databases, consider using REINDEX CONCURRENTLY (available in PostgreSQL 12+) to avoid locking the table during reindexing.

Monitoring Index Usage

PostgreSQL tracks how often indexes are used:

SELECT
  indexrelname AS index_name,
  idx_scan AS times_used,
  idx_tup_read AS tuples_read,
  idx_tup_fetch AS tuples_fetched
FROM pg_stat_user_indexes
ORDER BY idx_scan DESC;

If an index has zero scans, it is unused. Consider dropping it to save space and improve write performance.

Index Strategies

Some practical strategies for real-world applications:

Index foreign key columns — They are frequently used in JOINs
Index columns used in WHERE, ORDER BY, and GROUP BY
Use composite indexes for queries that filter on multiple columns
Use partial indexes when you only query a subset of rows
Drop unused indexes — they slow down writes
Monitor pg_stat_user_indexes regularly