High Performance PostgreSQL for Rails

Finding Missing Indexes

Missing indexes are a class of performance problem where sequential table scans are used because supporting indexes don’t exist.

For tables with row counts in the millions, particularly when data is scattered on various pages and the table has a high amount of bloat, scanning the whole table to then filter down to a few rows is very inefficient.

To identify opportunities to add indexes, try running a query like find_missing_indexes.sql^[172] on your database. This query shows counts of sequential scans and index scans for a given table.

When a table has a lot more sequential scans than index scans, the table might be missing indexes. From there, you’ll want to then drill into the queries for data in that table by using ...