public inbox for [email protected]
help / color / mirror / Atom feedFrom: Thom Brown <[email protected]>
To: Tomas Vondra <[email protected]>
Cc: pgsql-performance <[email protected]>
Cc: Tom Lane <[email protected]>
Subject: Re: [PERFORM] Unused index influencing sequential scan plan
Date: Fri, 28 Feb 2025 23:19:12 +0000
Message-ID: <CAA-aLv4dmFSJiE9CLoLHCDrBX5dhX8jw=UkNHQbC1-hwHUy+4A@mail.gmail.com> (raw)
In-Reply-To: <CAA-aLv7D0GXZUJJ43i1vV8rK9MOVv1kMkW769H2aA6yWQKa11w@mail.gmail.com>
References: <CAA-aLv4aukjF=03XNV4o2UKWqweGd+XaHK0y1aTsgGGXmzVcDA@mail.gmail.com>
<[email protected]>
<CAA-aLv68tNQt0-CR2b3wxqu+D0FUDz+bTVPnE6gE6riKCbXERw@mail.gmail.com>
<[email protected]>
<CAA-aLv7D0GXZUJJ43i1vV8rK9MOVv1kMkW769H2aA6yWQKa11w@mail.gmail.com>
On Thu, 18 Oct 2012, 18:01 Thom Brown, <[email protected]> wrote:
> On 18 October 2012 17:52, Tom Lane <[email protected]> wrote:
> > Thom Brown <[email protected]> writes:
> >> On 18 October 2012 17:44, Tom Lane <[email protected]> wrote:
> >>> Thom Brown <[email protected]> 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.
Thom
>
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected]
Subject: Re: [PERFORM] Unused index influencing sequential scan plan
In-Reply-To: <CAA-aLv4dmFSJiE9CLoLHCDrBX5dhX8jw=UkNHQbC1-hwHUy+4A@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox