Performance Regression in SQLite 3.45.3 for Complex GROUP BY Queries with Views

Analysis of Query Execution Slowdown in Views with Aggregated Joins

Query Structure and Observed Regression Across SQLite Versions

The core issue revolves around a significant performance degradation observed when executing a specific query involving views, joins, and aggregation across SQLite versions 3.24.0, 3.41.2, and 3.45.3. The query creates a temporary table by aggregating results from a view (v_clean_tool__most_recent_non_seed_lookups) that joins two tables (lookups and urls) through an intermediate view (v_most_recent_lookup_per_url). The original execution time of ~120 seconds in SQLite 3.24.0 increased to over an hour in 3.41.2 and improved to ~220 seconds in 3.45.3 but remained nearly double the original baseline.

The v_most_recent_lookup_per_url view identifies the most recent retrieval_datetime for each url_id using MAX(retrieval_datetime) grouped by url_id. This view is then joined with the lookups table to fetch additional columns and further filtered by exp_content_type values. The final aggregation groups records by exp_content_type, c_hostname, and c_path to count occurrences.

Key schema elements contributing to complexity:

• lookups table: Contains 22 million records with indexes on content_hash and a foreign key to urls.
• urls table: Contains 2.9 million records with a UNIQUE constraint on url and hierarchical references (parent_id, source_seed_id).
• Views: Rely on joins between large tables without explicit indexing on critical columns like retrieval_datetime and url_id in lookups.

The regression is attributed to changes in SQLite’s query planner logic across versions, particularly optimizations involving view materialization and join reordering. The absence of an optimal index on lookups(retrieval_datetime, url_id) exacerbates the problem by forcing full scans or inefficient index usage during aggregation.

Impact of Query Planner Changes and Missing Indexes

Version-Specific Query Planner Behavior

SQLite’s query planner underwent significant changes between 3.24.0 and 3.45.3. A critical optimization introduced in later versions—view-scan optimization—attempted to flatten nested views into the main query to avoid materializing intermediate results. However, this optimization was partially rolled back in 3.44.0 due to regressions in specific scenarios.

In the original query, the join between v_most_recent_lookup_per_url and lookups uses implicit join conditions (USING (url_id, retrieval_datetime)). Versions prior to 3.41.2 may have materialized the view first, whereas newer versions attempted to merge the view into the main query, leading to inefficient execution plans. For example:

• 3.24.0: Materializes v_most_recent_lookup_per_url first, reducing the dataset before joining with lookups.
• 3.41.2: Merges the view into the main query, causing redundant scans of the lookups table.
• 3.45.3: Partially restores earlier behavior but still suffers from suboptimal join ordering.

Missing Index on retrieval_datetime and url_id

The v_most_recent_lookup_per_url view computes MAX(retrieval_datetime) for each url_id. Without an index on (retrieval_datetime, url_id), SQLite must scan all lookups records for each url_id to find the maximum datetime. This results in O(n²) complexity for large datasets. Adding this index allows the query planner to perform a covering index scan, reducing the operation to O(n log n).

Join Order and Materialization

The implicit JOIN between v_most_recent_lookup_per_url and lookups allows the query planner to reorder tables based on cost estimates. However, incorrect estimates in newer versions may prioritize scanning lookups first, leading to unnecessary repetition of the MAX() computation. Forcing a CROSS JOIN disables reordering, ensuring the view is materialized first.

Optimizing Query Performance through Indexing and Join Enforcement

Step 1: Add a Composite Index on lookups(retrieval_datetime, url_id)

Create an index to optimize the MAX(retrieval_datetime) aggregation in v_most_recent_lookup_per_url:

CREATE INDEX lookups_x1 ON lookups(retrieval_datetime, url_id);

This index allows SQLite to:

1. Scan lookups in order of url_id and retrieval_datetime.
2. For each url_id, quickly locate the maximum retrieval_datetime without scanning all rows.

Verification: Use EXPLAIN QUERY PLAN on the view definition to confirm the index is utilized.

Step 2: Modify the View to Use CROSS JOIN

Rewrite the v_clean_tool__most_recent_non_seed_lookups view to enforce materialization of v_most_recent_lookup_per_url before joining with lookups:

CREATE VIEW IF NOT EXISTS v_clean_tool__most_recent_non_seed_lookups AS
	SELECT
		lookup_spider,
		error_code,
		exp_content_type,
		u.*
	FROM
		v_most_recent_lookup_per_url
	CROSS JOIN lookups USING (url_id, retrieval_datetime)
	JOIN urls u USING (url_id)
	WHERE
	exp_content_type NOT IN ('first', 'second', 'third', 'fourth', 'fifth')
	OR exp_content_type IS NULL;

Rationale: CROSS JOIN prevents the query planner from reordering the join, ensuring v_most_recent_lookup_per_url is evaluated first. This reduces the number of rows processed in subsequent joins.

Step 3: Analyze and Compare Query Plans

Use EXPLAIN QUERY PLAN to compare execution plans across SQLite versions. Focus on:

• Join order: Verify that v_most_recent_lookup_per_url is materialized before joining with lookups.
• Index usage: Confirm that lookups_x1 is used for the MAX() aggregation.

Example diagnostic steps:

EXPLAIN QUERY PLAN
SELECT url_id, MAX(retrieval_datetime) 
FROM lookups 
GROUP BY url_id;

Ensure the output includes USING INDEX lookups_x1.

Step 4: Monitor Performance with Indexes and Join Enforcement

After applying the index and modifying the view, re-run the original CREATE TEMP TABLE query and measure execution time. Expected outcomes:

• 3.45.3: Execution time should drop below the original 120 seconds due to efficient index usage.
• 3.41.2: Despite the flawed view-scan optimization, the index reduces execution time significantly.

Step 5: Consider Additional Indexing on Filtered Columns

If exp_content_type filtering in v_clean_tool__most_recent_non_seed_lookups remains a bottleneck, add a partial index on lookups(exp_content_type):

CREATE INDEX lookups_x2 ON lookups(exp_content_type)
WHERE exp_content_type NOT IN ('first', 'second', 'third', 'fourth', 'fifth')
OR exp_content_type IS NULL;

This index pre-filters records, reducing the dataset size early in the query execution.

Conclusion

The regression stems from query planner changes interacting with missing indexes and suboptimal join ordering. By enforcing materialization of critical views and adding composite indexes, performance can be restored or improved beyond original baselines. Always validate execution plans with EXPLAIN QUERY PLAN when upgrading SQLite versions, as optimizer improvements may require schema adjustments to realize their benefits.