SQLite Index Selection for COUNT(*) Queries

Core Principles of SQLite Query Planner Behavior for COUNT(*) Operations

The SQLite query planner’s decision-making process for selecting an optimal index during a COUNT(*) operation is governed by a combination of statistical metadata, index structure analysis, and heuristics based on column type affinity. When executing SELECT COUNT(*) FROM table;, SQLite aims to identify the smallest available covering index to minimize I/O operations. A covering index is one that contains all necessary data to resolve the query without requiring table heap accesses. For COUNT(*), this typically translates to scanning the index with the fewest total pages. However, the planner does not directly use physical storage metrics like index page counts. Instead, it estimates index size based on the sqlite_stat1 table’s metadata and assumptions about per-row byte consumption derived from column type declarations.

The sqlite_stat1 table stores two critical values for each index: the total number of rows in the indexed table and the approximate number of distinct entries in the index. These values help the optimizer estimate selectivity for WHERE clause predicates but are less directly influential for COUNT(*). For full-table scans, the planner prioritizes indexes it deems "narrower"—those with smaller estimated per-row byte footprints—to reduce the total pages read. This estimation hinges on the declared data types of the indexed columns. Columns with numeric affinity (e.g., INTEGER) are assumed to occupy 4 bytes per field, while TEXT columns default to 20 bytes unless a length constraint (e.g., VARCHAR(100)) explicitly caps the estimate. Indexes involving expressions or user-defined functions introduce ambiguity, as their effective data types are not explicitly declared, leading the planner to fall back on conservative assumptions.

Key Factors Leading to Suboptimal Index Selection in COUNT(*) Scenarios

1. Misalignment Between Declared Column Types and Actual Storage Footprint
SQLite’s byte-per-row estimates for indexes are based on the declared types of columns, not their actual stored values. An index on an INTEGER column will be presumed to consume 4 bytes per entry, even if the actual values are smaller (e.g., single-digit integers). Conversely, a TEXT column with short strings may occupy far fewer bytes than the 20-byte default assumption. When an index’s physical size (pages) contradicts the optimizer’s type-based estimates, suboptimal index selection can occur. For example, an index on a TEXT column with a 20-byte estimate might physically occupy fewer pages than an INTEGER index due to compression or efficient storage of small strings, but the planner may still favor the INTEGER index based on erroneous size projections.

2. Ambiguity in Expression-Based Indexes
Indexes built on expressions or user-defined functions (e.g., Func(textField)) lack explicit type declarations for their computed values. SQLite defaults to treating such expressions as BLOB or TEXT with a 20-byte estimate, even if the function’s output is logically comparable to a smaller type. This can lead the planner to overestimate the index’s size and deprioritize it incorrectly. In cases where the expression’s output is deterministic and produces values with a known, smaller size (e.g., fixed-length strings), the optimizer remains unaware of this nuance, resulting in misplaced preference for other indexes.

3. Incomplete or Outdated Statistics in sqlite_stat1
The sqlite_stat1 table’s statistics are populated via the ANALYZE command and are critical for the query planner’s cost calculations. If these statistics are outdated—such as after significant data inserts, updates, or deletions—the planner’s estimates for index size and selectivity become inaccurate. For COUNT(*), outdated row counts (nRow in sqlite_stat1) can mislead the planner into favoring indexes that were historically smaller but no longer represent the optimal choice. Additionally, the absence of sqlite_stat4 data (which provides deeper histogram-based insights) limits the planner’s ability to refine its estimates for complex queries.

4. Overhead of Deterministic Function Evaluation in Indexes
Indexes incorporating user-defined functions marked as SQLITE_DETERMINISTIC (a requirement for index eligibility) may incur hidden evaluation costs not reflected in the planner’s size estimates. While the function’s output is stored in the index, the planner does not account for potential computational overhead during index scans. This can lead to scenarios where a smaller physical index is avoided due to perceived risks of runtime inefficiency, even when such concerns are unfounded.

Comprehensive Diagnosis and Resolution Strategies for COUNT(*) Index Selection Issues

