PostgreSQL 9.0 High Performance

Assembling row sets

To understand how to optimize the way a query runs, you have to first understand the options for how it can be executed. Now that you're armed with some basics on how nodes fit together and costs are computed, the next stage to understanding how queries work is to see the options for bottom level plan nodes that are usually selecting rows.

Tuple id

Each row in the database has a tuple id, a number visible as the system column named ctid in each row. You can use these to look up a row:

SELECT ctid,customerid FROM customers limit 3;
 ctid  | customerid 
-------+------------
 (0,1) |          1
 (0,2) |          2
 (0,3) |          3

EXPLAIN SELECT customerid FROM customers WHERE ctid='(0,1)';
                       QUERY PLAN                        
--------------------------------------------------------- ...