From: | Thom Brown <thom(at)linux(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | pgsql-performance <pgsql-performance(at)postgresql(dot)org> |
Subject: | Re: Unused index influencing sequential scan plan |
Date: | 2012-10-18 17:01:05 |
Message-ID: | CAA-aLv7D0GXZUJJ43i1vV8rK9MOVv1kMkW769H2aA6yWQKa11w@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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?
--
Thom
From | Date | Subject | |
---|---|---|---|
Next Message | Thom Brown | 2012-10-18 17:01:51 | Re: Unused index influencing sequential scan plan |
Previous Message | Peter Geoghegan | 2012-10-18 17:00:43 | Re: Unused index influencing sequential scan plan |