On Thu, 18 Oct 2012, 18:01 Thom Brown, <thom@linux.com> wrote:
On 18 October 2012 17:52, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Thom Brown <thom@linux.com> writes: >> On 18 October 2012 17:44, Tom Lane <tgl@sss.pgh.pa.us> wrote: >>> Thom Brown <thom@linux.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.
