JOINs with FTS5 Virtual Tables: Performance Issues and Solutions

Slow JOIN Performance with FTS5 Virtual Tables and Normal Tables

When working with SQLite, combining Full-Text Search (FTS) virtual tables, such as FTS5, with normal tables in JOIN operations can lead to significant performance degradation. This issue is particularly evident when the FTS5 virtual table is used in conjunction with large datasets, where the query planner may struggle to optimize the execution plan effectively. The problem manifests as slow query execution times, even when the underlying data and indexes are well-structured.

In the provided scenario, a query involving a JOIN between an FTS5 virtual table (vtreatments) and two normal tables (materialsCitations and treatments) took 170 seconds to execute. This is in stark contrast to the sub-queries, which executed in milliseconds. The primary issue lies in how SQLite’s query planner handles the interaction between the FTS5 virtual table and the normal tables, especially when filtering conditions and GROUP BY clauses are involved.

The FTS5 virtual table is designed for fast text search operations, but its integration with normal tables can be problematic. The query planner may not always choose the most efficient execution plan, particularly when the FTS5 virtual table returns a large number of rows. This inefficiency is exacerbated when the query involves additional filtering conditions, such as checking for non-deleted rows (deleted = 0), and grouping the results.

Inefficient Query Plans Due to Missing Statistics

One of the primary reasons for the poor performance of JOINs involving FTS5 virtual tables is the lack of accurate statistics about the data distribution in the normal tables. SQLite’s query planner relies on these statistics to make informed decisions about the order of table scans, index usage, and join strategies. When these statistics are missing or outdated, the query planner may choose a suboptimal execution plan, leading to slow query performance.

In the provided example, the query planner initially chose a plan that involved scanning the FTS5 virtual table first, followed by searching the materialsCitations and treatments tables using their respective indexes. However, this plan was inefficient because the query planner did not have accurate information about the selectivity of the deleted = 0 condition. As a result, it underestimated the number of rows that would need to be processed, leading to a slow query execution.

Running the ANALYZE command on the materialsCitations and treatments tables provided the query planner with the necessary statistics to make a better decision. After analyzing the tables, the query planner changed the order of table access, leading to a significant improvement in query performance. The updated query plan involved searching the treatments table first, followed by the materialsCitations table, and finally scanning the FTS5 virtual table. This change reduced the query execution time from 170 seconds to just 259 milliseconds.

The ANALYZE command collects statistics about the distribution of data in the tables and indexes, which helps the query planner make more informed decisions. However, SQLite does not run ANALYZE automatically because it can be time-consuming, especially for large databases. The command requires a full scan of each index, which can take a significant amount of time depending on the size of the database and the hardware on which it is running.

Optimizing JOINs with FTS5 Virtual Tables Using ANALYZE and PRAGMA Commands

To address the performance issues associated with JOINs involving FTS5 virtual tables, it is essential to ensure that the query planner has access to accurate statistics about the data distribution in the normal tables. This can be achieved by running the ANALYZE command on the relevant tables after the data has been loaded and the indexes have been created. The ANALYZE command should be run periodically, especially after significant changes to the data, to ensure that the statistics remain up-to-date.

In addition to running ANALYZE, it is also recommended to use the PRAGMA optimize command, which is designed to be run by applications as they are shutting down. The PRAGMA optimize command automatically determines which indexes need to be analyzed and runs the necessary ANALYZE commands on those indexes. This approach is more efficient than running a full ANALYZE on all indexes, as it only analyzes the indexes that have changed significantly since the last analysis.

For databases that are primarily read-only, such as the one in the provided scenario, running ANALYZE once after the data has been loaded and the indexes have been created is usually sufficient. However, for databases that are frequently updated, it may be necessary to run ANALYZE or PRAGMA optimize periodically to ensure that the query planner continues to make optimal decisions.

Another approach to improving the performance of JOINs involving FTS5 virtual tables is to rewrite the query to minimize the interaction between the virtual table and the normal tables. In the provided example, the original query was rewritten to use a subquery that first filtered the treatments table based on the results from the FTS5 virtual table. This approach reduced the query execution time from 170 seconds to 439 milliseconds, as it allowed the query planner to process the FTS5 virtual table results more efficiently.

When rewriting queries, it is important to consider the selectivity of the conditions used in the WHERE clause. Conditions that filter out a large percentage of the rows should be applied as early as possible in the query execution plan to reduce the number of rows that need to be processed in subsequent steps. In the provided example, the condition vtreatments MATCH "carabus" was applied early in the rewritten query, which significantly reduced the number of rows that needed to be processed in the subsequent JOIN operations.

In conclusion, JOINs involving FTS5 virtual tables and normal tables can be challenging to optimize due to the unique characteristics of FTS5 virtual tables and the potential for inefficient query plans. However, by ensuring that the query planner has access to accurate statistics through the use of the ANALYZE and PRAGMA optimize commands, and by carefully rewriting queries to minimize the interaction between the virtual table and the normal tables, it is possible to achieve significant improvements in query performance.