Optimizing SQLite Queries Combining FTS and Non-FTS Indexes

Understanding the Query Planner’s Behavior with FTS and Non-FTS Indexes

The core issue revolves around the inefficiency of the SQLite query planner when combining Full-Text Search (FTS) indexes with non-FTS indexes. The problem manifests when attempting to filter records using both FTS and non-FTS criteria, particularly when the FTS filter returns a broad result set. The query planner often defaults to using the row ID index, leading to suboptimal performance, especially when the FTS filter is broad. This behavior is counterintuitive, as the planner should ideally estimate the usefulness of the row ID index based on the size of the FTS result set. Instead, it prioritizes the row ID index, resulting in unnecessary manual filtering and sorting, which significantly degrades performance.

The issue is further complicated by the fact that the query planner does not seem to leverage the likelihood function to estimate the usefulness of the row ID index. Even when explicitly using the likelihood function to indicate that the row ID index is likely to be less useful, the query planner does not adjust its strategy. This suggests a limitation in the query planner’s ability to dynamically adjust its indexing strategy based on the estimated size of the FTS result set.

Why the Query Planner Prioritizes Row ID Index Over Non-FTS Index

The query planner’s decision to prioritize the row ID index over the non-FTS index can be attributed to several factors. First, the row ID index is inherently more straightforward for the planner to use, as it directly maps to the primary key of the table. This direct mapping makes it easier for the planner to quickly locate records, even if it results in additional filtering and sorting steps later in the query execution.

Second, the query planner may not have sufficient information about the size of the FTS result set at the time it makes its indexing decision. The planner typically makes its decisions based on static statistics and heuristics, which may not accurately reflect the dynamic nature of FTS queries. As a result, the planner defaults to the row ID index, assuming it will be the most efficient path, even when this assumption is incorrect.

Third, the query planner may not fully understand the relationship between the FTS index and the non-FTS index. In the given scenario, the non-FTS index (posts_defaultView) is designed to cover both the filtering and sorting criteria, making it a more efficient choice when the FTS filter is broad. However, the planner may not recognize this relationship, leading it to prioritize the row ID index instead.

Strategies to Optimize Query Performance with FTS and Non-FTS Indexes

To address the inefficiencies in the query planner’s behavior, several strategies can be employed to optimize query performance when combining FTS and non-FTS indexes.

1. Disabling the Row ID Index in the Join Clause: One effective strategy is to disable the row ID index in the join clause by using the + operator. This forces the query planner to use the non-FTS index (posts_defaultView) for filtering and sorting, which can significantly improve performance when the FTS filter is broad. However, this approach has a trade-off: if the FTS filter is narrow, the non-FTS index may not be as efficient, leading to slower query execution.

2. Using Subqueries to Isolate FTS and Non-FTS Filters: Another approach is to use subqueries to isolate the FTS and non-FTS filters. By first applying the FTS filter in a subquery and then applying the non-FTS filter in the outer query, you can guide the query planner to use the appropriate indexes for each filter. This approach can help the planner make better decisions about which index to use, especially when the FTS filter is broad.

3. Leveraging the Likelihood Function: Although the likelihood function does not seem to affect the query planner in the given scenario, it can still be useful in other contexts. The likelihood function can be used to provide hints to the query planner about the expected size of the FTS result set. While this may not directly influence the planner’s indexing strategy, it can help in other optimizations, such as reducing the number of rows that need to be processed in subsequent steps.

4. Analyzing and Updating Statistics: Ensuring that the query planner has up-to-date statistics is crucial for making informed indexing decisions. Regularly analyzing and updating the statistics for both FTS and non-FTS indexes can help the planner better estimate the size of the FTS result set and make more efficient indexing decisions. This can be done using the ANALYZE command in SQLite, which updates the statistics used by the query planner.

5. Experimenting with Index Hints: SQLite allows for the use of index hints, which can be used to guide the query planner to use specific indexes. While this approach requires careful tuning and experimentation, it can be effective in scenarios where the query planner consistently makes suboptimal indexing decisions. Index hints can be specified using the INDEXED BY clause in the query.

6. Considering Alternative Indexing Strategies: In some cases, it may be beneficial to reconsider the indexing strategy altogether. For example, creating a composite index that includes both FTS and non-FTS criteria can help the query planner make more efficient decisions. This approach requires careful consideration of the query patterns and the trade-offs involved in maintaining additional indexes.

7. Monitoring and Profiling Query Performance: Regularly monitoring and profiling query performance can help identify inefficiencies and guide optimization efforts. Tools such as SQLite’s EXPLAIN QUERY PLAN can provide insights into the query planner’s decisions and help identify areas for improvement. Profiling can also help identify queries that are particularly sensitive to changes in indexing strategy, allowing for targeted optimizations.

8. Exploring Alternative Databases: While SQLite is a powerful and lightweight database, there may be scenarios where alternative databases with more advanced query optimization capabilities are better suited to the task. Databases such as PostgreSQL or MySQL offer more sophisticated query planners and indexing strategies, which may be better equipped to handle complex queries involving both FTS and non-FTS criteria.

In conclusion, optimizing SQLite queries that combine FTS and non-FTS indexes requires a deep understanding of the query planner’s behavior and the ability to guide it towards more efficient indexing decisions. By employing strategies such as disabling the row ID index, using subqueries, leveraging the likelihood function, and regularly updating statistics, it is possible to significantly improve query performance. Additionally, careful monitoring and profiling of query performance, along with consideration of alternative indexing strategies and databases, can further enhance optimization efforts.