PostgreSQL Index Only Scan can NOT return ctid
5
votes
1
answer
1181
views
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?
Asked by Qiushi Bai
(65 rep)
Apr 14, 2019, 05:32 AM
Last activity: May 2, 2020, 11:47 PM
Last activity: May 2, 2020, 11:47 PM