+ This is the + default state for newly created indexes. This is not needed in the ALTER INDEX docs, IMO.ss
+ <para> + Disable the specified index. A disabled index is not used for queries, but it + is still updated when the underlying table data changes and will still be + used to enforce constraints (such as UNIQUE, or PRIMARY KEY constraints). + This can be useful for testing query performance with and without specific + indexes. If performance degrades after disabling an index, it can be easily + re-enabled using <literal>ENABLE</literal>. Before disabling, it's recommended + to check <structname>pg_stat_user_indexes</structname>.<structfield>idx_scan</structfield> + to identify potentially unused indexes. + </para> This got me thinking if dropping the index is the only use case we really care about. For example, you may want to prevent an index that is enforcing a constraint from being used by the planner, but you probably don't want to drop it. In fact, I also think that you may want the index from being used in one part of your application but could potentially benefit other parts of your application. In that case, I can see a GUC that allows you to force the use of a an index that has been CREATED or ALTERED as DISABLED. UNlike the GUC suggested earlier in the thread, this GUC can simply be a boolean to allow the force usage of a DISABLED index. FWIW, Oracle has a similar parameter called OPTIMIZER_USE_INVISIBLE_INDEXES. + underlying table data changes. This can be useful when you want to create + an index without immediately impacting query performance, allowing you to c/performance/planning ?? I have also been thinking about DISABLE as the keyword, and I really don't like it. DISABLE indicates, at least ot me, that the index is not available for either reads or writes. Looking at other engines, Sqlserver uses DISABLE to drop the index data, but keeps the index metadata around. Oracle uses INVISIBLE and MariabDB uses IGNORABLE to provide similar functionality to that being discussed here. I find those keywords to be more appropriate for this purpose. What about if we use HIDDEN instead of DISABLE as the keyword? Regards, Sami
