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=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) 
(13 rows)

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