Index only scan and ctid - Mailing list pgsql-hackers

From Laurenz Albe
Subject Index only scan and ctid
Date
Msg-id b6a2860abd533fef12f6b713c109d29f3e3c2a15.camel@cybertec.at
Whole thread Raw
Responses Re: Index only scan and ctid
List pgsql-hackers
I noticed that "ctid" in the select list prevents an index only scan:

CREATE TABLE ios (id bigint NOT NULL, val text NOT NULL);

INSERT INTO ios SELECT i, i::text FROM generate_series(1, 100000) AS i;

CREATE INDEX ON ios (id);

VACUUM (ANALYZE) ios;

EXPLAIN (VERBOSE, COSTS off) SELECT ctid, id FROM ios WHERE id < 100;
                 QUERY PLAN                 
--------------------------------------------
 Index Scan using ios_id_idx on laurenz.ios
   Output: ctid, id
   Index Cond: (ios.id < 100)
(3 rows)

This strikes me as strange, since every index contains "ctid".

This is not an artificial example either, because "ctid" is automatically
added to all data modifying queries to be able to identify the tuple
for EvalPlanQual:

EXPLAIN (VERBOSE, COSTS off) UPDATE ios SET val = '' WHERE id < 100;
                    QUERY PLAN                    
--------------------------------------------------
 Update on laurenz.ios
   ->  Index Scan using ios_id_idx on laurenz.ios
         Output: id, ''::text, ctid
         Index Cond: (ios.id < 100)
(4 rows)

Is this low hanging fruit?  If yes, I might take a stab at it.

Yours,
Laurenz Albe




pgsql-hackers by date:

Previous
From: Tom Lane
Date:
Subject: Re: Dumping/restoring fails on inherited generated column
Next
From: Tom Lane
Date:
Subject: Re: Index only scan and ctid