在Greenplum中实现公用表表达式 [英] Materialize Common Table Expression in Greenplum

问题描述

是否有一种方法可以强制Greenplum PostgreSQL在 WITH 子句中实现子查询，例如 MATERIALIZE 和 INLINE 优化程序提示在Oracle中执行以下操作吗？

Is there a way to force Greenplum PostgreSQL to materialize a subquery in a WITH clause like what MATERIALIZE and INLINE optimizer hints do as below in Oracle?

WITH dept_count AS (
  SELECT /*+ MATERIALIZE */ deptno, COUNT(*) AS dept_count
  FROM   emp
  GROUP BY deptno)
SELECT ...

我已经搜索了一段时间，只是在Oracle中找到此功能。

I've been searching this for a while, only to find this functionality in Oracle.

我知道我可以使用 CREATE TABLE AS ，但我有几个类似的查询，迫使我在每次查询后都删除临时表，这非常不方便，而且效率低下。

I know I can use CREATE TABLE AS, but I have several similar queries, forcing me to drop the temporary table after each query, which is very inconvenient and maybe inefficient.

更新：
我测试了下表：

Update: I tested the following table:

CREATE TABLE test (id: INT);

EXPLAIN WITH test2 AS (SELECT id FROM test)
SELECT COUNT(*) FROM test2;

                                 QUERY PLAN                                     
------------------------------------------------------------------------------------
Aggregate  (cost=0.36..0.37 rows=1 width=8)
   ->  Gather Motion 32:1  (slice1; segments: 32)  (cost=0.01..0.35 rows=1 width=8)
         ->  Aggregate  (cost=0.01..0.01 rows=1 width=8)
               ->  Subquery Scan test2  (cost=0.00..0.00 rows=1 width=0)
                     ->  Seq Scan on test  (cost=0.00..0.00 rows=1 width=4)

我使用Greenplum Postgresql 8.2

I'm using Greenplum Postgresql 8.2

推荐答案

临时表

对于在 session 期间持续存在的临时表，请使用实际的 TEMPORARY TABLE 。

Temporary table

If you are looking for a temporary table that persists for the duration of your session, use an actual TEMPORARY TABLE.

CREATE TEMPORARY TABLE t AS
SELECT deptno, COUNT(*) AS dept_count
FROM   emp
GROUP  BY deptno;

SELECT ...
FROM t ...

创建[临时]表AS

但是，PostgreSQL中没有全局临时表。临时表仅对创建它的用户可见，并且仅在创建它的会话期间可见。

However, there is no "global" temporary table in PostgreSQL. A temporary tables is only visible to the user who created it and only for the duration of the session it was created in.

CTE 仅在它们所属的查询中可见。

CTEs are only visible in the query they are part of. Never beyond that.

将临时表的可见性限制为一个查询，将它们放入事务中并添加 COMCOMM DROP ，它将在事务结束时自动删除临时表：

To restrict the visibility of your temp tables to a single query, put them into a transaction and add ON COMMIT DROP, which drops the temp table automatically at the end of the transaction:

BEGIN;
CREATE TEMP TABLE t ON COMMIT DROP AS
SELECT ...

我可以想到的唯一用例在哪里有意义：如果要在巨大的临时表上创建索引：

The only use case I can think of, where this would make sense: if you want to create indexes on a huge temporary table:

CREATE INDEX ON t(col1);

SELECT ..
FROM t ...;

ROLLBACK;

或者（在这里没有区别）：

Or (doesn't make a difference here):

COMMIT;

如果使用 ROLLBACK ，还可以只需使用没有 ON COMMIT DROP 的临时表，因为所有内容都会回滚。

If you use ROLLBACK, you can also just use a temp table without ON COMMIT DROP since everything is rolled back anyways.

这篇关于在Greenplum中实现公用表表达式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！