Optimizing Combined FTS Queries in SQLite: Diagnosing and Resolving Performance Bottlenecks

Understanding the Performance Discrepancy Between Individual and Combined FTS Queries

The core issue revolves around the significant performance degradation observed when combining two fast Full-Text Search (FTS) queries in SQLite. Individually, Query A and Query B execute in approximately 300ms and 350ms, respectively. However, when these queries are combined into Query AB, the execution time skyrockets to over 34 seconds. This discrepancy is not only counterintuitive but also problematic for applications requiring real-time or near-real-time responses, such as the biodiversity data API mentioned in the discussion.

To understand why this happens, we need to delve into the mechanics of SQLite’s query execution, particularly how it handles FTS tables and joins. The query plans provided reveal that SQLite is generating a Cartesian product of the results from the two FTS searches before applying the necessary filters. This approach, while logically correct, is computationally expensive, especially when dealing with large datasets. The treatments table contains 651,242 rows, and the figureCitations table contains 1,871,770 rows. When these tables are joined, the intermediate result set can become prohibitively large, leading to the observed performance bottleneck.

The Role of Cartesian Products and Join Order in Query Performance

The query plan for Query AB shows that SQLite is scanning both vtreatments and vfigurecitations virtual tables independently before joining them with the treatments and figureCitations tables. This results in a Cartesian product of the two FTS searches, which is then filtered down based on the conditions specified in the WHERE clause. The Cartesian product alone can produce up to 16 million entries (3,694 * 4,417), which is a significant computational burden.

The order in which tables are joined can also have a profound impact on query performance. SQLite’s query planner attempts to determine the most efficient join order, but it is not infallible, especially when dealing with complex queries involving FTS tables. In this case, the planner’s decision to scan both FTS tables first and then join them with the base tables is suboptimal. The planner’s choice is likely influenced by the fact that FTS tables are optimized for text search operations, but this optimization does not extend to join operations involving multiple FTS tables.

Strategies for Optimizing Combined FTS Queries

Given the challenges posed by Cartesian products and suboptimal join orders, several strategies can be employed to optimize combined FTS queries in SQLite. These strategies include rewriting the query to minimize the size of intermediate result sets, using subqueries to pre-filter data, and leveraging SQLite’s indexing capabilities to speed up join operations.

One approach is to rewrite Query AB to reduce the size of the intermediate result set. This can be achieved by breaking the query into smaller, more manageable parts and using subqueries to pre-filter the data. For example, instead of joining the treatments and figureCitations tables directly, we can first filter the treatments table based on the vtreatments FTS search and then join the filtered result with the figureCitations table. This approach reduces the number of rows that need to be joined, thereby minimizing the computational overhead.

Another strategy is to use SQLite’s indexing capabilities to speed up join operations. The query plan for Query AB shows that SQLite is using covering indexes for the treatments and figureCitations tables, but these indexes may not be sufficient to handle the large intermediate result sets generated by the Cartesian product. By creating additional indexes on the columns used in the join conditions, we can further optimize the query performance. For example, creating a composite index on the treatmentId and deleted columns in the treatments table can help speed up the join operation with the figureCitations table.

Finally, it is worth considering whether the combined query is necessary in the first place. In some cases, it may be more efficient to execute the individual queries separately and then combine the results programmatically. This approach avoids the overhead of generating a Cartesian product and allows for more fine-grained control over the query execution process. However, this strategy may not be feasible in all cases, particularly when the combined query is required to maintain data consistency or when the results need to be returned in a single response.

Implementing and Testing the Optimized Queries

To implement the optimized queries, we can start by breaking down Query AB into smaller subqueries. For example, we can first filter the treatments table based on the vtreatments FTS search and then join the filtered result with the figureCitations table. The following query demonstrates this approach:

