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

• Large table query: When a large table is queried, WorkTable scan can divide the query result into multiple work units. Each work unit processes a part of data, improving the query efficiency.
• Multi-table join query: When multiple tables need to be associated for a query, WorkTable scan can divide the data of each table into multiple work units. Each work unit processes a part of data, reducing the data volume of join operations and improving query efficiency.
• Partitioned table query: When a partitioned table is queried, WorkTable scan can divide the data of each partition into multiple work units. Each work unit processes a part of the data, improving the query efficiency.
• Statistics query of a large amount of data: When a large amount of data needs to be queried, WorkTable scan can divide the data into multiple work units. Each work unit processes a part of the data, improving the query efficiency.

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.