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

On 07/12/2018 03:44 AM, Yugo Nagata wrote:
> On Wed, 11 Jul 2018 14:37:46 +0200
> Tomas Vondra <tomas(dot)vondra(at)2ndquadrant(dot)com> wrote:
>
>>
>> On 07/11/2018 01:28 PM, Ashutosh Bapat wrote:
>
>>> 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.
>
> You are right. When the table have rows and this is vacuumed, index only
> scan's cost is cheaper and chosen properly. Sorry, I have jumped to the
> conclusion before confirming this.
>

I'm very experienced in this. I've done this mistake a million times ;-)

regards

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