Worktable Scan

Description

Worktable scan is a memory-based query optimization technology. It can cache query results in the memory to improve query performance. When GaussDB executes a query statement, it stores the query result in a temporary table and uses worktable scan to scan the temporary table to obtain the query result. Worktable scan reduces disk I/O operations and improves query performance. However, it also has some disadvantages, for example, a large amount of memory space is consumed, and a problem such as memory overflow may be caused. Therefore, you need to adjust and optimize the worktable scan according to actual situations.

Typical Scenarios

• Recursive query: In the WITH RECURSIVE scenario, worktable scan can cache the query result of each recursion and use it as the data source input for the next recursion.

Examples

Example: Recursive Union tables without indexes

-- Prepare data.
gaussdb=# CREATE TABLE t9(a int); 
CREATE TABLE 
gaussdb=# INSERT INTO t9 VALUES(1); 
INSERT 0 1

-- Execution result.
gaussdb=# EXPLAIN WITH RECURSIVE tt AS ( 
SELECT a FROM t9 
UNION ALL 
SELECT a + 1 FROM tt WHERE a < 10) 
SELECT * FROM tt; 
                                QUERY PLAN                                  
---------------------------------------------------------------------------- 
CTE Scan on tt (cost=7288.14..8937.58 rows=82472 width=4) 
  CTE tt 
  -> Recursive Union (cost=0.00..7288.14 rows=82472 width=4) 
    -> Seq Scan on t9 (cost=0.00..34.02 rows=2402 width=4) 
    -> WorkTable Scan on tt (cost=0.00..560.47 rows=8007 width=4) 
         Filter: (a < 10) 
(6 rows)

-- Drop.
gaussdb=# DROP TABLE t9;

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