Step 1: Validate Index Metadata and Statistics
Begin by inspecting the contents of the sqlite_stat1 table for the target indexes. Execute:

SELECT tbl, idx, stat FROM sqlite_stat1 WHERE tbl = 'Table';

Verify that the nRow (first value in stat) aligns with the actual table row count. If discrepancies exist, regenerate statistics using ANALYZE. For the example indexes A and B, ensure that stat reflects the current data distribution. If index A (Func(textField)) shows a lower nRow than the table’s actual row count, the planner may incorrectly assume it is smaller.

Step 2: Analyze Query Plan and Index Usage
Use EXPLAIN QUERY PLAN to observe the planner’s decision-making process:

EXPLAIN QUERY PLAN SELECT COUNT(*) FROM Table;

Look for lines indicating USING COVERING INDEX. If index A is selected despite B having fewer pages, proceed to dissect the planner’s byte-per-row estimates. Calculate the estimated bytes for each index using their declared types:

• Index A (Func(textField)): Assumed 20 bytes per entry (TEXT default).
• Index B (intField): Assumed 4 bytes per entry (INTEGER).

Multiply these values by the nRow from sqlite_stat1 to compare estimated index sizes. If the estimate for A is lower than B, the planner’s preference for A is rationalized despite physical evidence to the contrary.

Step 3: Override Type-Based Assumptions via Manual Statistics
SQLite allows manual adjustment of index size estimates by modifying the sqlite_stat1 table. To reflect index B’s actual page count (220 pages), calculate a synthetic stat value that approximates the planner’s expectations. The formula for the second value in stat (approximate distinct entries) can be manipulated to influence the planner’s cost model. For example, setting stat for index B to 98007 98007 (implying a unique index) forces the planner to recognize it as a dense, narrow option. Execute:

UPDATE sqlite_stat1 SET stat = '98007 98007' WHERE idx = 'B';
ANALYZE; -- Ensure the planner reloads updated statistics

Re-run the query plan analysis to verify if index B is now selected.

Step 4: Optimize Index Design for COUNT(*) Scenarios
If manual statistic adjustments prove ineffective, redesign the schema to better align with the planner’s assumptions:

• Convert Expression-Based Indexes to Column-Based: Materialize the output of Func(textField) into a stored column with an explicit type declaration (e.g., VARCHAR(10)), then index the new column. This provides clarity to the planner:

  ALTER TABLE Table ADD COLUMN func_text VARCHAR(10) GENERATED ALWAYS AS (Func(textField)) VIRTUAL;
  CREATE INDEX A_new ON Table(func_text);
  

• Leverage Covering Indexes: Create a composite index on intField that includes other frequently accessed columns. While this increases the index’s physical size, it may improve the planner’s confidence in its utility for multiple queries.

Step 5: Utilize Index Hints for Critical Queries
As a last resort, override the planner’s choice using index hints:

SELECT COUNT(*) FROM Table INDEXED BY B;

This forces the use of index B but should be employed sparingly, as it bypasses the planner’s logic and may degrade performance under future data changes.

Step 6: Profile Index Storage Characteristics
Use the sqlite3_analyzer tool or custom queries against the DBSTAT virtual table to obtain precise page counts and storage details for each index:

SELECT name, pgsize FROM dbstat WHERE name IN ('A', 'B');

Compare these physical metrics against the planner’s estimates to identify gross misalignments. If index B is physically smaller but not selected, consider redefining the intField column with a more precise type (e.g., SMALLINT) to reinforce its 4-byte assumption.

Final Consideration: Function Determinism and Index Eligibility
Ensure that any user-defined functions used in indexes are correctly marked as SQLITE_DETERMINISTIC during registration. Non-deterministic functions render indexes ineligible for certain optimizations, potentially excluding them from COUNT(*) consideration entirely. Verify the function registration code:

sqlite3_create_function_v2(
  db, "Func", 1, SQLITE_UTF8 | SQLITE_DETERMINISTIC, 
  nullptr, &func, nullptr, nullptr, nullptr
);

The SQLITE_DETERMINISTIC flag is critical for index eligibility and planner trust in the index’s consistency.