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.
Item |
Description |
---|---|
Worktable Scan |
Operator name. |
Filter |
Filter condition of the operator. In the example, a is less than 10. |
Feedback
Was this page helpful?
Provide feedbackThank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.
For any further questions, feel free to contact us through the chatbot.
Chatbot