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
---------------------------------------------------------------------------
 Streaming (type: GATHER)  (cost=48.89..85.84 rows=721 width=4)
   Node/s: All datanodes
   ->  CTE Scan on tt  (cost=0.00..7.20 rows=721 width=4)
         ->  Recursive Union  (cost=0.00..44.89 rows=721 width=4)
               ->  Seq Scan on t9  (cost=0.00..13.13 rows=20 width=4)
               ->  WorkTable Scan on tt  (cost=0.00..2.45 rows=70 width=4)
                     Filter: (a < 10)
(7 rows)

-- Drop.
gaussdb=#DROP TABLE t9;

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