TID Scan

Description

This is a row number scan operator, which uses the row number (ctid) to filter and return the tuples. In the Astore scenario, data is stored in the heap page by row. In addition to the value of a storage column, the corresponding row number is also stored in the B-tree index. Therefore, GaussDB supports quick retrieval by row number.

The row number is in the following format:

(page_number, item_number)      -- page_number starts from 0, and the value of item_number starts from 1.

The corresponding tuple in the disk or cache is found based on the given ctid value.

Typical Scenarios

Prerequisites: The GUC parameter enable_tidscan has been set to on.

You can quickly search for data by specifying a valid row number if a table supports TID scan. Currently, GaussDB selects TID scan only when the WHERE condition is =. IN is not supported.

Examples

-- Prepare data.
gaussdb=# CREATE TABLE t_tid_test(a numeric,b numeric,c numeric);
CREATE TABLE 

gaussdb=# DECLARE 
    n1 numeric := 10; 
    n2 numeric := 0; 
    n3 numeric := 100; 
BEGIN 
    WHILE n1 > 0 LOOP 
        WHILE n2 < 100 LOOP 
            WHILE n3 > 0 LOOP 
                INSERT INTO t_tid_test VALUES(n1, n2, n3); 
                n3 := n3 - 1; 
            END LOOP; 
            n2 := n2 + 1; 
        END LOOP; 
        n1 := n1 - 1; 
    END LOOP; 
END; 
/
ANONYMOUS BLOCK EXECUTE

-- Execution result.
gaussdb=# SET enable_tidscan = on; 
SET
gaussdb=# EXPLAIN ANALYZE SELECT * FROM t_tid_test WHERE ctid = '(0,10)'::tid; 
                                             QUERY PLAN 
----------------------------------------------------------------------------------------------------- 
 Tid Scan on t_tid_test  (cost=0.00..4.01 rows=1 width=14) (actual time=0.016..0.016 rows=1 loops=1) 
   TID Cond: (ctid = '(0,10)'::tid) 
 Total runtime: 0.077 ms 
(3 rows)

-- Drop the table.
gaussdb=# DROP TABLE t_tid_test;

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