Re: Preferring index-only-scan when the cost is equal

On 07/11/2018 01:28 PM, Ashutosh Bapat wrote:
> On Wed, Jul 11, 2018 at 11:03 AM, Yugo Nagata <nagata(at)sraoss(dot)co(dot)jp> wrote:
>> 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.
>>
>
> I don't think we should change add_path() for this. We will
> unnecessarily check that condition even for the cases where we do not
> create index paths. I think we should fix the caller of add_path()
> instead to add index only path before any index paths. For that the
> index list needs to be sorted by the possibility of using index only
> scan.
>
> But I think in your case, it might be better to first check whether
> there is any costing error because of which index only scan's path has
> the same cost as index scan path. Also I don't see any testcase which
> will show why index only scan would be more efficient in your case.
> May be provide output of EXPLAIN ANALYZE.
>

I suspect this only happens due to testing on empty tables. Not only is
testing of indexes on small tables rather pointless in general, but more
importantly there will be no statistics. So we fall back to some default
estimates, but we also don't have relallvisible etc which is crucial for
estimating index-only scans. I'd bet that's why the cost estimates for
index scans and index-only scans are the same here.

Yugo-san, have you observed this behavior on larger tables?

regards

--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services