SQLite Query Planner Not Utilizing Covering Indices Efficiently

Understanding SQLite’s Index Selection Behavior

SQLite’s query planner is designed to optimize query performance by selecting the most efficient index for a given query. However, there are scenarios where the planner may not choose the expected index, even when a covering index is available. This behavior can be perplexing, especially when the covering index appears to be the optimal choice. To understand why this happens, we need to delve into how SQLite’s query planner evaluates and selects indices.

The query planner in SQLite uses a cost-based approach to determine which index to use. It estimates the cost of using each available index and selects the one with the lowest estimated cost. This estimation is based on factors such as the number of rows that need to be scanned, the size of the index, and the complexity of the query. However, the planner’s cost estimation is not always perfect, and it may sometimes favor an index that is not the most efficient for a particular query.

In the provided example, the query planner chooses the primary key index over the covering index, even though the covering index appears to be more efficient. This is because the primary key index is unique and guarantees a single row lookup, which the planner estimates to be faster than using a non-unique covering index. However, this estimation may not always be accurate, especially when the covering index can eliminate the need for additional row lookups and expression evaluations.

Why SQLite May Ignore Expression-Based Covering Indices

One of the key issues in the provided discussion is the use of expression-based covering indices. SQLite’s query planner is not currently capable of fully leveraging expression-based indices, particularly when the expression involves a boolean comparison. This limitation can lead to suboptimal query plans, where the planner ignores the covering index and instead performs a row lookup followed by an expression evaluation.

In the example, the index test_index_1 includes an expression value2 = 'test_value'. Despite this expression being part of the index, the query planner does not recognize it as a valid covering index for the query. This is because the planner does not understand that the expression in the index can be used to satisfy the query’s condition. As a result, it defaults to using the primary key index, which it estimates to be faster.

To work around this limitation, the query can be rewritten to make the expression an equality constraint rather than a bare boolean value. This forces the query planner to recognize the expression as part of the index and use it accordingly. For example, the query can be rewritten as follows:

SELECT id
FROM test
WHERE id = 'abc'
  AND value1 = 42
  AND (value2 = 'test_value') = TRUE;

This modification changes the expression from a boolean comparison to an equality constraint, which the query planner can better understand and utilize. By doing so, the planner is more likely to choose the covering index, resulting in improved query performance.

Optimizing Query Performance with Covering Indices in Complex Schemas

In more complex schemas, such as the one involving person, company, and personCompany tables, the query planner’s behavior can become even more nuanced. The presence of multiple tables, subqueries, and joins can complicate the planner’s decision-making process, leading to suboptimal index selection.

In the provided example, the query involves a subquery that checks the profitability of a company. The covering index companyProfitableCovering is designed to avoid row lookups by including the profitable column in the index. However, the query planner does not automatically choose this index, even when it is available. Instead, it defaults to using the primary key index, which requires additional row lookups and expression evaluations.

To address this issue, the query can be modified to explicitly use the covering index with the INDEXED BY clause. This forces the query planner to use the specified index, bypassing its cost estimation process. For example:

SELECT id, (
  SELECT COUNT(*) FROM personCompany
  WHERE personID = person.id
    AND (
      SELECT profitable
      FROM company INDEXED BY companyProfitableCovering
      WHERE id = personCompany.companyID
    )
) FROM person;

By using the INDEXED BY clause, the query planner is forced to use the companyProfitableCovering index, which can significantly improve query performance. However, this approach should be used judiciously, as it overrides the planner’s decision-making process and may not always result in the most efficient query plan.

In cases where the schema is more complex, with multiple columns and larger row sizes, the performance benefits of using covering indices can be even more pronounced. However, the query planner’s limitations in handling expression-based indices and its reliance on cost estimation can still lead to suboptimal index selection. In such scenarios, it is essential to carefully analyze the query plans and consider alternative approaches, such as rewriting the query or using WITHOUT ROWID tables, to achieve the desired performance improvements.

Conclusion

SQLite’s query planner is a powerful tool for optimizing query performance, but it has limitations that can lead to suboptimal index selection, particularly when dealing with expression-based covering indices. By understanding the planner’s behavior and its cost estimation process, developers can take steps to ensure that the most efficient index is used for each query. This may involve rewriting queries to make expressions more explicit, using the INDEXED BY clause to force the use of specific indices, or considering alternative schema designs such as WITHOUT ROWID tables. With careful analysis and optimization, it is possible to achieve significant performance improvements in SQLite databases, even in complex schemas with large datasets.