Index-only Scan
Description
Index-only scan is a query optimization technology in GaussDB. It can improve query performance by scanning only indexes without accessing table data. If the query condition involves only an index column of a table, you can use index-only scan to optimize the query. Index-only scan directly scans indexes, reducing I/O operations and CPU overhead and improving query performance.
Typical Scenarios
You only need to query the value of the index column without accessing other columns in the table. For example, query the maximum or minimum value of a column in a table, or query the number of different values in a column.
Examples
The target column contains only index columns.
-- Prepare data. gaussdb=# CREATE TABLE test2 (a int, b int); CREATE TABLE gaussdb=# CREATE INDEX test2_idx ON test2 (a, b); CREATE INDEX -- Randomly insert 1,000 data records. gaussdb=# INSERT INTO test2 VALUES(generate_series(1, 1000), generate_series(1, 1000)); INSERT 0 1000 -- Execution result. gaussdb=# EXPLAIN SELECT a, b FROM test2 WHERE a = 10 AND b = 20; QUERY PLAN ---------------------------------------------------------------------------- [Bypass] Index Only Scan using test2_idx on test2 (cost=0.00..4.27 rows=1 width=8) Index Cond: ((a = 10) AND (b = 20)) (3 rows) -- Drop the table. gaussdb=# DROP TABLE test2;
In the preceding example, the output of the index-only scan operator is as follows.
Item |
Description |
---|---|
Index-only Scan |
Operator name. |
Index Cond |
Filter predicate of the operator. In the example, the filter condition is that the value of column a is equal to 10 and the value of column b is equal to 20. When a query is executed, rows that meet these conditions are included in the final result set. |
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