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

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.

Thanks,

--
Yugo Nagata <nagata(at)sraoss(dot)co(dot)jp>