Optimizing MIN() and MAX() Aggregate Queries with Indexes in SQLite

Understanding the Performance Discrepancy Between Combined and Separate MIN()/MAX() Queries

A common scenario in SQLite involves retrieving the minimum and maximum values of a column using the MIN() and MAX() aggregate functions. When these functions are used in separate queries, SQLite efficiently leverages indexes to return results. However, when combined into a single query (e.g., SELECT MIN(c), MAX(c) FROM t;), performance degrades significantly for larger tables. This discrepancy arises from differences in how SQLite’s query planner processes these two forms of aggregation.

Key Observations

1. Execution Plan Differences
   When MIN(c) and MAX(c) are queried separately, SQLite uses the index on column c to perform a covering index scan, retrieving the extremum values directly from the index structure. For example:

   EXPLAIN QUERY PLAN SELECT MIN(a) FROM foo;
   -- Output: `SEARCH foo USING COVERING INDEX foo_a`
   

   The same applies to MAX(a).

   However, when combined into SELECT MIN(a), MAX(a) FROM foo;, the query planner defaults to a full table scan (or sequential scan) of the base table:

   EXPLAIN QUERY PLAN SELECT MIN(a), MAX(a) FROM foo;
   -- Output: `SCAN foo`
   

   This forces SQLite to read every row in the table, bypassing the index entirely.

2. Performance Impact
   The performance gap becomes noticeable as the table grows. For small tables (e.g., hundreds of rows), the difference is negligible. For larger tables (millions of rows), a full table scan introduces significant latency compared to index-based extremum lookups, which are O(1) operations in practice (due to B-tree traversal to the first or last leaf node).

3. Index Utilization Logic
   SQLite’s query planner prioritizes minimizing disk I/O and computational overhead. When a single aggregate function is used, the planner recognizes that the extremum can be retrieved via a single seek on the index. For combined aggregates, the planner does not currently optimize for dual index seeks and instead falls back to scanning the entire table. This behavior is rooted in the planner’s cost-based heuristics, which favor simpler execution plans unless explicitly guided otherwise.

Why SQLite Fails to Use Indexes for Combined MIN()/MAX() Queries

1. Query Planner Limitations with Multiple Aggregates

SQLite’s query planner treats aggregate functions as independent operations unless they can be resolved through a single pass over the data. When MIN(c) and MAX(c) are requested together, the planner assumes that both values must be derived from the same dataset traversal. Since the index for c is ordered, the minimum and maximum values reside at opposite ends of the B-tree structure. Retrieving both would require two separate index traversals (one for MIN(c), one for MAX(c)), which the planner does not automatically orchestrate for combined queries.

Instead, the planner opts for a full table scan, which—while slower—ensures that all rows are processed in a single pass. This decision is suboptimal for indexed columns but aligns with the planner’s conservative approach to minimizing complexity.

2. Index Scan vs. Table Scan Tradeoffs

A covering index (e.g., CREATE INDEX foo_a ON foo (a);) contains all the data needed to resolve MIN(a) and MAX(a) without accessing the base table. However, the planner’s cost estimator assigns a lower "cost" to a full table scan when multiple aggregates are involved. This is because:

• Index Fragmentation: If the index is not clustered (i.e., the table rows are not physically ordered by a), retrieving non-sequential data via the index may incur random I/O.
• Statistics Misinterpretation: SQLite’s internal statistics (stored in sqlite_stat1) may inaccurately estimate the number of rows or distribution of values, leading the planner to prefer a table scan.

3. Lack of Composite Aggregate Optimization

SQLite does not currently implement optimizations for composite aggregates that could leverage multiple index seeks. For example:

• Dual Index Traversal: Executing MIN(c) via a forward index scan and MAX(c) via a reverse scan, then merging results.
• Materialized Subqueries: Treating MIN(c) and MAX(c) as independent subqueries during planning.

Without these optimizations, the planner defaults to the simplest execution strategy, even if it is inefficient.

Solutions to Force Index Usage for Combined MIN()/MAX() Queries

1. Rewrite the Query Using Subqueries

The most effective workaround is to split the combined query into two subqueries, each targeting a single aggregate. This forces the planner to use the index for both operations:

SELECT (SELECT MIN(a) FROM foo) AS min_a, (SELECT MAX(a) FROM foo) AS max_a;

Execution Plan:

QUERY PLAN
|--SCALAR SUBQUERY
|  `--SEARCH foo USING COVERING INDEX foo_a
`--SCALAR SUBQUERY
   `--SEARCH foo USING COVERING INDEX foo_a

Each subquery triggers an index seek, avoiding a full table scan. This approach reduces execution time to near-constant for indexed columns, regardless of table size.

2. Use UNION ALL for Batch Operations

If the query is part of a larger batch process, combine the results using UNION ALL:

SELECT 'min' AS type, MIN(a) AS value FROM foo
UNION ALL
SELECT 'max' AS type, MAX(a) AS value FROM foo;

Execution Plan:

QUERY PLAN
|--CO-ROUTINE 2
|  `--SEARCH foo USING COVERING INDEX foo_a
|--CO-ROUTINE 4
|  `--SEARCH foo USING COVERING INDEX foo_a
`--COMPOUND QUERY

This method is less elegant for application code but ensures index usage.

3. Leverage Covering Indexes

Ensure the index on c is a covering index (i.e., includes all columns referenced in the query). While this is already true for CREATE INDEX foo_a ON foo (a);, composite indexes or INCLUDE columns (in SQLite 3.30+) can help in more complex scenarios:

CREATE INDEX foo_covering ON foo (a) INCLUDE (other_column);

4. Update SQLite Statistics

Run the ANALYZE command to refresh table and index statistics. This helps the planner make better decisions about index usage:

ANALYZE;

5. Use Query Planner Hints (Advanced)

For advanced users, SQLite’s INDEXED BY clause can force the planner to use a specific index:

SELECT MIN(a), MAX(a) FROM foo INDEXED BY foo_a;

Caution: This bypasses the planner’s cost-based logic and may degrade performance if the index is suboptimal for other queries.

6. Monitor for Future Optimizer Improvements

SQLite’s query planner evolves over time. Follow release notes for optimizations related to composite aggregates. For instance, future versions may recognize that MIN() and MAX() can be resolved via separate index traversals in a single query.

By understanding the limitations of SQLite’s query planner and restructuring queries to align with its optimization patterns, developers can achieve near-optimal performance for combined MIN()/MAX() operations. The key is to decouple the aggregates into subqueries or separate statements, ensuring that each leverages the index independently.