Re: [PERFORM] Unused index influencing sequential scan plan

On Thu, 18 Oct 2012, 18:01 Thom Brown, <thom(at)linux(dot)com> wrote:

> On 18 October 2012 17:52, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> > Thom Brown <thom(at)linux(dot)com> writes:
> >> On 18 October 2012 17:44, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> >>> Thom Brown <thom(at)linux(dot)com> writes:
> >>>> And as a side note, how come it's impossible to get the planner to use
> >>>> an index-only scan to satisfy the query (disabling sequential and
> >>>> regular index scans)?
> >
> >>> Implementation restriction - we don't yet have a way to match
> index-only
> >>> scans to expressions.
> >
> >> Ah, I suspected it might be, but couldn't find notes on what scenarios
> >> it's yet to be able to work in. Thanks.
> >
> > I forgot to mention that there is a klugy workaround: add the required
> > variable(s) as extra index columns. That is,
> >
> > create index i on t (foo(x), x);
> >
> > The planner isn't terribly bright about this, but it will use that index
> > for a query that only requires foo(x), and it won't re-evaluate foo()
> > (though I think it will cost the plan on the assumption it does :-().
>
> Ah, yes, I've tested this and got it using an index-only scan, and it
> was faster than than the sequential scan (index only scan 5024.545 ms
> vs seq scan 6627.072 ms).
>
> So this is probably a dumb question, but is it possible to achieve the
> optimisation provided by index statistics but without the index, and
> without a messy workaround using a supplementary column which stores
> function-derived values? If not, is that something which can be
> introduced?
>

A very late thanks for extended statistics, Tomas.

Thom

>