On Mon, Mar 7, 2016 at 11:33 PM, Tom Lane <t...@sss.pgh.pa.us> wrote:
> James Sewell <james.sew...@lisasoft.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.
