Thread: Index only scan and ctid

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

Laurenz Albe <laurenz.albe@cybertec.at> writes:
> I noticed that "ctid" in the select list prevents an index only scan:
> This strikes me as strange, since every index contains "ctid".

There's no provision for an IOS to return a system column, though.
Not sure what it'd take to make that possible.

            regards, tom lane

On Mon, 2020-02-03 at 14:43 -0500, Tom Lane wrote:
> Laurenz Albe <laurenz.albe@cybertec.at> writes:
> > I noticed that "ctid" in the select list prevents an index only scan:
> > This strikes me as strange, since every index contains "ctid".
> 
> There's no provision for an IOS to return a system column, though.
> Not sure what it'd take to make that possible.

I was reminded what the obvious problem is:
the ctid of a heap only tuple is not stored in the index.  Duh.

Yours,
Laurenz Albe

Laurenz Albe <laurenz.albe@cybertec.at> writes:
> On Mon, 2020-02-03 at 14:43 -0500, Tom Lane wrote:
>> Laurenz Albe <laurenz.albe@cybertec.at> writes:
>>> I noticed that "ctid" in the select list prevents an index only scan:
>>> This strikes me as strange, since every index contains "ctid".

>> There's no provision for an IOS to return a system column, though.
>> Not sure what it'd take to make that possible.

> I was reminded what the obvious problem is:
> the ctid of a heap only tuple is not stored in the index.  Duh.

Duh ... the members of a HOT chain share the same indexed value(s),
which is why we needn't care exactly which one is live during IOS.
But they don't have the same TID.  Oh well.

            regards, tom lane

Greg Stark <stark@mit.edu> writes:
> For the user visible ctid we could just arbitrarily declare that the ctid
> returned by an IOS is the head of the HOT update chain instead of the tail.

No, I don't think that'd work at all, because that tuple might be dead.
A minimum expectation is that "SELECT ... WHERE ctid = 'xxx'" would return
the same data as the IOS, and that would fail because it wouldn't return
anything.

(In principle I suppose we could *also* redefine what selecting by ctid
means.  Doubt I want to go there though.)

> For a data modifying query -- and it would have to be one targeting some
> other table or else there's no way it could be an IOS -- does having a ctid
> for the head rather than the tail still work?

If you target a tuple that is live according to your current snapshot,
but nonetheless out-of-date, EPQ will chase up to the head for you.
But you gotta start with a tuple that is visible to your snapshot.

            regards, tom lane