On 18 October 2012 17:52, Tom Lane <tgl@xxxxxxxxxxxxx> wrote: > Thom Brown <thom@xxxxxxxxx> writes: >> On 18 October 2012 17:44, Tom Lane <tgl@xxxxxxxxxxxxx> wrote: >>> Thom Brown <thom@xxxxxxxxx> 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? -- Thom -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
