That said - Thank you everyone for the discussions and pointers. I now have a new patch that introduces the ability to enable or disable indexes using ALTER INDEX and CREATE INDEX commands, and updating get_relation_info in plancat.c to skip disabled indexes entirely by baking in the concept into IndexOptInfo structure. Below are all the relevant details. Original motivation for the problem and proposal for a patch can be found at [1]. This patch passes all the existing specs and the newly added regression tests. The patch is ready for review and test. It compiles, so the can patch can be applied for testing as well. Implementation details: - New Grammar: - ALTER INDEX ... ENABLE/DISABLE - CREATE INDEX ... DISABLE - Default state is enabled. Indexes are only disabled when explicitly instructed via CREATE INDEX ... DISABLE or ALTER INDEX ... DISABLE. - Primary Key and Unique constraint indexes are always enabled as well. The ENABLE/DISABLE grammar is not supported for these types of indexes. They can be later disabled via ALTER INDEX ... ENABLE/DISABLE if needed. - ALTER INDEX ... ENABLE/DISABLE performs an in-place update of the pg_index catalog to protect against indcheckxmin [2] (older unrelated thread). - pg_get_indexdef() support the new functionality and grammar. This change is reflected in \d output for tables and pg_dump. We show the DISABLE syntax accordingly. - Updated create_index.sql regression test to cover the new grammar and verify that disabled indexes are not used in queries. The test CATALOG_VERSION_NO - Basic single-column and multi-column indexes - Partial indexes - _expression_ indexes - Join indexes - GIN and GiST indexes - Covering indexes - Range indexes - Unique indexes and constraints - Adds a new enabled attribute to the IndexOptInfo structure. - Modifies get_relation_info in plancat.c to skip disabled indexes entirely, thus reducing the number of places we need to check if an index is disabled or not. Inspired by the conversations start at [3]. - I chose to modify the logic within get_relation_info as compared to, say, reducing the cost to make the planner not consider an index during planning, mostly to keep the number of changes being introduced to a minimum and also the logic itself being self-contained and easier to under perhaps (?). - No changes are made to stop the index from getting rebuilt. This way we ensure no data miss or corruption when index is re-nabled. - TOAST indexes are supported and enabled by default as well. - REINDEX CONCURRENTLY is supported as well and existing state of pg_index.indisenabled is carried over accordingly. - See the changes in create_index.sql to get an idea of the grammar and sql statements. - See the changes in create_index.out to get an idea of the catalogue states and EXPLAIN output to see when an index is getting used or isn't (when disabled). - Incorporated DavidR's feedback from [4] around documentation and also you will see that by skip disabled indexes entirely from get_relation_info in plancat.c (as mentioned above), we address the other mentioned issues as well. Looking forward to any and all feedback on this patch, including but not limited to code quality, tests, fundamental logic. Thanks Shayon |
v1-0001-Introduce-the-ability-to-enable-disable-indexes-u.patch
Description: Binary data
