关于SQLite查询规划器的困惑：视图查询性能差异问题

问题分析 #

你遇到的核心问题是：SQLite查询规划器无法将外层查询的WHERE条件（CHOICE2）下推到包含GROUP BY和CASE逻辑的复杂视图V_Order_Calculator3中。这导致CHOICE2会先全量计算视图的分组结果（处理150万条记录），再在外层过滤单条数据，因此耗时远超CHOICE1（先过滤再分组）。

可行解决方案 #

1. 手动内联视图逻辑并下推条件 #

直接将视图的定义替换到查询中，把过滤条件放到视图对应的基础表查询里，这是最直接有效的方法：

SELECT 
    order_id
FROM 你的基础订单表 -- 替换为V_Order_Calculator3依赖的原始订单表
WHERE order_id = '00002092-03b4-4661-a9f4-afa73984860a'
GROUP BY
(CASE 
    WHEN order_type IN ('webshop_sell','sell','buy') THEN order_id
    WHEN order_type IN ('sell_return','buy_return')  THEN order_linked_id
    ELSE 0
END)

这种方式让查询规划器先过滤出目标订单，再执行分组，效率和CHOICE1完全一致。

2. 使用非物化CTE（SQLite 3.35.0+） #

如果需要保留视图的复用性，可以用NOT MATERIALIZED修饰CTE，提示SQLite不要提前物化整个结果集，而是尝试将外层条件下推到CTE内部：

WITH order_groups AS NOT MATERIALIZED (
    SELECT 
        order_id
    FROM V_Order_Calculator3 
    GROUP BY
    (CASE 
        WHEN order_type IN ('webshop_sell','sell','buy') THEN order_id
        WHEN order_type IN ('sell_return','buy_return')  THEN order_linked_id
        ELSE 0
    END)
)
SELECT * FROM order_groups WHERE order_id = '00002092-03b4-4661-a9f4-afa73984860a'

注意：此功能需要SQLite版本≥3.35.0，若版本较低需先升级。

3. 优化视图定义 #

检查V_Order_Calculator3的定义，移除查询不需要的字段、过滤条件或复杂逻辑，简化后的视图更易被查询规划器优化，提升条件下推的可能性。

4. 验证执行计划 #

用EXPLAIN QUERY PLAN对比两种查询的执行流程，确认条件下推情况：

-- 查看CHOICE1的执行计划
EXPLAIN QUERY PLAN
SELECT 
    order_id
FROM V_Order_Calculator3 
WHERE order_id = '00002092-03b4-4661-a9f4-afa73984860a'
GROUP BY
(CASE 
    WHEN order_type IN ('webshop_sell','sell','buy') THEN order_id
    WHEN order_type IN ('sell_return','buy_return')  THEN order_linked_id
    ELSE 0
END);

-- 查看CHOICE2的执行计划
EXPLAIN QUERY PLAN
select * from (
    SELECT 
        order_id
    FROM V_Order_Calculator3 
    GROUP BY
    (CASE 
        WHEN order_type IN ('webshop_sell','sell','buy') THEN order_id
        WHEN order_type IN ('sell_return','buy_return')  THEN order_linked_id
        ELSE 0
    END)
) a where a.order_id = '00002092-03b4-4661-a9f4-afa73984860a';

对比结果会发现，CHOICE1的计划中先出现SEARCH TABLE（过滤），再USE TEMP B-TREE FOR GROUP BY；而CHOICE2则先SCAN TABLE（全量读取）再分组，最后过滤。

内容的提问来源于stack exchange，提问作者Pooya Sarraf