Hi,
I found that there is a situation that even when index only scan can be effective,
the planner doesn't select this. The planner makes indexe paths in descending
order of indexrelid, and the new path is discarded if its cost is not less than
the existing paths' cost. As a result, IndexOnlyScan path can be discard even
hough it may be effective than normal IndexScan.
Here is a example;
=# create table test1 (i int, d int);
CREATE TABLE
=# create index on test1(i) include (d);
CREATE INDEX
=# explain select d from test1 where i = 0;
QUERY PLAN
----------------------------------------------------------------------------------
Index Only Scan using test1_i_d_idx on test1 (cost=0.15..36.35 rows=11 width=4)
Index Cond: (i = 0)
(2 rows)
=# create index on test1(i) ;
CREATE INDEX
=# explain select d from test1 where i = 0;
QUERY PLAN
---------------------------------------------------------------------------
Index Scan using test1_i_idx on test1 (cost=0.15..36.35 rows=11 width=4)
Index Cond: (i = 0)
(2 rows)
This is not new for the covered index feature. We can see the same thing when using
multi-column indexes.
=# create table test2 (i int, d int);
CREATE TABLE
=# create index on test2(i,d);
CREATE INDEX
=# explain select d from test2 where i = 0;
QUERY PLAN
----------------------------------------------------------------------------------
Index Only Scan using test2_i_d_idx on test2 (cost=0.15..36.35 rows=11 width=4)
Index Cond: (i = 0)
(2 rows)
=# create index on test2(i);
CREATE INDEX
=# explain select d from test2 where i = 0;
QUERY PLAN
---------------------------------------------------------------------------
Index Scan using test2_i_idx on test2 (cost=0.15..36.35 rows=11 width=4)
Index Cond: (i = 0)
(2 rows)
Attached is a patch to prefer index-only-scan when the cost is equal to other index
path. Honestly, I'm not sure this is the best way. Any comments and advices would
be appriciated.
Regards,
--
Yugo Nagata <nagata@sraoss.co.jp>
