与子查询相比，SQL 查询使用 With 需要更长的时间

Question

我有点困惑，为什么使用 with 子句的简单 SQL 查询比将它们放在子查询中花费的时间要长得多。我的 IDE 上的一个实例用于 >1000 万条记录，使用 with 子句运行 >30 分钟，使用子查询仅运行 <10 秒。

下面仅举一个简单的例子:

with table1 as (select tb1.a, tb1.b, tb2.c, tb3.d
                from tablea
                where a > 0 and b = 2016)
     table2 as (select a, b, c, d, e, f
                from table1 tb1
                left join table2 tb2 on tb1.a=tb2.a
                left join table3 tb3 on tb1.b=tb2.b)
select * from table2

与将它们作为子查询相比:

select * from (select a, b, c, d, e, f
                from (select tb1.a, tb1.b, tb2.c, tb3.d
                      from tablea
                      where a > 0 and b = 2016) tb1
                left join table2 tb2 on tb1.a=tb2.a
                left join table3 tb3 on tb1.b=tb2.b) table2

后一个查询比前一个查询完成得快得多。但是，前者在查询结构上更容易理解，所以如果可能的话我更喜欢后者。

请问是我在解释时使用的IDE(DBeaver)造成的巨大差异，还是基于SQL语句逻辑本身？

谢谢。

Answer 1

这是因为在 Postgres 中，CTE 充当优化障碍。一种解决方法(具有几乎相同的句法结构)是用 TEMP VIEWs 替换 CTE:

---

CREATE TEMP VIEW v1 AS
        SELECT ta.a, ta.b, ta.c, ta.d
        FROM tablea ta
        WHERE a > 0 and b = 2016
        ;
CREATE TEMP VIEW v2 AS
        SELECT a, b, c, d, e, f
        FROM v1
        LEFT JOIN table2 tb2 ON v1.a=tb2.a
        LEFT JOIN table3 tb3 ON v1.b=tb3.b
        ;
SELECT * from v2;