Performance Regression in SQLite 3.41 Views with Mixed Affinity Columns

Understanding the Performance Degradation in Compound Views with TEXT Columns and Literals

Issue Overview: Performance Regression in UNION ALL Views with TEXT Affinity Mismatches

A significant performance regression was observed in SQLite version 3.41.0 compared to 3.40.0 when executing queries against views that combine columns of TEXT affinity with string literals in a UNION ALL compound query. The regression manifests as a 10x slowdown in a minimal test case and a 1.7x slowdown in a more complex workload. The root cause lies in a change to the query planner’s "push-down optimization" logic introduced in commit 23f61a4ba8. This optimization is critical for efficiently resolving predicates (e.g., WHERE auth_name = 'EPSG' AND code = '4326') by pushing filter conditions into the subqueries of the view definition. When the affinity of columns in the UNION ALL arms mismatches—specifically, when one arm references a TEXT column and the other includes a string literal without explicit casting—the query planner disables the push-down optimization. This forces SQLite to scan the entire dataset for each query, drastically increasing execution time. Casting the literal to TEXT (e.g., CAST('projected' AS TEXT)) aligns the affinities of both arms, restoring the optimization and eliminating the regression.

The regression was particularly impactful in applications like the PROJ library’s test suite, where thousands of similar queries caused runtime to double (from 18 to 39 seconds). The issue was resolved in SQLite trunk via check-in aa6bd6dff7 and will be included in the 3.42.0 release. However, understanding the mechanics of this regression is essential for developers working with compound views, affinity mismatches, or SQLite upgrades.

Possible Causes: Affinity Mismatches in Compound Queries and Push-Down Optimization Constraints

The regression stems from two interrelated factors: SQLite’s type affinity system and a refinement to the push-down optimization logic.

1. SQLite’s Type Affinity System:
   SQLite assigns a "type affinity" to columns based on their declared type (e.g., TEXT, INTEGER). When values are inserted or compared, SQLite may convert them to match the column’s affinity. For example, a string literal like 'projected' has no inherent affinity unless explicitly cast. In the original view definition, the type column from geodetic_crs has TEXT affinity, while the literal 'projected' has no affinity. This creates an affinity mismatch between the two arms of the UNION ALL compound query.

2. Push-Down Optimization and Commit 23f61a4ba8:
   The push-down optimization allows SQLite to propagate WHERE clause predicates into subqueries, reducing the number of rows processed. Prior to commit 23f61a4ba8, this optimization was applied even if the compound query arms had differing affinities. The commit introduced a new restriction: for the optimization to apply, all arms of a compound query must share the same affinity. This change aimed to prevent incorrect results or inefficiencies when handling mixed-type data. However, it inadvertently disabled the optimization in cases where affinities were semantically compatible but technically mismatched (e.g., a TEXT column and an uncast string literal).

3. Compounded Impact on Query Execution:
   Without push-down optimization, SQLite must materialize the entire result set of the UNION ALL view before applying the WHERE clause. For large tables, this results in full scans of both geodetic_crs and projected_crs for every query. The performance penalty scales linearly with the number of queries, as seen in the PROJ test suite.

Troubleshooting Steps, Solutions, and Fixes: Resolving Affinity Mismatches and Restoring Performance

To address this regression, developers must ensure consistent affinities across compound query arms and leverage SQLite’s latest fixes. Below is a structured approach:

1. Identify Affinity Mismatches in Views:
   Examine the schema and view definitions for implicit type mismatches. Use PRAGMA table_info(table_name); to inspect column affinities. For compound queries, verify that all SELECT arms return columns with matching affinities. In the example, the type column (affinity: TEXT) and the 'projected' literal (no affinity) constitute a mismatch.

2. Explicitly Cast Literals to Match Column Affinity:
   Modify the view definition to cast literals to the target affinity. For example:

   CREATE TEMP VIEW VV AS
     SELECT auth_name, code, type FROM geodetic_crs
     UNION ALL
     SELECT auth_name, code, CAST('projected' AS TEXT) FROM projected_crs;
   

   This aligns the affinity of both arms to TEXT, enabling the push-down optimization.

3. Update to SQLite 3.42.0 or Later:
   The fix in check-in aa6bd6dff7 relaxes the affinity-matching requirement by allowing the optimization if the affinities can be coerced to a common type. Upgrade to a version containing this fix to avoid manual casting in view definitions.

4. Analyze Query Plans with EXPLAIN:
   Use EXPLAIN or EXPLAIN QUERY PLAN to diagnose whether push-down optimization is applied. Compare outputs between SQLite versions. In 3.41.0, a missing SEARCH step (e.g., SCAN instead) indicates the optimization is disabled. After applying fixes, the plan should show SEARCH with indexed lookups.

5. Schema Design Best Practices:
   Avoid mixing columns and literals with differing affinities in compound queries. Prefer explicit casts or derived columns with uniform types. For example, define a type column in projected_crs instead of using a literal.

6. Monitor Performance in Upgrade Pipelines:
   Integrate performance regression tests into CI/CD pipelines when upgrading SQLite. Use tools like hyperfine to benchmark critical queries before and after upgrades.

By addressing affinity mismatches and staying updated with SQLite releases, developers can mitigate this performance regression and ensure efficient query execution.