SQLite Query Planner Fails with INDEXED BY and .expert Mode Errors

Understanding the Query Planner’s Behavior with INDEXED BY and .expert Mode

The core issue revolves around the SQLite query planner’s unexpected behavior when using the INDEXED BY clause and the .expert mode. Specifically, the .expert mode fails with the error Error: no such index: ix_treatments_authorityName, even though the index clearly exists and is listed when running .indexes. This discrepancy is puzzling, especially since the query executes successfully and efficiently when the INDEXED BY clause is used. However, without INDEXED BY, the query planner selects suboptimal indexes, leading to significantly slower query performance. This issue is compounded by the fact that the database schema is complex, with multiple tables, virtual tables (FTS5 and GEOPOLY), and a large dataset (17GB with 600K rows in the central treatments table).

The problem is further complicated by the dynamic nature of the queries, which are built programmatically based on user input via a REST API. This means that the query planner must handle a wide variety of query patterns, including LIKE, MATCH, and BETWEEN operations, without a predefined structure. The presence of a deleted flag (though temporarily removed for simplicity) adds another layer of complexity, as it was previously part of the indexing strategy. The query planner’s inability to consistently choose the correct indexes suggests a deeper issue with how SQLite evaluates index selectivity and query patterns, particularly in complex schemas with dynamic query requirements.

Potential Causes of the Query Planner’s Suboptimal Index Selection

Several factors could contribute to the query planner’s suboptimal index selection and the .expert mode error. First, the structure of the indexes themselves may be problematic. As Keith Medcalf pointed out, the indexes might be "ass-backwards," with low-selectivity columns like deleted placed at the front of the index. This reduces the index’s effectiveness, as the query planner cannot efficiently narrow down the search space. Moving high-selectivity columns (e.g., authorityName) to the front of the index could improve performance, but this requires careful analysis of query patterns and data distribution.

Second, the .expert mode’s failure to recognize the ix_treatments_authorityName index suggests a potential bug or limitation in how .expert interacts with the INDEXED BY clause. The .expert mode is designed to recommend indexes for a given query, but it may not account for manually specified indexes via INDEXED BY. This could explain why it throws an error even though the index exists. Additionally, the .expert mode relies on SQLite’s internal statistics (e.g., STAT4) to make recommendations. If these statistics are outdated or incomplete, the recommendations may be inaccurate.

Third, the dynamic nature of the queries poses a significant challenge for the query planner. With no predefined query patterns, the planner must evaluate a wide range of potential indexes for each query. This increases the likelihood of suboptimal index selection, especially if the statistics used by the planner do not accurately reflect the data distribution. The use of virtual tables (FTS5 and GEOPOLY) further complicates matters, as these tables have different indexing and query optimization requirements compared to standard tables.

Finally, the size of the database (17GB) and the number of rows (600K in treatments and up to 1M in other tables) mean that even small inefficiencies in index selection can have a significant impact on query performance. The query planner must balance the need for fast query execution with the overhead of evaluating multiple indexes, which can lead to suboptimal decisions in complex schemas.

Troubleshooting Steps, Solutions, and Fixes for Query Planner Issues

To address the query planner’s suboptimal index selection and the .expert mode error, follow these detailed troubleshooting steps and solutions:

Step 1: Review and Optimize Index Structure

Begin by reviewing the structure of your indexes, particularly those used in the problematic queries. Ensure that high-selectivity columns (e.g., authorityName) are placed at the front of the index, followed by lower-selectivity columns (e.g., deleted). For example, consider modifying the ix_treatments_authorityName index to prioritize authorityName:

CREATE INDEX ix_treatments_authorityName ON treatments(authorityName, deleted);

This change allows the query planner to narrow down the search space more effectively when filtering by authorityName.

Step 2: Update SQLite Statistics with ANALYZE

Ensure that SQLite’s internal statistics are up to date by running the ANALYZE command. This command collects data distribution statistics for all indexed columns, which the query planner uses to make informed decisions. Run the following command in the SQLite CLI:

ANALYZE;

