I have this table in PostgreSQL 11:
CREATE TABLE A (id bigint PRIMARY KEY, text text)
Now I want to fetch the ctid
of rows meeting some condition like id = 123
.
However, even using pg_hint_plan
to hint PostgreSQL to use Index Only Scan, it will always give me Index Scan:
/*+ IndexOnlyScan(a) */ explain analyze select ctid from a where id = 823977776533426178;
QUERY PLAN
----------------------------------------------------------------------------------------------------------
Index Scan using a_pkey on a (cost=0.14..8.16 rows=1 width=6) (actual time=0.038..0.039 rows=1 loops=1)
Index Cond: (id = '823977776533426178'::bigint)
Planning Time: 0.122 ms
Execution Time: 0.095 ms
(4 rows)
My understanding is that ctid
is the physical row id (block_no: record_offset) of each row and it must be included in any btree index, since it needs this information to fetch the row from heap file.
Then why can't an index-only scan return the ctid
directly?
Is it just that PostgreSQL does not implement it that way?
Best Answer
Your understanding is almost but not quite correct. True, every btree index tuple needs a
ctid
(or some form of block number and tuple index) to point to the heap tuple (table row). But (at least) since the introduction of "heap-only tuples" with Postgres 8.3, there may be a HOT chain to follow to arrive at the current live tuple in the snapshot - with a differentctid
than the one stored in the index.Since it is also not possible to use the
ctid
(or any system column) as index expression, it is currently (and for the forseeable future) completely impossible to get actid
from an index-only scan.Related:
Quoting the Postgres Wiki on Index-only_scans / Interaction_with_HOT: