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