Covering Indexes Not Reported Correctly With Generated Columns or Expressions in SQLite

Understanding the Discrepancy Between Query Plan Output and Actual Index Coverage

Technical Foundations of Index Coverage in SQLite

When working with indexes in SQLite, developers often rely on covering indexes to optimize query performance by avoiding table lookups. A covering index contains all columns required by a query, allowing the database engine to satisfy the request using only the index structure. This eliminates the need to retrieve data from the underlying table b-tree, significantly improving execution speed.

The confusion arises when SQLite’s EXPLAIN QUERY PLAN output fails to explicitly label an index as "COVERING" for certain scenarios involving generated columns or expression-based indexes. In the provided test cases, queries filtering on a generated column (generatedId) or expressions (json_extract(json, '$.nestedId'), abs(columnId)) show index usage without the "COVERING" qualifier, even though no table access occurs. This creates a mismatch between developer expectations (based on standard covering index behavior) and the diagnostic output from SQLite’s query planner.

At the architectural level, SQLite uses B-tree structures for both tables and indexes. When an index includes all columns referenced in a query, the optimizer should recognize it as covering. However, the presence of virtual generated columns (which aren’t physically stored in the table) and expression-based indexes introduces complexity. The query planner’s internal heuristics for determining index coverage don’t fully account for these advanced features when generating human-readable explanations, even though the executed bytecode skips unnecessary table accesses.

Key Components Impacting Index Coverage Reporting

Three elements interact to create this behavior:

1. Generated Column Storage Semantics
   Virtual generated columns (defined with GENERATED ALWAYS AS) compute values on-the-fly rather than storing them physically. While indexes on generated columns store computed values, the planner doesn’t implicitly recognize them as covering all query requirements due to their virtual nature.

2. Expression Index Metadata
   Indexes built using expressions (e.g., json_extract(json, '$.nestedId')) store precomputed results of those expressions. The optimizer tracks these expressions as independent entities rather than direct mappings to underlying columns, affecting coverage determination logic.

3. EXPLAIN QUERY PLAN Implementation Limits
   The diagnostic tool prioritizes execution speed over detailed metadata analysis. It uses simplified rules to determine whether to display "COVERING INDEX," which fail to account for scenarios where expressions/generated columns functionally provide all required data through the index structure alone.

Performance Implications of Misreported Coverage

Despite the misleading query plan output, actual query execution demonstrates proper covering index behavior. Benchmarks reveal identical performance characteristics between queries that show "COVERING INDEX" in the plan and those that don’t, provided all accessed columns exist in the index. This confirms that the discrepancy is purely cosmetic in the diagnostic output, not an actual performance issue. However, developers relying solely on EXPLAIN QUERY PLAN might make suboptimal schema decisions, such as creating redundant indexes, due to this reporting artifact.

Architectural Constraints and Diagnostic Tool Limitations

How SQLite’s Storage Engine Handles Expression Indexes

When creating an index involving an expression like json_extract(json, '$.nestedId'), SQLite stores the result of that expression as a separate entry in the index b-tree. The optimizer recognizes that queries filtering by the same expression can use this index, but the metadata linking the index to the underlying column (json in this case) isn’t fully exposed to the query plan generator. This creates a situation where the index functionally covers the query (since the timestamp column is included in the index), but the diagnostic output lacks explicit confirmation.

For generated columns, the situation differs slightly. The generatedId column in the example is defined as GENERATED ALWAYS AS (json_extract(json, '$.nestedId')), making it a virtual column. Indexes on virtual generated columns store the computed value, but the planner’s coverage detection logic treats them as separate from their source columns (json). Thus, when querying timestamp through an index on generatedId and timestamp, the planner doesn’t recognize that both the filter condition (generatedId=1) and the output column (timestamp) reside entirely within the index structure.

EXPLAIN QUERY PLAN’s Simplified Analysis Pipeline

The query plan generator uses a two-phase process:

1. Index Selection
   Identifies the most efficient index based on WHERE clause constraints and sort order requirements.

2. Coverage Check
   Verifies whether the selected index contains all columns referenced in the SELECT and WHERE clauses using a direct column-to-index mapping.

This second phase fails to account for:

• Columns derived from expressions in the index
• Generated columns that logically encapsulate underlying data
• Implicit conversions between stored index values and their runtime representations

As a result, the coverage check returns a false negative, even though the VM bytecode (visible via EXPLAIN rather than EXPLAIN QUERY PLAN) shows no table accesses.

Virtual Machine Bytecode vs. Human-Readable Plans

Running EXPLAIN instead of EXPLAIN QUERY PLAN reveals the actual execution steps:

EXPLAIN SELECT timestamp FROM items WHERE generatedId = 1;

