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

-- Collect statistics.
gaussdb=#ANALYZE t_tid_test;

-- Execution result.
gaussdb=#SET enable_tidscan = on; 
SET
gaussdb=# SET max_datanode_for_plan = 2;
SET
gaussdb=# EXPLAIN SELECT * FROM t_tid_test WHERE ctid = '(0,10)'::tid;
                                  QUERY PLAN
------------------------------------------------------------------------------
 Data Node Scan  (cost=0.00..0.00 rows=0 width=0)
   Node/s: All datanodes

 Remote SQL: SELECT a, b, c FROM public.t_tid_test WHERE ctid = '(0,10)'::tid
 Datanode Name: datanode1
   Seq Scan on t_tid_test  (cost=0.00..2.25 rows=1 width=13)
     Filter: (ctid = '(0,10)'::tid)

 Datanode Name: datanode2
   Tid Scan on t_tid_test  (cost=0.00..4.01 rows=1 width=96)
     TID Cond: (ctid = '(0,10)'::tid)

(12 rows)

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

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