Postgres CTE 是否会读取所有行，即使它不需要？

Question

这样的查询会读取big_table的所有行还是实际需要的行？

WITH tmp AS (SELECT * FROM big_table)
SELECT * FROM
    small_table st
JOIN tmp ON tmp.Id = st.BigTableId
WHERE st.Id = 45

子选择会表现更好吗？

SELECT * FROM
    small_table st
JOIN (SELECT * FROM big_table) as tmp ON tmp.Id = st.BigTableId
WHERE st.Id = 45

我喜欢使用WITH编写更多可读性查询，但如果成本的性能较差。

Answer 1

在问题中所述的情况下，用CTE的情况下，CTE中的查询是自主执行的（没有连续查询的任何条件），CTE查询将比另一个情况慢得多。

在您有疑问的情况下，请使用解释分析。您应该得到类似于以下计划的计划。

使用CTE（大表有100000行，所有这些行都将在CTE中进行扫描）：

 Hash Join  (cost=1580.96..4269.53 rows=565 width=12) (actual time=10.349..42.718 rows=1 loops=1)
   Hash Cond: (tmp.id = st.bigtableid)
   CTE tmp
     ->  Seq Scan on big_table  (cost=0.00..1572.65 rows=112965 width=4) (actual time=0.011..11.813 rows=100000 loops=1)
   ->  CTE Scan on tmp  (cost=0.00..2259.30 rows=112965 width=4) (actual time=0.013..33.524 rows=100000 loops=1)
   ->  Hash  (cost=8.30..8.30 rows=1 width=8) (actual time=0.013..0.013 rows=1 loops=1)
         Buckets: 1024  Batches: 1  Memory Usage: 9kB
         ->  Index Scan using small_table_pkey on small_table st  (cost=0.28..8.30 rows=1 width=8) (actual time=0.009..0.009 rows=1 loops=1)
               Index Cond: (id = 45)

没有CTE：

 Nested Loop  (cost=0.57..16.61 rows=1 width=12) (actual time=0.069..0.071 rows=1 loops=1)
   ->  Index Scan using small_table_pkey on small_table st  (cost=0.28..8.29 rows=1 width=8) (actual time=0.008..0.008 rows=1 loops=1)
         Index Cond: (id = 45)
   ->  Index Only Scan using big_table_pkey on big_table  (cost=0.29..8.31 rows=1 width=4) (actual time=0.056..0.056 rows=1 loops=1)
         Index Cond: (id = st.bigtableid)
         Heap Fetches: 1

请注意，您不需要第二次查询中的子查询。它将自动对此进行优化：

SELECT * 
FROM small_table st
JOIN big_table as tmp ON tmp.Id = st.BigTableId
WHERE st.Id = 45