Physical ordering and ctid

• Physical layout on the disk
   
• Pages and tuples
   
• CLUSTER and REINDEX

CREATE TABLE ctid_order ( a INTEGER, b INTEGER );

INSERT INTO ctid_order(a,b) VALUES (1,5);
INSERT INTO ctid_order(a,b) VALUES (2,2);
INSERT INTO ctid_order(a,b) VALUES (3,1);
INSERT INTO ctid_order(a,b) VALUES (4,4);
INSERT INTO ctid_order(a,b) VALUES (5,3);

SELECT a, oid, ctid FROM ctid_order;

 a |   oid   | ctid  
---+---------+-------
 1 | 8203874 | (0,1)
 2 | 8203875 | (0,2)
 3 | 8203876 | (0,3)
 4 | 8203877 | (0,4)
 5 | 8203878 | (0,5)

CREATE INDEX tid_b ON ctid_order(b);

CLUSTER tid_b ON ctid_order;

SELECT a, b, oid, ctid FROM ctid_order;

 a | b |   oid   | ctid  
---+---+---------+-------
 3 | 1 | 8203876 | (0,1)
 2 | 2 | 8203875 | (0,2)
 5 | 3 | 8203878 | (0,3)
 4 | 4 | 8203877 | (0,4)
 1 | 5 | 8203874 | (0,5)

SELECT a, b, oid, ctid FROM ctid_order ORDER BY a;

 a | b |   oid   | ctid  
---+---+---------+-------
 1 | 5 | 8203874 | (0,5)
 2 | 2 | 8203875 | (0,2)
 3 | 1 | 8203876 | (0,1)
 4 | 4 | 8203877 | (0,4)
 5 | 3 | 8203878 | (0,3)

INSERT INTO ctid_order(a,b) VALUES (6,3);

SELECT a, b, oid, ctid FROM ctid_order;

 a | b |   oid   | ctid  
---+---+---------+-------
 3 | 1 | 8203876 | (0,1)
 2 | 2 | 8203875 | (0,2)
 5 | 3 | 8203878 | (0,3)
 4 | 4 | 8203877 | (0,4)
 1 | 5 | 8203874 | (0,5)
 6 | 3 | 8203883 | (0,6)

• Physical layout - directory structure
   
• Amount per "page"
   
• pg_class (relfilenode)
   
• Other considerations

Previous: 'Previous and Next' Query         [Table of Contents]         Next: 'Multi Search' Query