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
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.
It might be a bit confusing when sequential scans return the tail (or
whichever member is visible). But it's not really wrong, all the members of
the chain are equally valid answers.
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? I'm not clear how EPQ works
for such cases. Does it still do an index scan at all or does it just do a
ctid scan? And does it follow HOT update chains if the row was updated?
On Tue., Feb. 4, 2020, 13:23 Tom Lane, <tgl@sss.pgh.pa.us> wrote:
Show quoted text
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