If the statistics are outdated or incomplete, the query planner may make suboptimal index choices. Regularly running ANALYZE is especially important in databases with frequent data changes.

Step 3: Verify STAT4 Support

Confirm that your SQLite build includes support for STAT4 statistics. STAT4 provides more detailed data distribution statistics, which can help the query planner make better decisions. Check the SQLite version and compile options to ensure STAT4 is enabled:

sqlite> .version

If STAT4 is not enabled, consider rebuilding SQLite with STAT4 support. This can be done by compiling SQLite from source with the appropriate flags.

Step 4: Test Queries Without INDEXED BY

To better understand the query planner’s behavior, test your queries without the INDEXED BY clause. This allows the planner to choose indexes based on its internal logic and statistics. Compare the performance of these queries with those using INDEXED BY to identify any discrepancies. For example:

SELECT
  checkInYear,
  COUNT(DISTINCT figureCitations.figureCitationId) AS num
FROM
  treatments
  JOIN figureCitations ON figureCitations.treatmentId = treatments.treatmentId
WHERE
  treatments.authorityName LIKE 'miller%'
  AND treatments.deleted = 0
  AND figureCitations.deleted = 0
GROUP BY 1;

If the query planner consistently chooses suboptimal indexes, consider refining your indexing strategy or using query hints to guide the planner.

Step 5: Investigate .expert Mode Limitations

The .expert mode’s failure to recognize the ix_treatments_authorityName index suggests a potential limitation or bug. To work around this issue, avoid using INDEXED BY when running .expert. Instead, let the .expert mode recommend indexes based on the query structure. For example:

sqlite> .expert
sqlite> SELECT
  ...>   checkInYear,
  ...>   COUNT(DISTINCT figureCitations.figureCitationId) AS num
  ...> FROM
  ...>   treatments
  ...>   JOIN figureCitations ON figureCitations.treatmentId = treatments.treatmentId
  ...> WHERE
  ...>   treatments.authorityName LIKE 'miller%'
  ...>   AND treatments.deleted = 0
  ...>   AND figureCitations.deleted = 0
  ...> GROUP BY 1;

If .expert still fails to recommend appropriate indexes, consider manually analyzing query patterns and refining your indexing strategy.

Step 6: Optimize for Dynamic Queries

Given the dynamic nature of your queries, focus on creating a flexible indexing strategy that accommodates a wide range of query patterns. This may involve creating composite indexes that cover multiple columns or using covering indexes to reduce the need for table lookups. For example:

CREATE INDEX ix_treatments_authorityName_deleted ON treatments(authorityName, deleted, treatmentId);

This index covers both the authorityName and deleted columns, as well as the treatmentId column used in joins. By covering more columns, you reduce the need for additional lookups, improving query performance.

Step 7: Monitor and Tune Query Performance

Regularly monitor query performance using tools like EXPLAIN QUERY PLAN and SQLite’s built-in profiling capabilities. Identify slow queries and analyze their execution plans to pinpoint inefficiencies. For example:

EXPLAIN QUERY PLAN
SELECT
  checkInYear,
  COUNT(DISTINCT figureCitations.figureCitationId) AS num
FROM
  treatments
  JOIN figureCitations ON figureCitations.treatmentId = treatments.treatmentId
WHERE
  treatments.authorityName LIKE 'miller%'
  AND treatments.deleted = 0
  AND figureCitations.deleted = 0
GROUP BY 1;

Use the insights gained from these analyses to refine your indexing strategy and query design.

Step 8: Consider Advanced Optimization Techniques

For particularly challenging queries, consider advanced optimization techniques such as partial indexes, expression indexes, or even rewriting queries to better align with SQLite’s strengths. For example, if certain queries frequently filter by deleted = 0, consider creating a partial index:

CREATE INDEX ix_treatments_authorityName_active ON treatments(authorityName) WHERE deleted = 0;

This index only includes rows where deleted = 0, reducing its size and improving query performance.

By following these steps, you can address the query planner’s suboptimal index selection and the .expert mode error, ultimately improving the performance and reliability of your SQLite database.