In this lesson, you will learn how to evaluate whether your database indexes are actually helping your query performance or if they are being ignored by the optimizer due to poor selectivity.
Beyond the Index: Understanding Selectivity
Just because you have an index on a column does not mean the query optimizer will use it. The primary factor determining whether an engine performs an Index Seek (efficiently pinpointing specific rows) versus an Index Scan (reading through the entire index structure) is selectivity.
Selectivity refers to the fraction of rows returned by a predicate. If a filter returns 0.01% of a table, that is "high selectivity," and the optimizer will likely choose an Index Seek. If that same filter returns 80% of the rows, the optimizer will likely abandon the index and perform a Full Table Scan, as the overhead of jumping back and forth between the index and the data pages is more expensive than just reading the table sequentially.
Analyzing the Trade-off
When you examine your query execution plans, look for these indicators:
- Index Seek: The engine traverses the B-Tree to find exact matches. This is ideal for highly selective filters (e.g.,
WHERE user_id = 550). - Index Scan: The engine reads the entire index. This happens when the optimizer decides it needs too many rows to make a seek efficient, or when your query predicate doesn't align with the leading column of a composite index.
Consider a table with a column is_active. If 95% of your records are active, an index on this column has low selectivity. Querying WHERE is_active = true will almost always result in an Index Scan or a Table Scan because the optimizer knows it has to touch almost every page anyway.
-- High selectivity: Index seek is likely
SELECT * FROM orders WHERE order_uuid = 'a1-b2-c3';
-- Low selectivity: Index scan or table scan is likely
SELECT * FROM orders WHERE status = 'processed';
Before adding an index, calculate the cardinality of the column. If the number of unique values is low, the index will provide minimal performance gain and may actually increase maintenance overhead during data ingestion.
Key Takeaway: Always evaluate index selectivity; an index is only as effective as its ability to drastically reduce the number of rows the engine must process.