Query Planner Inefficiency After PRAGMA OPTIMIZE in SQLite

Query Planner Chooses Suboptimal Plan Post-PRAGMA OPTIMIZE

Issue Overview

The core issue revolves around a significant performance regression observed in a specific SQLite query after executing PRAGMA OPTIMIZE=0x10002. The query, which initially performs efficiently, degrades in performance post-optimization, leading to a substantial increase in execution time. This regression is particularly concerning because PRAGMA OPTIMIZE is designed to enhance query performance by updating the internal statistics used by the query planner to make informed decisions about the most efficient execution plan.

The schema in question involves three tables: Note, Tag, and RelationshipNoteTag. The Note table contains a primary key id and a deleted column, while the Tag table has a primary key id. The RelationshipNoteTag table establishes a many-to-many relationship between Note and Tag via NoteID and TagID columns. Indexes are created on the deleted column of the Note table and on the TagID and NoteID columns of the RelationshipNoteTag table.

The problematic query is a correlated subquery that counts the number of non-deleted notes associated with each tag. Before running PRAGMA OPTIMIZE, the query planner chooses an efficient plan that leverages the indexes on RelationshipNoteTag and Note. However, after optimization, the planner switches to a less efficient plan that results in a significant increase in the number of rows scanned, particularly in the Note table.

Possible Causes

The root cause of this performance regression lies in the interaction between the query planner’s decision-making process and the statistics updated by PRAGMA OPTIMIZE. The query planner relies on these statistics to estimate the cost of different execution plans. When these statistics are updated, the planner may choose a different plan based on its revised cost estimates. In this case, the updated statistics appear to mislead the planner into selecting a plan that is less efficient for the given query.

One potential factor contributing to this issue is the distribution of data in the Note table, particularly the deleted column. The query planner’s decision to use the Note_deleted index post-optimization suggests that the statistics may have led it to overestimate the selectivity of the deleted IS NULL condition. This overestimation could be due to the way SQLite calculates selectivity based on the updated statistics, leading the planner to believe that scanning the Note table using the Note_deleted index is more efficient than using the primary key index.

Another contributing factor could be the nature of the id column in the Note table, which is a UUID stored as a text string. SQLite’s handling of text-based primary keys can sometimes lead to suboptimal query plans, especially when compared to integer-based keys. The length and complexity of UUIDs may influence the planner’s cost calculations, potentially leading to less efficient plans.

Additionally, the lack of a unique constraint on the NoteTagsRelationship_Note index might be influencing the planner’s decisions. While the NoteTagsRelationship_Tag index is unique, the absence of a unique constraint on the NoteTagsRelationship_Note index could lead the planner to make different assumptions about the data distribution, further complicating its cost calculations.

Troubleshooting Steps, Solutions & Fixes

To address this issue, several steps can be taken to diagnose and resolve the performance regression caused by PRAGMA OPTIMIZE. These steps involve analyzing the query plan, adjusting the schema, and potentially modifying the query itself.

1. Analyze the Query Plan and Statistics:

The first step is to thoroughly analyze the query plans before and after running PRAGMA OPTIMIZE. This involves examining the sqlite_stat1 table to understand how the statistics have changed and how these changes might be influencing the query planner’s decisions. The sqlite_stat1 table contains information about the distribution of data in the indexed columns, which the query planner uses to estimate the cost of different execution plans.

In this case, the sqlite_stat1 entries for the Note table show that the Note_deleted index has a high number of rows (44858) and a relatively low number of distinct values (4). This suggests that the deleted column has a low selectivity, meaning that a large number of rows have the same value for deleted. However, the query planner’s decision to use the Note_deleted index post-optimization indicates that it may be overestimating the selectivity of the deleted IS NULL condition.

2. Adjust the Schema:

One potential solution is to adjust the schema to provide the query planner with more accurate information about the data distribution. This could involve adding a unique constraint to the NoteTagsRelationship_Note index, which would help the planner make more informed decisions about the cost of different execution plans. Additionally, considering the use of integer-based primary keys instead of text-based UUIDs could improve the planner’s ability to estimate costs accurately.

3. Modify the Query:

Another approach is to modify the query to make it more query-planner-friendly. This could involve rewriting the query to avoid correlated subqueries or to use different join strategies that the planner can optimize more effectively. For example, rewriting the query to use a LEFT JOIN instead of a correlated subquery might lead to a more efficient execution plan.

4. Use Query Hints:

SQLite allows the use of query hints to influence the query planner’s decisions. In this case, adding a hint to force the planner to use the primary key index on the Note table instead of the Note_deleted index could help mitigate the performance regression. However, this approach should be used with caution, as it can lead to brittle queries that may not perform well under different data distributions.

5. Re-evaluate the Use of PRAGMA OPTIMIZE:

Finally, it may be necessary to re-evaluate the use of PRAGMA OPTIMIZE in this context. While PRAGMA OPTIMIZE is designed to improve query performance, it can sometimes lead to unexpected regressions, particularly in complex queries or schemas. In this case, it might be worth considering whether the benefits of running PRAGMA OPTIMIZE outweigh the potential risks, or whether alternative optimization strategies could be employed.

6. Monitor and Test:

After implementing any changes, it is crucial to monitor the performance of the query and test it under different data distributions to ensure that the issue has been resolved. This involves running the query with and without PRAGMA OPTIMIZE and comparing the execution plans and performance metrics to verify that the changes have had the desired effect.

In conclusion, the performance regression observed after running PRAGMA OPTIMIZE is likely due to the query planner’s reliance on updated statistics that lead it to choose a less efficient execution plan. By analyzing the query plan, adjusting the schema, modifying the query, and re-evaluating the use of PRAGMA OPTIMIZE, it is possible to mitigate this issue and restore the query’s performance. However, this process requires a thorough understanding of the schema, the query, and the query planner’s behavior, as well as careful testing and monitoring to ensure that the changes have the desired effect.