Re: INDEX ONLY scan with expression index

On Mon, Mar 7, 2016 at 11:33 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> James Sewell <james(dot)sewell(at)lisasoft(dot)com> writes:
> > Would anyone be able to shed some light on why expression based indexes
> > can't be used for an index only scan?
> > I've found a few comments saying this is the case, and I've proven it is
> > the case in reality - but I can't seem to find the why.
>
> Well, it would help if you posted a concrete example ... but there's
> at least one known limitation: the planner's rule for whether an
> index can be used for an index-only scan is that all variables needed
> by the query be available from the index. So if you have an index
> on f(x), it might be useful for a query that needs f(x), but you won't
> get an index-only scan for it because the planner fails to notice that
> the query has no references to bare "x" but just "f(x)". (This is
> something that could be fixed, but it's not clear how to do so without
> imposing considerable cost on queries that get no benefit because they
> have no interest in f(x).)
>
> The recommended workaround at the moment is to create a two-column index
> on "f(x), x". The second index column has no great value in reality,
> but it lets the planner accept the index as usable for an IOS. As a
> small consolation prize, it might let you get an IOS on cases where you
> *do* need x as well.
>
>
Have we intentionally excluded creating a section under Chapter 11. Indexes
covering the user-visible dynamics of IOS and what can be done - such as
the advice just given - to cause the planner to choose one?

​David J.