在PostgreSQL中哪个是更好的CTE或内部选择 [英] Which is better CTE or Inner-Selects in PostgreSQL

问题描述

下面给出的是我的两个不同查询执行的示例，它们具有相同的结果

Given below is a sample of my two different query execution with same result

select 内部选择>查询。

The select query using inner-select.

   select p.product from
   (
      select * from tbl_a where productid not in (select productid from tbl_b)
   ) p 
   order by p.product

并使用 CTE 选择查询。

   with cte as 
   (
   select * from tbl_a where productid not in (select productid from tbl_b)
   )
   select product from cte order by product

• 所以我的问题是其中哪一个对用？以及在什么条件下可以使用它们？


• So my question is which among the above is good to use? And At what condition can use each of them?

推荐答案

Postgres总是可以实现的CTE，这意味着CTE版本具有读取和写入数据的额外开销。 （注意：在其他数据库中不一定是正确的。）Postgres不会（必要）实现子查询，因此该版本应该更快。

Postgres always materializes CTEs, which means that the CTE version has additional overhead for reading and writing the data. (Note: this is not necessarily true in other databases.) Postgres does not (necessarily) materialize subqueries, so that version should be faster.

一种更好的编写方式查询将完全放弃子查询/ CTE：

A better way to write this query is to dispense with the subquery/CTE entirely:

  select a.product
  from tbl_a a
  where a.productid not in (select productid from tbl_b)
  order by a.product;

（我忽略了不存在还是左外部联接实际上比不在更好。）

(I am ignoring whether not exists or left outer join would actually be better than not in.)

在编写查询时，没有使用CTE而不是子查询的一般规则。这取决于很多因素，尤其是是否对基础表建立索引以及CTE在查询中出现多少次。

There is not a general rule for using CTEs instead of subqueries when writing queries. It depends on a lot of factors, especially whether the underlying tables are indexed and how many times the CTE would appear in the query.

这篇关于在PostgreSQL中哪个是更好的CTE或内部选择的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！