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

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..56.78 rows=716 width=8)
   Index Cond: (c1 > 990000)
(3 rows)

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

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) */ c2 FROM test WHERE c1 = 10 GROUP BY c2;
                                   QUERY PLAN
---------------------------------------------------------------------------------
 HashAggregate  (cost=31897.47..31897.49 rows=2 width=4)
   Group By Key: c2
   ->  Index Scan using c1_idx on test  (cost=0.00..31866.82 rows=12261 width=4)
         Index Cond: (c1 = 10)
(4 rows)

Example 4: In the JOIN operation, indexes can be used for matching.

gaussdb=#  EXPLAIN SELECT /*+ indexscan(t1 c1_idx) */ t1.*, t2.* FROM test t1 JOIN test t2 on t1.c1 = t2.c1;
                                      QUERY PLAN
---------------------------------------------------------------------------------------
 Hash Join  (cost=111134.00..786942.61 rows=4996829 width=16)
   Hash Cond: (t1.c1 = t2.c1), (LLVM Optimized)
   ->  Index Scan using c1_idx on test t1  (cost=0.00..146659.32 rows=3000000 width=8)
   ->  Hash  (cost=41407.00..41407.00 rows=3000000 width=8)
         ->  Seq Scan on test t2  (cost=0.00..41407.00 rows=3000000 width=8)
(5 rows)
-- Drop.
gaussdb=# DROP TABLE test;