Sample Header Ad - 728x90

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