Index only scan and ctid

Поиск
Список
Период
Сортировка
От Laurenz Albe
Тема Index only scan and ctid
Дата
Msg-id b6a2860abd533fef12f6b713c109d29f3e3c2a15.camel@cybertec.at
обсуждение исходный текст
Ответы Re: Index only scan and ctid  (Tom Lane <tgl@sss.pgh.pa.us>)
Список 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 по дате отправления:

Предыдущее
От: Tom Lane
Дата:
Сообщение: Re: Dumping/restoring fails on inherited generated column
Следующее
От: Tom Lane
Дата:
Сообщение: Re: Index only scan and ctid