?? Why My SQL Query Wasn’t Using an Index (and What I Learned)

Recently, I encountered an issue where I expected my SQL query to use an index scan, but instead, it was performing a sequential scan (Seq Scan). Naturally, this was frustrating because I knew my query was filtering on an indexed column. When I ran the EXPLAIN command to analyze the execution plan, I was surprised to see the optimizer completely ignoring my index, opting instead for a full table scan. This led me down a rabbit hole of learning, and here’s what I discovered about why this can happen and what you can do about it.

???♂? The Problem: Sequential Scan Instead of Index Scan

When I first wrote my query, it looked something like this:

SELECT * FROM my_table WHERE indexed_column = 'value';

Since I had created an index on indexed_column, I was expecting the query to be fast and efficient, leveraging an index scan to find the relevant rows. However, after running EXPLAIN, it was clear that the database was doing a full Seq Scan instead:

Seq Scan on my_table (cost=0.00..431.00 rows=1 width=100)

This raised the question: Why wasn’t my query using the index? Here’s what I learned while investigating.

1. ??? Small Table Size: Indexes Aren’t Always Necessary

One of the first things I discovered is that table size plays a major role in whether an index scan is used. If your table is relatively small, the database might find it faster to just scan the entire table rather than going through the overhead of using an index.

??Indexes are most useful on larger datasets. For small tables, the database engine often decides that a full table scan is faster, and in many cases, it’s right. If your table has only a few thousand rows, sequential scans can be quicker than indexed lookups because there’s no need to jump around the table.

2. ?? Index Selectivity: Not All Indexes Are Created Equal

Next, I learned about index selectivity, which refers to how many unique values exist in the indexed column. If the column has very few unique values (e.g., a "status" column with values like "active" and "inactive"), the database may decide that using the index won’t filter out enough rows to justify its use.

??For an index to be useful, it needs to significantly reduce the number of rows the database has to scan. If a large percentage of rows match the indexed column, the database might prefer a sequential scan. High cardinality (lots of unique values) increases the likelihood of index use.

3. ?? Outdated Statistics: The Optimizer’s Blind Spot

Another eye-opener was the importance of statistics. The database uses statistics to estimate how many rows a query will return, and how much data needs to be read. If the statistics are outdated, the optimizer might make bad decisions, like choosing a sequential scan over an index scan.

??Keeping table and index statistics up-to-date is critical for query optimization. In PostgreSQL, you can run ANALYZE to refresh the statistics:

ANALYZE my_table;

In MySQL, you can use:

ANALYZE TABLE my_table;

This helps the database make better decisions about whether to use an index or not.

4. ??? Poor Index Design: The Index Might Not Fit the Query

Even though I was filtering on an indexed column, I realized that how I designed the query could affect whether the index is used. If I had used a function on the indexed column (e.g., LOWER(indexed_column) = 'value'), or if I had written a complex query with an OR condition, the database might not be able to use the index.

??Index usage can be affected by query design. Avoid using functions on indexed columns, and simplify the query where possible. If you need complex expressions, consider creating functional indexes that match the query pattern. If you use multiple columns in the filter, ensure they are in a composite index.

5. ?? Too Many Matching Records: Sequential Scan May Be More Efficient

I also learned that if the indexed condition returns a large portion of the table’s rows (let’s say more than 30%), the optimizer will likely use a Seq Scan because reading the entire table might be faster than using an index.

??Indexes work best when they filter out large portions of the table. If the condition matches many rows, the optimizer might prefer to read the entire table sequentially rather than perform repeated index lookups. This is especially true for queries where the indexed column has low selectivity.

6. ?? Query Structure: How You Write the Query Matters

Interestingly, the way the query is written can also influence whether an index is used. For example, I found out that using an OR condition between two indexed columns could force a sequential scan instead of an index scan.

??Query structure plays a huge role in whether an index is used. Using OR conditions, complex subqueries, or unnecessary joins can prevent the database from using indexes. Sometimes, rewriting the query by breaking it into UNION statements or simplifying joins can help.

7. ?? Database Configuration: Tuning Parameters Matter

Another factor is the database’s configuration settings. For example, in PostgreSQL, parameters like random_page_cost and seq_page_cost influence whether the optimizer favors sequential scans or index scans. In my case, adjusting these values slightly changed the behavior of my queries.

??Database configuration plays a role in query optimization. While most of the time the defaults are good enough, tuning certain parameters can push the optimizer to use indexes more often. For example, lowering random_page_cost makes the optimizer more likely to use indexes.

?? Conclusion: The Art of Query Optimization

This experience taught me that query optimization is more than just throwing an index at a problem. It requires understanding how the database optimizer works and knowing when and why it might ignore an index. By analyzing table size, index selectivity, query structure, and database configuration, I was able to improve my query performance and ensure that indexes are used appropriately.

?? Pro Tips:

?? Check if the table is small and doesn't need an index.

?? Ensure the column has high selectivity.

?? Update table and index statistics.

??? Review your query structure to make sure it aligns with the index.

?? Tune your database configuration if necessary.

Ultimately, by understanding how the database makes its decisions, you can optimize your queries to run more efficiently!