Обсуждение: Invisible Indexes
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. So, do we want this feature? If we do I'll go ahead and prepare a patch. cheers andrew -- Andrew Dunstan https://www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
On Mon, Jun 18, 2018 at 2:36 PM, Andrew Dunstan <andrew.dunstan@2ndquadrant.com> wrote: > > 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. > So, do we want this feature? If we do I'll go ahead and prepare a patch. I know that it's definitely a feature that I want. Haven't thought about the syntax, though. -- Peter Geoghegan
On 18 June 2018 at 16:36, Andrew Dunstan <andrew.dunstan@2ndquadrant.com> wrote: > > 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. > > So, do we want this feature? If we do I'll go ahead and prepare a patch. > should pg_index.indisvalid works for this? in that case you only need the syntax for it... -- Jaime Casanova www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
On 06/18/2018 05:46 PM, Jaime Casanova wrote: > On 18 June 2018 at 16:36, Andrew Dunstan <andrew.dunstan@2ndquadrant.com> wrote: >> 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. >> >> So, do we want this feature? If we do I'll go ahead and prepare a patch. >> > should pg_index.indisvalid works for this? in that case you only need > the syntax for it... > I thought about that. But I think these are more or less orthogonal. I doubt it will involve lots of extra code, though. cheers andrew -- Andrew Dunstan https://www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
On 06/18/2018 05:44 PM, Peter Geoghegan wrote: > On Mon, Jun 18, 2018 at 2:36 PM, Andrew Dunstan > <andrew.dunstan@2ndquadrant.com> wrote: >> 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. >> So, do we want this feature? If we do I'll go ahead and prepare a patch. > I know that it's definitely a feature that I want. Well, that's encouraging ;-) > Haven't thought > about the syntax, though. > I envisioned: CREATE INDEX .... [NOT VISIBLE] ...; ALTER INDEX ... [SET [NOT] VISIBLE] ...; Let the bikeshedding begin :-) cheers andrew -- Andrew Dunstan https://www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
On 2018-06-18 17:50:44 -0400, Andrew Dunstan wrote: > > > On 06/18/2018 05:46 PM, Jaime Casanova wrote: > > On 18 June 2018 at 16:36, Andrew Dunstan <andrew.dunstan@2ndquadrant.com> wrote: > > > 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. > > > > > > So, do we want this feature? If we do I'll go ahead and prepare a patch. > > > > > should pg_index.indisvalid works for this? in that case you only need > > the syntax for it... > > > > > I thought about that. But I think these are more or less orthogonal. I > doubt it will involve lots of extra code, though. Be careful about that - currently it's not actually trivially possible to ever update pg_index rows. No, I'm not kidding you. pg_index.indexcheckxmin relies on the pg_index row's xmin. If you have ALTER do a non inplace update, you'll break things. Greetings, Andres Freund
Andrew Dunstan <andrew.dunstan@2ndquadrant.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.
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.
I'm not sure about the "enforce constraint only" argument --- that
sounds like a made-up use-case to me. It's pretty hard to imagine
a case where a unique index applies to a query and yet you don't want
to use it.
> So, do we want this feature? If we do I'll go ahead and prepare a patch.
On the whole I'm not excited about it, at least not with this approach.
Have you considered an extension or GUC with only local side effects?
regards, tom lane
Hi, On 2018-06-18 17:57:04 -0400, Tom Lane wrote: > Andrew Dunstan <andrew.dunstan@2ndquadrant.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
On Mon, Jun 18, 2018 at 2:57 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Admittedly, this isn't great in a production environment, but neither > would be disabling the index in the way you suggest. > > 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. The globally visible side-effects are the point, though. Some users desire cheap insurance against dropping what turns out to be the wrong index. FWIW, this isn't just a MySQL feature. Oracle has a similar feature. -- Peter Geoghegan
Andres Freund <andres@anarazel.de> writes:
> On 2018-06-18 17:57:04 -0400, Tom Lane wrote:
>> 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.
Well, what I was thinking about was that this functionality already
exists (I think) in one or more "index advisor" plugins. It's possible
that they've all bit-rotted for lack of support, which would not speak
highly of the demand for the feature. But if we feel this is worth
pulling into core, I think something along the lines of a GUC listing
indexes to ignore for planning purposes might be a better design.
It'd certainly dodge the issues you mentioned about lack of mutability
of pg_index entries.
regards, tom lane
On 2018-06-18 18:05:11 -0400, Tom Lane wrote: > Andres Freund <andres@anarazel.de> writes: > > On 2018-06-18 17:57:04 -0400, Tom Lane wrote: > >> 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. > > Well, what I was thinking about was that this functionality already > exists (I think) in one or more "index advisor" plugins. They're doing the opposite, right? I.e. they return "hypothetical indexes", which then can be used by the planner. None of the ones I've seen currently mask out an existing index. > It's possible that they've all bit-rotted for lack of support, which > would not speak highly of the demand for the feature. IDK, the DBA / developer crowd hitting issues like this isn't the same as the crowd willing to update an external plugin that doesn't even do quite what you want, and was more experimental than anything. Greetings, Andres Freund
Peter Geoghegan <pg@bowt.ie> writes:
> On Mon, Jun 18, 2018 at 2:57 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> 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.
> The globally visible side-effects are the point, though. Some users
> desire cheap insurance against dropping what turns out to be the wrong
> index.
Perhaps there are use-cases where you want globally visible effects,
but the primary use-case Andrew cited (i.e. EXPLAIN experimentation)
would not want that.
Anyway, if we do it with a GUC, the user can control the scope of
the effects.
regards, tom lane
On Tue, Jun 19, 2018 at 12:05 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > > Well, what I was thinking about was that this functionality already > exists (I think) in one or more "index advisor" plugins. It's possible > that they've all bit-rotted for lack of support, which would not speak > highly of the demand for the feature. But if we feel this is worth > pulling into core, I think something along the lines of a GUC listing > indexes to ignore for planning purposes might be a better design. > It'd certainly dodge the issues you mentioned about lack of mutability > of pg_index entries. I know only one extension which does exactly that: https://github.com/postgrespro/plantuner It seems that it's still maintained.
Andres Freund <andres@anarazel.de> writes:
> On 2018-06-18 18:05:11 -0400, Tom Lane wrote:
>> Well, what I was thinking about was that this functionality already
>> exists (I think) in one or more "index advisor" plugins.
> They're doing the opposite, right? I.e. they return "hypothetical
> indexes", which then can be used by the planner. None of the ones I've
> seen currently mask out an existing index.
I had the idea that some of them could also hide existing indexes.
It's been awhile, so maybe my memory is faulty, but the hook we
provide is capable of that:
/*
* Allow a plugin to editorialize on the info we obtained from the
* catalogs. Actions might include altering the assumed relation size,
* removing an index, or adding a hypothetical index to the indexlist.
*/
if (get_relation_info_hook)
(*get_relation_info_hook) (root, relationObjectId, inhparent, rel);
regards, tom lane
On Mon, Jun 18, 2018 at 3:12 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Perhaps there are use-cases where you want globally visible effects, > but the primary use-case Andrew cited (i.e. EXPLAIN experimentation) > would not want that. > > Anyway, if we do it with a GUC, the user can control the scope of > the effects. I had imagined that those use cases would be the most common. Dropping an index in production because it very much looks like it is unused is always a bit nerve-wracking in my experience. It's often hard to be 100% sure due to factors like replicas, the possible loss of statistic collector stats masking a problem, the possibility that there are very important queries that do use the index but are only run very infrequently, and so on. -- Peter Geoghegan
On 06/18/2018 06:12 PM, Tom Lane wrote: > Peter Geoghegan <pg@bowt.ie> writes: >> On Mon, Jun 18, 2018 at 2:57 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: >>> 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. >> The globally visible side-effects are the point, though. Some users >> desire cheap insurance against dropping what turns out to be the wrong >> index. > Perhaps there are use-cases where you want globally visible effects, > but the primary use-case Andrew cited (i.e. EXPLAIN experimentation) > would not want that. > > Anyway, if we do it with a GUC, the user can control the scope of > the effects. > > Yeah, but Peter makes the case that people want it for global experimentation. "We think we can safely drop this humungous index that would take us days to rebuild, but before we do let's make it invisible and run for a few days just to make sure." I guess we could do that with a GUC, but it seems ugly. To Andres' point about the fragility of pg_index, maybe we'd need a separate_catalog (pg_invisible_index)? cheers andrew -- Andrew Dunstan https://www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Andrew Dunstan <andrew.dunstan@2ndquadrant.com> writes:
> On 06/18/2018 06:12 PM, Tom Lane wrote:
>> Anyway, if we do it with a GUC, the user can control the scope of
>> the effects.
> Yeah, but Peter makes the case that people want it for global
> experimentation. "We think we can safely drop this humungous index that
> would take us days to rebuild, but before we do let's make it invisible
> and run for a few days just to make sure." I guess we could do that with
> a GUC, but it seems ugly.
I find it hard to believe that it's uglier than what you suggested...
and it also does more, and is easier to implement.
regards, tom lane
But if we feel this is worth
pulling into core, I think something along the lines of a GUC listing
indexes to ignore for planning purposes might be a better design.
It'd certainly dodge the issues you mentioned about lack of mutability
of pg_index entries.
While adding a mutable column to pg_index is probably ideal having a pg_index_visible table related one-to-one (optional?) with pg_index. MySQL has, and we would probably want, a GUC to control whether to check the table for visibility.
Reading the MySQL description for this one use case posited is a DBA wanting to remove an index and see which queries appear in their duration limit log (probably in combination with auto-explain).
An SQL interface to the feature seems desirable. On that front VISIBLE and INVISIBLE are the pre-existing keywords for MySQL.
As long as BEGIN-ALTER INDEX-ROLLBACK works as expected I wouldn't see any need for a GUC accepting text inputs. That said, somehow making "ALTER INDEX LOCAL name INVISIBLE" work and having it auto-revert back to visible as transaction end would provide for the one major advantage of an in-session SET.
David J.
On Mon, Jun 18, 2018 at 6:11 PM, Andres Freund <andres@anarazel.de> wrote: > On 2018-06-18 18:05:11 -0400, Tom Lane wrote: >> Andres Freund <andres@anarazel.de> writes: >> > On 2018-06-18 17:57:04 -0400, Tom Lane wrote: >> >> 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. >> >> Well, what I was thinking about was that this functionality already >> exists (I think) in one or more "index advisor" plugins. > > They're doing the opposite, right? I.e. they return "hypothetical > indexes", which then can be used by the planner. None of the ones I've > seen currently mask out an existing index. > > >> It's possible that they've all bit-rotted for lack of support, which >> would not speak highly of the demand for the feature. > > IDK, the DBA / developer crowd hitting issues like this isn't the same > as the crowd willing to update an external plugin that doesn't even do > quite what you want, and was more experimental than anything. > Indeed. ISTR a conversation I had with someone on slack earlier this year about the validity of just manually updating indisvalid as a means for determining if an index could be safely removed (for the record, I did not recommend it ;-) DBA's are often willing to weedwhacker at things in SQL when the alternative is to learn C. Robert Treat http://xzilla.net
Yeah, but Peter makes the case that people want it for global experimentation. "We think we can safely drop this humungous index that would take us days to rebuild, but before we do let's make it invisible and run for a few days just to make sure." I guess we could do that with a GUC, but it seems ugly.
On that front what's the proposed behavior for cached plans using said index?
IIUC with a GUC you'd have to force clients to establish new sessions if you wanted all queries to be affected by the new setting whereas using cache invalidation you can affect existing sessions with a catalog update.
David J.
On 19.06.2018 01:11, Andres Freund wrote: > On 2018-06-18 18:05:11 -0400, Tom Lane wrote: >> Andres Freund <andres@anarazel.de> writes: >>> On 2018-06-18 17:57:04 -0400, Tom Lane wrote: >>>> 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. >> Well, what I was thinking about was that this functionality already >> exists (I think) in one or more "index advisor" plugins. > They're doing the opposite, right? I.e. they return "hypothetical > indexes", which then can be used by the planner. None of the ones I've > seen currently mask out an existing index. > I think that "invisible" indexes are tightly related with "hypothetical" indexes. Both are used to estimate query execution cost if particular index exists/not exists. Certainly, in case of hypothetical indexes we can only calculate cost, but not actually execute query using this index. And "invisible" indexes allows to execute query without this index. But the final goal of both in the same. And if we are introducing some syntax for invisible indexes, may be it is better to take in account also "hypothetical" indexes and let them to be toggled by this syntax also. I am not sure if it can be completely done at extension level. At least definitely, altering grammar is not possible at extension level. But it can be handled using index parameters. Both invisible and hypothetical indexes seems to be really useful things: steps forward to "zero administration" database. My point is that we should consider them together. -- Konstantin Knizhnik Postgres Professional: http://www.postgrespro.com The Russian Postgres Company
On Mon, Jun 18, 2018 at 6:12 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Peter Geoghegan <pg@bowt.ie> writes: >> On Mon, Jun 18, 2018 at 2:57 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: >>> 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. > >> The globally visible side-effects are the point, though. Some users >> desire cheap insurance against dropping what turns out to be the wrong >> index. > > Perhaps there are use-cases where you want globally visible effects, > but the primary use-case Andrew cited (i.e. EXPLAIN experimentation) > would not want that. > > Anyway, if we do it with a GUC, the user can control the scope of > the effects. Yeah, I agree that a GUC seems more powerful and easier to roll out. A downside is that there could be cached plans still using that old index. If we did DDL on the index we could be sure they all got invalidated, but otherwise how do we know? BTW, like you, I seem to remember somebody writing an extension that did added a GUC that did exactly this, and demoing it at a conference. Maybe Oleg or Teodor? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
2018-06-19 15:05 GMT-03:00 Robert Haas <robertmhaas@gmail.com>: > Yeah, I agree that a GUC seems more powerful and easier to roll out. > A downside is that there could be cached plans still using that old > index. If we did DDL on the index we could be sure they all got > invalidated, but otherwise how do we know? > If we want to test the effect of disabling an index, we could set GUC only on the current session. DDL will make the index invisible immediately. Things can go worse after that. I prefer the former. It is more conservative but could confuse users if the effect is not immediate (few words could explain cached plans x invisible indexes). > BTW, like you, I seem to remember somebody writing an extension that > did added a GUC that did exactly this, and demoing it at a conference. > Maybe Oleg or Teodor? > https://github.com/postgrespro/plantuner -- Euler Taveira Timbira - http://www.timbira.com.br/ PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento
On Tue, Jun 19, 2018 at 12:22 PM, Euler Taveira <euler@timbira.com.br> wrote: > If we want to test the effect of disabling an index, we could set GUC > only on the current session. DDL will make the index invisible > immediately. Things can go worse after that. I prefer the former. It > is more conservative but could confuse users if the effect is not > immediate (few words could explain cached plans x invisible indexes). If we're going to go that way, then we better not call them invisible indexes. Invisible indexes are generally understood to be indexes that are "invisible" to everyone -- not just the current session. -- Peter Geoghegan
Hi, On 2018-06-19 14:05:24 -0400, Robert Haas wrote: > Yeah, I agree that a GUC seems more powerful and easier to roll out. > A downside is that there could be cached plans still using that old > index. If we did DDL on the index we could be sure they all got > invalidated, but otherwise how do we know? Hm - it doesn't seem too hard to force an invalidation after SIGHUP and certain config changes. Seems like that would be a good idea for other existing GUCs anyway? Greetings, Andres Freund
On 06/19/2018 02:05 PM, Robert Haas wrote: > On Mon, Jun 18, 2018 at 6:12 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: >> Peter Geoghegan <pg@bowt.ie> writes: >>> On Mon, Jun 18, 2018 at 2:57 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: >>>> 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. >>> The globally visible side-effects are the point, though. Some users >>> desire cheap insurance against dropping what turns out to be the wrong >>> index. >> Perhaps there are use-cases where you want globally visible effects, >> but the primary use-case Andrew cited (i.e. EXPLAIN experimentation) >> would not want that. >> >> Anyway, if we do it with a GUC, the user can control the scope of >> the effects. > Yeah, I agree that a GUC seems more powerful and easier to roll out. > A downside is that there could be cached plans still using that old > index. If we did DDL on the index we could be sure they all got > invalidated, but otherwise how do we know? > > BTW, like you, I seem to remember somebody writing an extension that > did added a GUC that did exactly this, and demoing it at a conference. > Maybe Oleg or Teodor? > A major downside to a GUC is that you have to be aware of the current setting, since we're not going to have one settoing for each invisible index. Doing it at the SQL level you can treat each index separately. A GUC will actually involve more code, I suspect. cheers andrew -- Andrew Dunstan https://www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Andrew Dunstan <andrew.dunstan@2ndquadrant.com> writes:
> A major downside to a GUC is that you have to be aware of the current
> setting, since we're not going to have one settoing for each invisible
> index. Doing it at the SQL level you can treat each index separately. A
> GUC will actually involve more code, I suspect.
I'd envision it being a list of index names. We already have most
if not all of the underpinnings for such a thing, I believe, lurking
around the code for search_path, temp_tablespaces, etc.
regards, tom lane
On Sun, Jun 24, 2018 at 09:59:15AM -0400, Tom Lane wrote: > Andrew Dunstan <andrew.dunstan@2ndquadrant.com> writes: > > A major downside to a GUC is that you have to be aware of the current > > setting, since we're not going to have one settoing for each invisible > > index. Doing it at the SQL level you can treat each index separately. A > > GUC will actually involve more code, I suspect. > > I'd envision it being a list of index names. We already have most > if not all of the underpinnings for such a thing, I believe, lurking > around the code for search_path, temp_tablespaces, etc. I would love to see an API that allowed hypothetical indexes too. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription +
On 19 June 2018 at 09:56, Andres Freund <andres@anarazel.de> wrote: > Be careful about that - currently it's not actually trivially possible > to ever update pg_index rows. No, I'm not kidding > you. pg_index.indexcheckxmin relies on the pg_index row's xmin. If you > have ALTER do a non inplace update, you'll break things. Couldn't we just add a dedicated xid field to pg_index to solve that, one which does not change when the row is updated? Or would it be insanely weird to just not allow setting or unsetting this invisible flag if indcheckxmin is true? I can't imagine there will be many people adding an index and not wanting to use it while it's still being created. I think the use case here is mostly people wanting to test dropping indexes before they go and remove that 1TB index that will take days to build again if they're wrong. -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
On Wed, Jul 4, 2018 at 6:26 PM, David Rowley <david.rowley@2ndquadrant.com> wrote: > Or would it be insanely weird to just not allow setting or unsetting > this invisible flag if indcheckxmin is true? I can't imagine there > will be many people adding an index and not wanting to use it while > it's still being created. I think the use case here is mostly people > wanting to test dropping indexes before they go and remove that 1TB > index that will take days to build again if they're wrong. I'm surprised that that use case wasn't the first one that everyone thought of. I actually assumed that that's what Andrew had in mind when reading his original message. I only realized later that it wasn't. -- Peter Geoghegan
On 5 July 2018 at 13:31, Peter Geoghegan <pg@bowt.ie> wrote: > On Wed, Jul 4, 2018 at 6:26 PM, David Rowley > <david.rowley@2ndquadrant.com> wrote: >> Or would it be insanely weird to just not allow setting or unsetting >> this invisible flag if indcheckxmin is true? I can't imagine there >> will be many people adding an index and not wanting to use it while >> it's still being created. I think the use case here is mostly people >> wanting to test dropping indexes before they go and remove that 1TB >> index that will take days to build again if they're wrong. > > I'm surprised that that use case wasn't the first one that everyone > thought of. I actually assumed that that's what Andrew had in mind > when reading his original message. I only realized later that it > wasn't. hmm. Maybe I missed any other use case. The mention of hypothetical indexes seems a bit lost on this thread. Andrew's proposal mentions that an invisible index will just not be considered by the planner. I'd very much assume here that the index must exist on disk, and there's not much hypothetical about that. It seems to me that there would be exactly 1 place in the backend that the new bool flag would need to be checked, and that's in get_relation_info() to skip any indexes that are "invisible". pg_dump would, of course, need to know about this flag too. Like Andrew, I'm not much of a fan of the GUC idea. Testing a plan without an index could just be a BEGIN; ALTER INDEX; EXPLAIN; ROLLBACK; operation. It seems much neater not to spread the properties of an index all over the place when we have a perfectly good table to store index properties in. Unsure why Tom thinks that's ugly. FWIW I have also seen customers asking if they can test drop an index by setting indisready to false. Naturally, people are often a bit scared to confirm messing around with catalogue tables on a busy production server is fine. Also, FWIW, I'd not bother with a CREATE INDEX syntax for this and leave it to ALTER INDEX. I also think that ENABLE/DISABLE is nicer than VISIBLE/NOT VISIBLE. Those are already unreserved words too. Although, perhaps pg_dump would prefer us to have syntax for this in CREATE INDEX since it could describe the new object in a single statement. -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
On Wed, Jul 4, 2018 at 7:09 PM, David Rowley <david.rowley@2ndquadrant.com> wrote: > hmm. Maybe I missed any other use case. The mention of hypothetical > indexes seems a bit lost on this thread. Andrew's proposal mentions > that an invisible index will just not be considered by the planner. > I'd very much assume here that the index must exist on disk, and > there's not much hypothetical about that. +1 > Like Andrew, I'm not much of a fan of the GUC idea. Testing a plan > without an index could just be a BEGIN; ALTER INDEX; EXPLAIN; > ROLLBACK; operation. It seems much neater not to spread the properties > of an index all over the place when we have a perfectly good table to > store index properties in. Unsure why Tom thinks that's ugly. I have to admit to not getting what's so ugly about it myself. > FWIW I have also seen customers asking if they can test drop an index > by setting indisready to false. Naturally, people are often a bit > scared to confirm messing around with catalogue tables on a busy > production server is fine. That's very easy for me to understand. A large production application can be complicated in a way that nobody can quite nail down. Often, being sure that dropping an index won't have any ramifications is an unobtainable luxury, because knowledge about how the app works isn't centralized in one place. If it's a very large index, why even take a very small chance? -- Peter Geoghegan
On Mon, Jun 18, 2018 at 5:57 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
I'm not sure about the "enforce constraint only" argument --- that
sounds like a made-up use-case to me. It's pretty hard to imagine
a case where a unique index applies to a query and yet you don't want
to use it.
I've not seen it with unique constraints, but have with EXCLUDE constraints. GiST index costing is not very robust, and the planner can easily decide that a read query should use the EXCLUDE-supporting GiST index in cases where it is not optimal.
Cheers,
Jeff