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                                  
---------------------------------------------------------------------------- 
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.