From: | Andres Freund <andres(at)anarazel(dot)de> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | Andrew Dunstan <andrew(dot)dunstan(at)2ndquadrant(dot)com>, PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: Invisible Indexes |
Date: | 2018-06-18 22:00:33 |
Message-ID: | 20180618220033.3vprsgjrtilliyzh@alap3.anarazel.de |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
Hi,
On 2018-06-18 17:57:04 -0400, Tom Lane wrote:
> Andrew Dunstan <andrew(dot)dunstan(at)2ndquadrant(dot)com> writes:
> > This is a MySQL feature, where an index is not considered by the
> > planner. Implementing it should be fairly straightforward, adding a new
> > boolean to pg_index, and options to CREATE INDEX and ALTER INDEX. I
> > guess VISIBLE would become a new unreserved keyword.
>
> > The most obvious use case is to see what the planner does when the index
> > is not visible, for example which other index(es) it might use. There
> > are probably other cases where we might want an index to enforce a
> > constraint but not to be used in query planning.
>
> Traditionally the way to do the former is
>
> begin;
> drop index unwanted;
> explain ....;
> rollback;
>
> Admittedly, this isn't great in a production environment, but neither
> would be disabling the index in the way you suggest.
Yea, I don't think a global action - which'll at least take a something
like a share-update-exclusive lock - is a suitable approach for this
kinda thing.
> I think the actually desirable way to handle this sort of thing is through
> an "index advisor" sort of plugin, which can hide a given index from the
> planner without any globally visible side-effects.
Although I'm a bit doubtful that just shoving this into an extension is
really sufficient. This is an extremely common task.
Greetings,
Andres Freund
From | Date | Subject | |
---|---|---|---|
Next Message | Peter Geoghegan | 2018-06-18 22:04:18 | Re: Invisible Indexes |
Previous Message | Tom Lane | 2018-06-18 21:57:04 | Re: Invisible Indexes |