Index only scan and ctid

From: Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at>
To: pgsql-hackers(at)postgresql(dot)org
Subject: Index only scan and ctid
Date: 2020-02-03 19:37:16
Message-ID: b6a2860abd533fef12f6b713c109d29f3e3c2a15.camel@cybertec.at
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2020-02-03 19:43:23 Re: Index only scan and ctid
Previous Message Tom Lane 2020-02-03 19:32:41 Re: Dumping/restoring fails on inherited generated column