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.