在WITH子句中添加无关表是否会减慢PostgreSQL中的查询速度？ [英] Does adding extraneous tables in a WITH clauses slow down a query in PostgreSQL?

问题描述

我有一个（可能）关于Postgres如何执行包含 WITH 子句的查询的基本问题。我想知道在 WITH 子句中是否包含无关的表是否会减慢查询速度。也就是说，如果从未在 WITH 子句的外部调用在 WITH 子句中创建的临时表，则为

I have a (possibly) basic question about how Postgres executes queries containing WITH clauses. I'm wondering whether including extraneous tables in a WITH clause actually slows down the query. That is, if the "temporary" table created in a WITH clause is never called outside of a WITH clause, is that "temporary" table actually created?

在第一个示例中，我将联接两个使用 WITH 子句：

In the first example, I am joining two "temporary" tables created using WITH clauses:

--Example 1
WITH temp1 as (
SELECT * from table_1
),
temp2 as (
select * from table_2
)
select * 
from temp1
join temp2;

在第二个示例中，我正在执行完全相同的查询，只是有一个无关的表 temp3在 WITH 子句中创建。

In the second example, I'm doing the exact same query except there is an extraneous table "temp3" created in the WITH clause.

--Example 2
WITH temp1 as (
SELECT * from table_1
),
temp2 as (
select * from table_2
),
temp3 as (
select * from table_3
)
select * 
from temp1
join temp2;

这两个查询之间是否存在性能差异？如果 table_3 是一个巨大的表，这会降低示例2与示例1中的查询速度吗？如果不是，为什么？

Is there any performance difference between these two queries? If table_3 is a huge table, will this slow down the query in example 2 vs. example 1? If not, why not?

似乎不影响查询时间。我仍然很好奇为什么，但是...

It seems like it does not affect the query time. I'm still curious as to why, though ...

推荐答案

您可以使用Explain来显示查询优化器将如何处理您的查询。

you can use Explain to show how the query optimizer will handle your query.

http：//www.postgresql .org / docs / 9.2 / static / sql-explain.html

在上述情况下，PSQL应该看到未使用temp3并且未将其包括在内。

In the case above PSQL should see that temp3 is not used and not include it.

使用上面的示例在一个我的数据库上。

using your example above on one my dbs.

explain with temp1 as (select * from cidrs), temp2 as (select * from contacts), temp3 as ( select * from accounts )  select * from temp1 join temp2 on temp1.id = temp2.id;
                             QUERY PLAN
---------------------------------------------------------------------
 Hash Join  (cost=22.15..25.44 rows=20 width=4174)
   Hash Cond: (temp1.id = temp2.id)
   CTE temp1
     ->  Seq Scan on cidrs  (cost=0.00..11.30 rows=130 width=588)
   CTE temp2
     ->  Seq Scan on contacts  (cost=0.00..10.20 rows=20 width=3586)
   ->  CTE Scan on temp1  (cost=0.00..2.60 rows=130 width=588)
   ->  Hash  (cost=0.40..0.40 rows=20 width=3586)
         ->  CTE Scan on temp2  (cost=0.00..0.40 rows=20 width=3586)
(9 rows)

您不会注意到temp3。在回答您的修改为何不影响查询时间的问题时，优化器足够聪明，可以看到它没有被使用，也不必费心计算。因此，它是优化程序的原因。

you will notice no mention of temp3. In answering your edit, about why it doesn't affect query time, the optimizer is smart enough to see that it isn't used and doesn't bother computing it. Hence the reason it is an optimizer.

这篇关于在WITH子句中添加无关表是否会减慢PostgreSQL中的查询速度？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！