更新时间:2025-05-29 GMT+08:00

Cte Scan

算子说明

CTE(Common Table Expression)是一种临时表达式,Cte Scan用于扫描CTE表达式生成的临时表。

在GaussDB中,可以通过使用with关键字来指定一个或多个CTE,然后在后续的查询中多次使用。

典型场景

当一个查询结果集需要在后续的查询中多次使用的时候,可以考虑使用CTE来避免多次计算。

示例

示例:带WITH语句的无法改写为子查询的CTE。

--数据准备。 
gaussdb=# CREATE TABLE employees(deptid integer, salary number); 
CREATE TABLE 
gaussdb=# CREATE TABLE managers(deptid integer); 
CREATE TABLE

gaussdb=# SET max_datanode_for_plan = 2;
SET
--执行结果。 
gaussdb=# EXPLAIN WITH table_b AS (SELECT deptid,sum(salary) dept_salary FROM employees GROUP BY deptid)  
SELECT m.deptid, b.dept_salary 
FROM managers m,table_b b,table_b c 
WHERE m.deptid = b.deptid AND  m.deptid = c.deptid; 

                                                                                                                                QUERY PLAN

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------
 Data Node Scan  (cost=0.00..0.00 rows=0 width=0)
   Node/s: All datanodes

 Remote SQL: WITH table_b AS (SELECT employees.deptid, sum(employees.salary) AS dept_salary FROM public.employees GROUP BY employees.deptid) SELECT m.deptid, b.dept_salary FROM public.managers m, ta
ble_b b, table_b c WHERE m.deptid = b.deptid AND m.deptid = c.deptid
 Datanode Name: datanode1
   Hash Join  (cost=46.32..113.37 rows=2402 width=36)
     Hash Cond: (m.deptid = b.deptid)
     CTE table_b
       ->  HashAggregate  (cost=28.57..30.57 rows=200 width=36)
             Group By Key: employees.deptid
             ->  Seq Scan on employees  (cost=0.00..22.38 rows=1238 width=36)
     ->  Seq Scan on managers m  (cost=0.00..34.02 rows=2402 width=4)
     ->  Hash  (cost=13.25..13.25 rows=200 width=40)
           ->  Hash Join  (cost=6.50..13.25 rows=200 width=40)
                 Hash Cond: (b.deptid = c.deptid)
                 ->  CTE Scan on table_b b  (cost=0.00..4.00 rows=200 width=36)
                 ->  Hash  (cost=4.00..4.00 rows=200 width=4)
                       ->  CTE Scan on table_b c  (cost=0.00..4.00 rows=200 width=4)

 Datanode Name: datanode2
   Hash Join  (cost=46.32..113.37 rows=2402 width=36)
     Hash Cond: (m.deptid = b.deptid)
     CTE table_b
       ->  HashAggregate  (cost=28.57..30.57 rows=200 width=36)
             Group By Key: employees.deptid
             ->  Seq Scan on employees  (cost=0.00..22.38 rows=1238 width=36)
     ->  Seq Scan on managers m  (cost=0.00..34.02 rows=2402 width=4)
     ->  Hash  (cost=13.25..13.25 rows=200 width=40)
           ->  Hash Join  (cost=6.50..13.25 rows=200 width=40)
                 Hash Cond: (b.deptid = c.deptid)
                 ->  CTE Scan on table_b b  (cost=0.00..4.00 rows=200 width=36)
                 ->  Hash  (cost=4.00..4.00 rows=200 width=4)
                       ->  CTE Scan on table_b c  (cost=0.00..4.00 rows=200 width=4)

(34 rows)


--删除表。
gaussdb=# DROP TABLE employees, managers;

上述示例中,CTE Scan算子输出信息如下所示。

信息名称

含义

CTE Scan

算子的名称。

Hash

内表创建hash table的算子。