Updated on 2025-03-13 GMT+08:00

Index Scan

Description

In an index scan, the database uses the index column specified by a statement to retrieve rows by traversing the index tree. When the database scans an index for a value, the value to be searched for can be found after n I/Os occur, where n is the height of the B-tree index. Generally, index scan is used to retrieve table data. The database reads the index block sequentially, finds the corresponding index key value, and then reads the corresponding table tuple based on the TID corresponding to the index key. When the data volume is large but the query result set is small, the index scan efficiency is higher than the Seq Scan efficiency.

Typical Scenarios

  • Querying specific rows in a table: When a query statement contains a WHERE clause whose conditions can be matched by index columns, GaussDB uses the index scan to search for rows that meet the conditions.
  • Sorting: When a query statement contains the ORDER BY clause whose columns can be sorted by index, GaussDB uses the index scan for sorting.
  • Aggregating: When a query statement contains the GROUP BY clause whose columns can be grouped by index, GaussDB uses the index scan for aggregation.
  • Connecting: When a query statement contains a JOIN operation whose columns can be matched by index, GaussDB uses the index scan to perform the join operation.

Examples

Example 1: Conditions in the WHERE clause can be matched by index columns.

-- Prepare data.
gaussdb=# CREATE TABLE test (c1 int, c2 int); 
CREATE TABLE 
gaussdb=# CREATE INDEX c1_idx ON test (c1); 
CREATE INDEX 
gaussdb=# INSERT INTO test SELECT generate_series(1, 1000000), random()::integer; 
INSERT 0 1000000
gaussdb=# INSERT INTO test SELECT generate_series(1, 1000000), random()::integer; 
INSERT 0 1000000
gaussdb=# INSERT INTO test SELECT generate_series(1, 1000000), random()::integer; 
INSERT 0 1000000

-- Collect statistics.
gaussdb=# ANALYZE test;
gaussdb=# EXPLAIN SELECT /*+ indexscan(test c1_idx) */ * FROM test WHERE c1 > 990000;
                               QUERY PLAN        
 [Bypass]
 Index Scan using c1_idx on test  (cost=0.00..344.17 rows=9767 width=8)
   Index Cond: (c1 > 990000)
(3 rows)

In the preceding example, the output of the index scan operator is as follows.

Item

Description

Index Scan

Operator name.

Index Cond

Filter predicate of the operator. In the example, the filter condition is the value of column c1 is greater than 990000. When a query is executed, rows that meet these conditions are included in the final result set.

Example 2: Columns in the ORDER BY clause can be sorted by index.

gaussdb=# EXPLAIN SELECT /*+ indexscan(test c1_idx) */ * FROM test ORDER BY c1;
                                  QUERY PLAN  
-------------------------------------------------------------------------------
 [Bypass]
 Index Scan using c1_idx on test  (cost=0.00..3815878.82 rows=2451905 width=8)
(2 rows)

Example 3: Columns in the GROUP BY clause can be grouped by index.

gaussdb=# EXPLAIN SELECT /*+ indexscan(test c1_idx) */ c1 FROM test GROUP BY c1;
                                       QUERY PLAN
----------------------------------------------------------------------------------------
 Group  (cost=0.00..102640.59 rows=200 width=4)
   Group By Key: c1
   ->  Index Only Scan using c1_idx on test  (cost=0.00..96510.82 rows=2451905 width=4)
(3 rows)

-- Drop.
gaussdb=# DROP TABLE test;