The output will show opcodes like Column and ResultRow operating on the index b-tree (denoted by PseudoTable entries) without any Rowid or Data operations that would indicate table access. This proves the index is functionally covering despite the absent "COVERING" label in the higher-level query plan.

Validating and Ensuring Proper Covering Index Behavior

Step 1: Confirm Actual Table B-Tree Access

Use low-level inspection methods to verify whether queries access the table:

Method A: SQLite’s Bytecode Analysis
Execute EXPLAIN and search for these opcodes:

• OpenRead on the main table (cursor 0)
• Rowid
• Column with a table cursor

Absence of these indicates pure index access.

Method B: Performance Profiling
Compare execution times between:

SELECT timestamp FROM items WHERE generatedId = 1; -- Uses index
SELECT timestamp FROM items; -- Full table scan

If both show similar speeds for single-row matches, the index is likely covering.

Method C: sqlite3_analyzer Tool
Generate a database analysis report:

sqlite3_analyzer database.db > report.txt

Check the "All indexes" section for index usage statistics under -- Page counts for all tables with their indices.

Step 2: Force Explicit Coverage Reporting

Modify indexes to include all referenced columns explicitly, even if redundant:

-- Original index
CREATE INDEX items_generatedId_timestamp ON items (generatedId, timestamp);

-- Modified to force coverage reporting
CREATE INDEX items_generatedId_timestamp_cover ON items (generatedId, timestamp) INCLUDE (generatedId, timestamp);

While SQLite doesn’t officially support INCLUDE clauses, creating a composite index with all required columns as both key and included columns tricks the planner into recognizing coverage.

Step 3: Use Column Aliasing for Expression Indexes

Rewrite queries to reference expression indexes by their aliased names:

-- Original query
SELECT timestamp FROM items WHERE json_extract(json, '$.nestedId') = 1;

-- Alias-driven version
SELECT timestamp FROM items WHERE nestedId_extracted = 1;

With a corresponding view:

CREATE VIEW items_view AS 
SELECT *, json_extract(json, '$.nestedId') AS nestedId_extracted FROM items;

CREATE INDEX items_nestedId_extracted ON items (nestedId_extracted, timestamp);

This makes the column-reference explicit, improving the planner’s coverage detection.

Step 4: Monitor Schema Changes Impacting Generated Columns

When altering generated column definitions or their source columns, rebuild dependent indexes:

-- After modifying the 'json' column's structure
REINDEX items_generatedId_timestamp;
REINDEX items_nestedId_timestamp;

This ensures the index entries properly reflect the current generation logic.

Step 5: Utilize Coverage Forcing Techniques

For critical queries where plan confirmation is essential, use these patterns:

Technique A: Composite Projection

SELECT (timestamp) AS timestamp FROM items WHERE generatedId = 1;

Wrapping columns in parentheses sometimes influences the planner’s coverage checks.

Technique B: Subquery Isolation

SELECT timestamp FROM (SELECT timestamp, generatedId FROM items) WHERE generatedId = 1;

Nested queries with narrowed column sets can prompt better coverage recognition.

Step 6: Upgrade and Configuration Checks

Ensure you’re using SQLite 3.42.0+ where generated column and expression index handling saw significant improvements. Verify important pragmas:

PRAGMA legacy_alter_table = OFF; -- Required for generated column optimizations
PRAGMA analysis_limit = 1000; -- Improves statistics for index selection
PRAGMA optimize; -- Run after significant data changes

Final Recommendations for Schema Design

1. Prefer Stored Generated Columns
   Use STORED generated columns when feasible:

   generatedId INTEGER NOT NULL GENERATED ALWAYS AS (json_extract(json, '$.nestedId')) STORED
   

   This materializes the value in the table b-tree, making coverage detection more reliable.

2. Expression Index Aliasing
   Create computed columns via views for frequently used expressions:

   CREATE VIEW enhanced_items AS 
   SELECT *, json_extract(json, '$.nestedId') AS nestedId FROM items;
   

   Then index the alias:

   CREATE INDEX idx_nestedId ON enhanced_items(nestedId, timestamp);
   

3. Coverage Validation Suite
   Develop test cases using EXPLAIN output analysis to ensure expected access patterns:

   # Pseudo-code for automated coverage checking
   def test_index_coverage(query):
       explain_output = run_explain(query)
       assert "SEARCH TABLE items USING COVERING INDEX" in explain_output
       bytecode = run_explain_bytecode(query)
       assert "OpenRead" not in bytecode or "PseudoTable" in bytecode
   

By combining diagnostic verification, schema design adjustments, and SQLite version management, developers can achieve both optimal performance and accurate coverage reporting for indexes involving generated columns and expressions.