Regression in SQLite 3.47.2: Complex CTE Query Hangs Indefinitely

Issue Overview: Regression in Query Performance Due to Automatic Indexing Changes

The core issue revolves around a significant regression in query performance when executing a complex Common Table Expression (CTE) query in SQLite versions 3.45.2 and 3.47.2. In SQLite 3.45.2, the query executes in approximately 40 seconds, whereas in SQLite 3.47.2, the same query hangs indefinitely. The root cause of this regression is traced back to changes in how SQLite handles automatic indexing during query execution.

The query in question involves 53 different tables and indexes, making it a highly complex and resource-intensive operation. The query plan reveals that the primary difference between the two versions lies in the utilization of automatic indexes. In SQLite 3.45.2, the query plan leverages 8 automatic indexes, which significantly speeds up the query execution. However, in SQLite 3.47.2, only 4 automatic indexes are used, with the remaining 4 being converted into full table scans. This conversion from automatic indexing to full table scans is the primary reason for the drastic performance degradation.

Automatic indexing is a feature in SQLite that creates temporary indexes on-the-fly to optimize query performance. These indexes are particularly useful for complex queries involving multiple joins and subqueries, as they reduce the need for full table scans. However, the regression in SQLite 3.47.2 indicates that the query planner is no longer able to effectively utilize automatic indexing in certain scenarios, leading to suboptimal query plans and, consequently, poor performance.

Possible Causes: Changes in Query Planner Behavior and Automatic Indexing

The regression in query performance can be attributed to several potential causes, all of which are related to changes in the query planner’s behavior and its handling of automatic indexing. One of the most significant changes is the introduction of new optimizations in SQLite 3.47.2, which may have inadvertently affected the query planner’s ability to generate efficient query plans for complex CTE queries.

The query planner in SQLite is responsible for determining the most efficient way to execute a given query. It does this by evaluating various execution plans and selecting the one with the lowest estimated cost. In the case of complex CTE queries, the query planner must consider a large number of possible execution plans, making it more susceptible to changes in its optimization algorithms.

One possible cause of the regression is that the query planner in SQLite 3.47.2 is now more conservative in its use of automatic indexing. This conservatism may be due to changes in the cost estimation algorithms, which now favor full table scans over automatic indexing in certain scenarios. While this change may improve performance for some queries, it can have a detrimental effect on others, particularly those that rely heavily on automatic indexing to avoid expensive full table scans.

Another potential cause is the introduction of new optimizations that interfere with the query planner’s ability to recognize when automatic indexing would be beneficial. For example, the query planner may now prioritize other optimizations, such as join reordering or subquery flattening, over the creation of automatic indexes. This prioritization could lead to situations where the query planner fails to create necessary automatic indexes, resulting in suboptimal query plans.

Additionally, the regression may be related to changes in the way SQLite handles temporary tables and subqueries. In complex CTE queries, temporary tables and subqueries are often used to store intermediate results, which are then used in subsequent parts of the query. If the query planner in SQLite 3.47.2 is less effective at optimizing these temporary tables and subqueries, it could lead to increased reliance on full table scans and, consequently, slower query execution.

Troubleshooting Steps, Solutions & Fixes: Addressing the Regression and Restoring Query Performance

To address the regression and restore query performance, several troubleshooting steps and solutions can be employed. The first step is to identify the specific changes in SQLite 3.47.2 that are causing the regression. This can be done by examining the query plans generated by both SQLite 3.45.2 and SQLite 3.47.2 and comparing the differences in how automatic indexing is utilized.

Once the specific changes have been identified, the next step is to determine whether the regression can be mitigated by modifying the query or the database schema. For example, it may be possible to rewrite the query to make better use of existing indexes or to create additional indexes that can be used in place of automatic indexes. In some cases, it may also be possible to adjust the query planner’s behavior by using query hints or pragmas to force the use of automatic indexing.

If modifying the query or schema is not feasible, the next step is to consider applying a patch or update to SQLite that addresses the regression. In this case, the regression was resolved in a subsequent check-in (0852c57ee2768224), which restored the query planner’s ability to effectively use automatic indexing. Applying this patch or updating to a version of SQLite that includes the fix should resolve the performance issue.

In cases where applying a patch or update is not immediately possible, a temporary workaround may be to revert to an earlier version of SQLite that does not exhibit the regression. While this is not a long-term solution, it can provide immediate relief while a more permanent fix is developed.

Another potential solution is to manually create the missing automatic indexes as permanent indexes in the database. This approach can be particularly effective if the query planner is consistently failing to create certain automatic indexes. By creating these indexes manually, the query planner can be forced to use them, thereby avoiding the need for full table scans.

Finally, it is important to monitor the performance of the query after applying any fixes or workarounds to ensure that the regression has been fully resolved. This can be done by comparing the query execution times before and after the changes and by examining the query plans to confirm that the desired optimizations are being applied.

In conclusion, the regression in SQLite 3.47.2 is a complex issue that requires a thorough understanding of the query planner’s behavior and the role of automatic indexing in query optimization. By carefully analyzing the query plans, identifying the root cause of the regression, and applying the appropriate fixes, it is possible to restore query performance and ensure that the database continues to operate efficiently.