Cte Scan

Description

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

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

Example: 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

-- 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
--------------------------------------------------------------------------------------------------------------
 Hash Join  (cost=1.15..1.81 rows=20 width=36)
   Hash Cond: (c.deptid = m.deptid)
   CTE table_b
     ->  Data Node Scan on employees "_REMOTE_TABLE_QUERY_"  (cost=0.00..0.00 rows=20 width=36)
           Node/s: All datanodes
   ->  CTE Scan on table_b c  (cost=0.00..0.40 rows=20 width=4)
   ->  Hash  (cost=0.90..0.90 rows=20 width=40)
         ->  Hash Join  (cost=0.25..0.90 rows=20 width=40)
               Hash Cond: (b.deptid = m.deptid)
               ->  CTE Scan on table_b b  (cost=0.00..0.40 rows=20 width=36)
               ->  Hash  (cost=0.00..0.00 rows=20 width=4)
                     ->  Data Node Scan on managers "_REMOTE_TABLE_QUERY_"  (cost=0.00..0.00 rows=20 width=4)
                           Node/s: All datanodes
(13 rows)

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