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 <nag...@sraoss.co.jp>
diff --git a/src/backend/optimizer/util/pathnode.c b/src/backend/optimizer/util/pathnode.c index dbf9adc..61f0609 100644 --- a/src/backend/optimizer/util/pathnode.c +++ b/src/backend/optimizer/util/pathnode.c @@ -537,6 +537,8 @@ add_path(RelOptInfo *parent_rel, Path *new_path) old_path, 1.0000000001) == COSTS_BETTER1) remove_old = true; /* new dominates old */ + else if (old_path->pathtype == T_IndexScan && new_path->pathtype == T_IndexOnlyScan) + remove_old = true; /* new dominates old */ else accept_new = false; /* old equals or * dominates new */