We all know that when using index scan in PostgreSQL, data is obtained from the table through the ctid stored in the index. At the same time, in PostgreSQL, if the columns to be queryed are all in the index, we can also use index only scan.
In this case, when we use ctid in the query, can we still use index only scan?
Logically speaking, there is no problem, for example in Oracle:
SQL> select rowid,id from t1 where id = 1; --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 25 | 1 (0)| 00:00:01 | |* 1 | INDEX RANGE SCAN| IDX_T1 | 1 | 25 | 1 (0)| 00:00:01 | ---------------------------------------------------------------------------
Our query contains rowid, and still does not need to return the table TABLE ACCESS BY INDEX ROWID BATCHED steps. But this doesn't seem to be the case in PostgreSQL.
index only scan：
bill=# explain analyze select c1 from t1 where c1 = 10;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------
Index Only Scan using idx_t1 on t1 (cost=0.29..10.74 rows=523 width=4) (actual time=0.021..0.117 rows=523 loops=1)
Index Cond: (c1 = 10)
Heap Fetches: 0
Planning Time: 0.076 ms
Execution Time: 0.196 ms
(5 rows)
After bringing ctid:
bill=# explain analyze select ctid,c1 from t1 where c1 = 10;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------
Index Scan using idx_t1 on t1 (cost=0.29..81.71 rows=523 width=10) (actual time=0.038..0.447 rows=523 loops=1)
Index Cond: (c1 = 10)
Planning Time: 0.098 ms
Execution Time: 0.537 ms
(4 rows)
You can see that index only scan is not used anymore, and instead it is a normal index scan.
Why is this happening? ctid must be included in any btree index. Why can't index only scan be used when using ctid?
See similar questions online:
Portal
The answer is that it is related to HOT. At first glance, it seems to make sense, but if it is HOT, then it will also judge multiple versions through the vm file. For ctid, we only need to judge its visibility through the vm file. At least when there are no invisible rows in the table, you should use index only scan.
This is actually because before using the vm file to judge visibility, the optimizer has decided in the parse stage whether to use index scan or index only scan, and use the check_index_only function to determine whether to use index only scan:
for (i = 0; i < index->ncolumns; i++)
{
int attno = index->indexkeys[i];
/*
* For the moment, we just ignore index expressions. It might be nice
* to do something with them, later.
*/
if (attno == 0)
continue;
if (index->canreturn[i])
index_canreturn_attrs =
bms_add_member(index_canreturn_attrs,
attno - FirstLowInvalidHeapAttributeNumber);
else
index_cannotreturn_attrs =
bms_add_member(index_cannotreturn_attrs,
attno - FirstLowInvalidHeapAttributeNumber);
}
index_canreturn_attrs = bms_del_members(index_canreturn_attrs,
index_cannotreturn_attrs);
/* Do we have all the necessary attributes? */
result = bms_is_subset(attrs_used, index_canreturn_attrs);
Let’s briefly explain the logic of the above code. When pg determines whether to use index only scan, it takes out the index column and puts it into a bitmap bitmap index_canreturn_attrs, puts the column used in the query into a bitmap bitmap attrs_used, and then determines whether the attrs_used bitmap is a subset of index_canreturn_attrs. If so, use index only scan. The index_canreturn_attrs information here is obtained from pg_index, so naturally the ctid information will not be stored.
This is the article about why index only scan does not return ctid when scanning PostgreSQL index. For more related PostgreSQL index only scan, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!