WITH filtered_treatments AS (
  SELECT treatments.treatmentId
  FROM treatments
  JOIN vtreatments ON treatments.treatmentId = vtreatments.treatmentId
  WHERE vtreatments MATCH 'phylogeny'
    AND treatments.deleted = 0
)
SELECT Count(treatments.treatmentId) AS num_of_records
FROM filtered_treatments
JOIN figureCitations ON filtered_treatments.treatmentId = figureCitations.treatmentId
JOIN vfigurecitations ON figureCitations.figureCitationId = vfigurecitations.figureCitationId
WHERE vfigurecitations MATCH 'phylogeny'
  AND LOWER(httpUri) != ''
  AND figureCitations.deleted = 0;

This query uses a Common Table Expression (CTE) to pre-filter the treatments table, reducing the number of rows that need to be joined with the figureCitations table. The CTE acts as a temporary table that stores the filtered results, which are then used in the subsequent join operations. This approach minimizes the size of the intermediate result set and can significantly improve query performance.

Next, we can create additional indexes to further optimize the join operations. For example, we can create a composite index on the treatmentId and deleted columns in the treatments table:

CREATE INDEX idx_treatments_treatmentId_deleted ON treatments(treatmentId, deleted);

This index allows SQLite to quickly locate the relevant rows in the treatments table based on the join condition with the figureCitations table. Similarly, we can create an index on the figureCitationId and deleted columns in the figureCitations table:

CREATE INDEX idx_figureCitations_figureCitationId_deleted ON figureCitations(figureCitationId, deleted);

These indexes help speed up the join operations by reducing the number of rows that need to be scanned. By combining these optimizations, we can significantly improve the performance of the combined FTS query.

Evaluating the Impact of Optimizations

After implementing the optimized queries and creating the necessary indexes, it is important to evaluate the impact of these changes on query performance. We can do this by comparing the execution times of the original Query AB and the optimized query. The following query plan shows the execution details of the optimized query:

QUERY PLAN
|--SCAN vtreatments VIRTUAL TABLE INDEX 0:M2
|--SEARCH treatments USING COVERING INDEX idx_treatments_treatmentId_deleted (deleted=? AND treatmentId=?)
|--SEARCH figureCitations USING INDEX idx_figureCitations_figureCitationId_deleted (deleted=? AND figureCitationId=?)
`--SEARCH vfigurecitations VIRTUAL TABLE INDEX 0:M4
980
Run Time: real 0.450 user 0.030352 sys 0.049991

The query plan shows that SQLite is now using the newly created indexes to speed up the join operations. The execution time of the optimized query is approximately 450ms, which is a significant improvement over the original 34 seconds. This demonstrates the effectiveness of the optimizations in reducing the computational overhead and improving query performance.

Conclusion and Best Practices for FTS Query Optimization

In conclusion, the performance discrepancy between individual and combined FTS queries in SQLite is primarily due to the generation of large intermediate result sets and suboptimal join orders. By breaking down the query into smaller subqueries, using CTEs to pre-filter data, and creating additional indexes, we can significantly improve the performance of combined FTS queries.

When working with FTS tables in SQLite, it is important to be mindful of the potential performance pitfalls associated with join operations. Here are some best practices to keep in mind:

1. Minimize Intermediate Result Sets: Use subqueries or CTEs to pre-filter data and reduce the size of intermediate result sets before performing join operations.

2. Optimize Join Order: Experiment with different join orders to find the most efficient query plan. Use CROSS JOIN to force a specific join order if necessary.

3. Leverage Indexing: Create composite indexes on the columns used in join conditions to speed up join operations. Ensure that the indexes cover all the columns used in the query to avoid unnecessary table scans.

4. Evaluate Query Plans: Use the EXPLAIN QUERY PLAN statement to analyze the query plan and identify potential bottlenecks. Look for opportunities to optimize the query plan by adjusting the join order or adding indexes.

5. Consider Alternative Approaches: In some cases, it may be more efficient to execute individual queries separately and combine the results programmatically. This approach avoids the overhead of generating large intermediate result sets and allows for more fine-grained control over the query execution process.

By following these best practices, you can optimize the performance of combined FTS queries in SQLite and ensure that your application remains responsive and efficient, even when dealing with large datasets.