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

On Thu, 12 Jul 2018 12:59:15 +0200
Tomas Vondra <tomas(dot)vondra(at)2ndquadrant(dot)com> wrote:

>
>
> 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 ;-)

Thank you. It is really encouraging for me.

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

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