CTE Scan

Description

Common table expression (CTE) is a temporary expression. CTE scan is used to scan temporary tables generated by CTEs.

In GaussDB, you can use the WITH keyword to specify one or more CTEs, which can be used multiple times in subsequent queries.

Typical Scenarios

When a query result set needs to be used for multiple times in subsequent queries, you can use CTE to avoid multiple calculations.

Examples

CTEs with the WITH statement cannot be rewritten as subqueries.

-- Prepare data.
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
-- Execution result.
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)


-- Drop the table.
gaussdb=#DROP TABLE employees, managers;

In the preceding example, the output of the CTE scan operator is as follows.