From c8886d5ac14f31897a7f8058a1caab3d9213993e Mon Sep 17 00:00:00 2001 From: Sami Imseih Date: Tue, 1 Apr 2025 12:27:23 -0500 Subject: [PATCH 1/1] Introduce the ability to enable/disable indexes using ALTER INDEX --- doc/src/sgml/catalogs.sgml | 11 + doc/src/sgml/ref/alter_index.sgml | 43 ++ doc/src/sgml/ref/create_index.sgml | 29 ++ src/backend/bootstrap/bootparse.y | 2 + src/backend/catalog/index.c | 31 +- src/backend/catalog/toasting.c | 2 +- src/backend/commands/indexcmds.c | 4 + src/backend/commands/tablecmds.c | 66 +++ src/backend/optimizer/util/plancat.c | 14 + src/backend/parser/gram.y | 48 +- src/backend/parser/parse_utilcmd.c | 3 + src/backend/utils/adt/ruleutils.c | 4 + src/backend/utils/cache/relcache.c | 1 + src/include/catalog/index.h | 1 + src/include/catalog/pg_index.h | 1 + src/include/nodes/parsenodes.h | 3 + src/include/nodes/pathnodes.h | 2 + src/test/regress/expected/create_index.out | 505 +++++++++++++++++++++ src/test/regress/sql/create_index.sql | 192 ++++++++ 19 files changed, 954 insertions(+), 8 deletions(-) diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml index fb050635551..123fcb04892 100644 --- a/doc/src/sgml/catalogs.sgml +++ b/doc/src/sgml/catalogs.sgml @@ -4618,6 +4618,17 @@ SCRAM-SHA-256$<iteration count>:&l partial index. + + + + indisenabled bool + + + If true, the index is currently enabled and should be used for queries. + If false, the index is disabled and should not be used for queries, + but is still maintained when the table is modified. Default is true. + + diff --git a/doc/src/sgml/ref/alter_index.sgml b/doc/src/sgml/ref/alter_index.sgml index 1d42d05d858..04b3b0b9bfe 100644 --- a/doc/src/sgml/ref/alter_index.sgml +++ b/doc/src/sgml/ref/alter_index.sgml @@ -31,6 +31,8 @@ ALTER INDEX [ IF EXISTS ] name ALTE SET STATISTICS integer ALTER INDEX ALL IN TABLESPACE name [ OWNED BY role_name [, ... ] ] SET TABLESPACE new_tablespace [ NOWAIT ] +ALTER INDEX [ IF EXISTS ] name ENABLE +ALTER INDEX [ IF EXISTS ] name DISABLE @@ -159,6 +161,33 @@ ALTER INDEX ALL IN TABLESPACE name + + ENABLE + + + Enable the specified index. The index will be used by the query planner + for query optimization. This is the default state for newly created indexes. + + + + + + DISABLE + + + Disable the specified index. A disabled index is not used by the query planner + for query optimization, but it is still maintained when the underlying table + data changes. This can be useful for testing query performance with and without + specific indexes, temporarily reducing the overhead of index maintenance + during bulk data loading operations, or verifying an index is not being used + before dropping it. If performance degrades after disabling an index, it can be + easily re-enabled. Before disabling, it's recommended to check + pg_stat_user_indexes.idx_scan + to identify potentially unused indexes. + + + + @@ -301,6 +330,20 @@ CREATE INDEX coord_idx ON measured (x, y, (z + t)); ALTER INDEX coord_idx ALTER COLUMN 3 SET STATISTICS 1000; + + To enable an index: + +ALTER INDEX idx_name ENABLE; + + + + + To disable an index: + +ALTER INDEX idx_name DISABLE; + + + diff --git a/doc/src/sgml/ref/create_index.sgml b/doc/src/sgml/ref/create_index.sgml index 208389e8006..d7a2f7df852 100644 --- a/doc/src/sgml/ref/create_index.sgml +++ b/doc/src/sgml/ref/create_index.sgml @@ -28,6 +28,7 @@ CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [ IF NOT EXISTS ] storage_parameter [= value] [, ... ] ) ] [ TABLESPACE tablespace_name ] [ WHERE predicate ] + [ DISABLE ] @@ -380,6 +381,19 @@ CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [ IF NOT EXISTS ] + + DISABLE + + + Creates the index in a disabled state (default enabled). A disabled index + is not used by the query planner for query optimization, but it is still + maintained when the underlying table data changes. This can be useful when + you want to create an index without immediately impacting query performance, + allowing you to enable it later at a more convenient time. The index can be + enabled later using ALTER INDEX ... ENABLE. + + + @@ -701,6 +715,14 @@ Indexes: partitioned index is a metadata only operation. + + When creating an index with the DISABLE option, the index + is created but not used for query planning. This can be useful for preparing + an index in advance of its use or for testing purposes. The index will still + be maintained as the table is modified, allowing it to be enabled later without + needing to be rebuilt. By default, all new indexes are enabled. + + @@ -980,6 +1002,13 @@ SELECT * FROM points CREATE INDEX CONCURRENTLY sales_quantity_index ON sales_table (quantity); + + To create an index on the table test_table with the default + name, but have it initially disabled: + +CREATE INDEX ON test_table (col1) DISABLE; + + diff --git a/src/backend/bootstrap/bootparse.y b/src/backend/bootstrap/bootparse.y index 9833f52c1be..9eea080ab5b 100644 --- a/src/backend/bootstrap/bootparse.y +++ b/src/backend/bootstrap/bootparse.y @@ -303,6 +303,7 @@ Boot_DeclareIndexStmt: stmt->concurrent = false; stmt->if_not_exists = false; stmt->reset_default_tblspc = false; + stmt->isenabled = true; /* locks and races need not concern us in bootstrap mode */ relationId = RangeVarGetRelid(stmt->relation, NoLock, @@ -356,6 +357,7 @@ Boot_DeclareUniqueIndexStmt: stmt->concurrent = false; stmt->if_not_exists = false; stmt->reset_default_tblspc = false; + stmt->isenabled = true; /* locks and races need not concern us in bootstrap mode */ relationId = RangeVarGetRelid(stmt->relation, NoLock, diff --git a/src/backend/catalog/index.c b/src/backend/catalog/index.c index 739a92bdcc1..510755a4c0b 100644 --- a/src/backend/catalog/index.c +++ b/src/backend/catalog/index.c @@ -119,7 +119,8 @@ static void UpdateIndexRelation(Oid indexoid, Oid heapoid, bool isexclusion, bool immediate, bool isvalid, - bool isready); + bool isready, + bool isenabled); static void index_update_stats(Relation rel, bool hasindex, double reltuples); @@ -571,7 +572,8 @@ UpdateIndexRelation(Oid indexoid, bool isexclusion, bool immediate, bool isvalid, - bool isready) + bool isready, + bool isenabled) { int2vector *indkey; oidvector *indcollation; @@ -649,6 +651,7 @@ UpdateIndexRelation(Oid indexoid, values[Anum_pg_index_indisready - 1] = BoolGetDatum(isready); values[Anum_pg_index_indislive - 1] = BoolGetDatum(true); values[Anum_pg_index_indisreplident - 1] = BoolGetDatum(false); + values[Anum_pg_index_indisenabled - 1] = BoolGetDatum(isenabled); values[Anum_pg_index_indkey - 1] = PointerGetDatum(indkey); values[Anum_pg_index_indcollation - 1] = PointerGetDatum(indcollation); values[Anum_pg_index_indclass - 1] = PointerGetDatum(indclass); @@ -714,6 +717,8 @@ UpdateIndexRelation(Oid indexoid, * already exists. * INDEX_CREATE_PARTITIONED: * create a partitioned index (table must be partitioned) + * INDEX_CREATE_DISABLED: +* create the index as disabled if instructed, defaults to being enabled. * constr_flags: flags passed to index_constraint_create * (only if INDEX_CREATE_ADD_CONSTRAINT is set) * allow_system_table_mods: allow table to be a system catalog @@ -759,6 +764,7 @@ index_create(Relation heapRelation, bool invalid = (flags & INDEX_CREATE_INVALID) != 0; bool concurrent = (flags & INDEX_CREATE_CONCURRENT) != 0; bool partitioned = (flags & INDEX_CREATE_PARTITIONED) != 0; + bool isenabled = (flags & INDEX_CREATE_ENABLED) != 0; char relkind; TransactionId relfrozenxid; MultiXactId relminmxid; @@ -1042,13 +1048,15 @@ index_create(Relation heapRelation, * (Or, could define a rule to maintain the predicate) --Nels, Feb '92 * ---------------- */ + UpdateIndexRelation(indexRelationId, heapRelationId, parentIndexRelid, indexInfo, collationIds, opclassIds, coloptions, isprimary, is_exclusion, (constr_flags & INDEX_CONSTR_CREATE_DEFERRABLE) == 0, !concurrent && !invalid, - !concurrent); + !concurrent, + isenabled); /* * Register relcache invalidation on the indexes' heap relation, to @@ -1317,6 +1325,8 @@ index_concurrently_create_copy(Relation heapRelation, Oid oldIndexId, List *indexColNames = NIL; List *indexExprs = NIL; List *indexPreds = NIL; + Form_pg_index indexForm; + bits16 createFlags; indexRelation = index_open(oldIndexId, RowExclusiveLock); @@ -1344,6 +1354,9 @@ index_concurrently_create_copy(Relation heapRelation, Oid oldIndexId, Anum_pg_index_indoption); indcoloptions = (int2vector *) DatumGetPointer(colOptionDatum); + /* Get the enabled state of the original index */ + indexForm = (Form_pg_index) GETSTRUCT(indexTuple); + /* Fetch reloptions of index if any */ classTuple = SearchSysCache1(RELOID, ObjectIdGetDatum(oldIndexId)); if (!HeapTupleIsValid(classTuple)) @@ -1435,6 +1448,16 @@ index_concurrently_create_copy(Relation heapRelation, Oid oldIndexId, stattargets[i].isnull = isnull; } + /* + * Determine the create flags for the new index. We always use SKIP_BUILD + * and CONCURRENT for concurrent reindexing. If the original index was + * enabled, we also set the ENABLED flag to maintain the same state in the + * new index. + */ + createFlags = INDEX_CREATE_SKIP_BUILD | INDEX_CREATE_CONCURRENT; + if (indexForm->indisenabled) + createFlags |= INDEX_CREATE_ENABLED; + /* * Now create the new index. * @@ -1458,7 +1481,7 @@ index_concurrently_create_copy(Relation heapRelation, Oid oldIndexId, indcoloptions->values, stattargets, reloptionsDatum, - INDEX_CREATE_SKIP_BUILD | INDEX_CREATE_CONCURRENT, + createFlags, 0, true, /* allow table to be a system catalog? */ false, /* is_internal? */ diff --git a/src/backend/catalog/toasting.c b/src/backend/catalog/toasting.c index 874a8fc89ad..a612a0a3de1 100644 --- a/src/backend/catalog/toasting.c +++ b/src/backend/catalog/toasting.c @@ -325,7 +325,7 @@ create_toast_table(Relation rel, Oid toastOid, Oid toastIndexOid, BTREE_AM_OID, rel->rd_rel->reltablespace, collationIds, opclassIds, NULL, coloptions, NULL, (Datum) 0, - INDEX_CREATE_IS_PRIMARY, 0, true, true, NULL); + INDEX_CREATE_IS_PRIMARY | INDEX_CREATE_ENABLED, 0, true, true, NULL); table_close(toast_rel, NoLock); diff --git a/src/backend/commands/indexcmds.c b/src/backend/commands/indexcmds.c index 33c2106c17c..90f9b111e57 100644 --- a/src/backend/commands/indexcmds.c +++ b/src/backend/commands/indexcmds.c @@ -1222,6 +1222,10 @@ DefineIndex(Oid tableId, flags |= INDEX_CREATE_PARTITIONED; if (stmt->primary) flags |= INDEX_CREATE_IS_PRIMARY; + if (stmt->isenabled) + flags |= INDEX_CREATE_ENABLED; + else + flags &= ~INDEX_CREATE_ENABLED; /* * If the table is partitioned, and recursion was declined but partitions diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c index 10624353b0a..58d3d29d565 100644 --- a/src/backend/commands/tablecmds.c +++ b/src/backend/commands/tablecmds.c @@ -719,6 +719,7 @@ static List *GetParentedForeignKeyRefs(Relation partition); static void ATDetachCheckNoForeignKeyRefs(Relation partition); static char GetAttributeCompression(Oid atttypid, const char *compression); static char GetAttributeStorage(Oid atttypid, const char *storagemode); +static void ATExecEnableDisableIndex(Relation rel, bool enable); /* ---------------------------------------------------------------- @@ -4685,6 +4686,8 @@ AlterTableGetLockLevel(List *cmds) case AT_SetExpression: case AT_DropExpression: case AT_SetCompression: + case AT_EnableIndex: + case AT_DisableIndex: cmd_lockmode = AccessExclusiveLock; break; @@ -5249,6 +5252,12 @@ ATPrepCmd(List **wqueue, Relation rel, AlterTableCmd *cmd, /* No command-specific prep needed */ pass = AT_PASS_MISC; break; + case AT_EnableIndex: + case AT_DisableIndex: + ATSimplePermissions(cmd->subtype, rel, ATT_INDEX | ATT_PARTITIONED_INDEX); + /* No command-specific prep needed */ + pass = AT_PASS_MISC; + break; default: /* oops */ elog(ERROR, "unrecognized alter table type: %d", (int) cmd->subtype); @@ -5645,6 +5654,12 @@ ATExecCmd(List **wqueue, AlteredTableInfo *tab, case AT_DetachPartitionFinalize: address = ATExecDetachPartitionFinalize(rel, ((PartitionCmd *) cmd->def)->name); break; + case AT_EnableIndex: + ATExecEnableDisableIndex(rel, true); + break; + case AT_DisableIndex: + ATExecEnableDisableIndex(rel, false); + break; default: /* oops */ elog(ERROR, "unrecognized alter table type: %d", (int) cmd->subtype); @@ -6591,6 +6606,8 @@ alter_table_type_to_string(AlterTableType cmdtype) return "DROP COLUMN"; case AT_AddIndex: case AT_ReAddIndex: + case AT_EnableIndex: + case AT_DisableIndex: return NULL; /* not real grammar */ case AT_AddConstraint: case AT_ReAddConstraint: @@ -21466,3 +21483,52 @@ GetAttributeStorage(Oid atttypid, const char *storagemode) return cstorage; } + +/* + * ATExecEnableDisableIndex + * Performs a catalog update to enable or disable an index in pg_index. + */ +static void +ATExecEnableDisableIndex(Relation rel, bool enable) +{ + Oid indexOid = RelationGetRelid(rel); + Relation pg_index; + HeapTuple indexTuple; + Form_pg_index indexForm; + bool updated = false; + + pg_index = table_open(IndexRelationId, RowExclusiveLock); + + indexTuple = SearchSysCacheCopy1(INDEXRELID, ObjectIdGetDatum(indexOid)); + if (!HeapTupleIsValid(indexTuple)) + elog(ERROR, "could not find tuple for index %u", indexOid); + + indexForm = (Form_pg_index) GETSTRUCT(indexTuple); + if (indexForm->indcheckxmin) + { + heap_freetuple(indexTuple); + table_close(pg_index, RowExclusiveLock); + ereport(ERROR, + (errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE), + errmsg("cannot enable/disable index while indcheckxmin is true"), + errhint("Wait for all transactions that might see inconsistent HOT chains to complete"))); + } + + if (indexForm->indisenabled != enable) + { + indexForm->indisenabled = enable; + + CatalogTupleUpdate(pg_index, &indexTuple->t_self, indexTuple); + updated = true; + } + + heap_freetuple(indexTuple); + table_close(pg_index, RowExclusiveLock); + + if (updated) + { + CacheInvalidateRelcache(rel); + InvokeObjectPostAlterHook(IndexRelationId, indexOid, 0); + CommandCounterIncrement(); + } +} diff --git a/src/backend/optimizer/util/plancat.c b/src/backend/optimizer/util/plancat.c index 0489ad36644..08167df3f8b 100644 --- a/src/backend/optimizer/util/plancat.c +++ b/src/backend/optimizer/util/plancat.c @@ -295,6 +295,20 @@ get_relation_info(PlannerInfo *root, Oid relationObjectId, bool inhparent, info->opcintype = (Oid *) palloc(sizeof(Oid) * nkeycolumns); info->canreturn = (bool *) palloc(sizeof(bool) * ncolumns); + /* + * Skip disabled indexes altogether, as they should not be + * considered for query planning. This builds the data structure + * for the planner's use and we make it part of IndexOptInfo since + * the index is already open. We also close the relation before + * continuing to the next index. + */ + info->enabled = index->indisenabled; + if (!info->enabled) + { + index_close(indexRelation, NoLock); + continue; + } + for (i = 0; i < ncolumns; i++) { info->indexkeys[i] = index->indkey.values[i]; diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y index 0fc502a3a40..414a830ba1f 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -324,7 +324,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); %type add_drop opt_asc_desc opt_nulls_order %type alter_table_cmd alter_type_cmd opt_collate_clause - replica_identity partition_cmd index_partition_cmd + replica_identity partition_cmd index_partition_cmd index_alter_cmd %type alter_table_cmds alter_type_cmds %type alter_identity_column_option_list %type alter_identity_column_option @@ -491,6 +491,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); %type opt_unique opt_verbose opt_full %type opt_freeze opt_analyze opt_default %type opt_binary copy_delimiter +%type opt_index_enabled %type copy_from opt_program @@ -2157,6 +2158,24 @@ AlterTableStmt: n->nowait = $13; $$ = (Node *) n; } + | ALTER INDEX qualified_name index_alter_cmd + { + AlterTableStmt *n = makeNode(AlterTableStmt); + n->relation = $3; + n->cmds = list_make1($4); + n->objtype = OBJECT_INDEX; + n->missing_ok = false; + $$ = (Node *) n; + } + | ALTER INDEX IF_P EXISTS qualified_name index_alter_cmd + { + AlterTableStmt *n = makeNode(AlterTableStmt); + n->relation = $5; + n->cmds = list_make1($6); + n->objtype = OBJECT_INDEX; + n->missing_ok = true; + $$ = (Node *) n; + } | ALTER INDEX qualified_name alter_table_cmds { AlterTableStmt *n = makeNode(AlterTableStmt); @@ -2382,6 +2401,21 @@ index_partition_cmd: } ; +index_alter_cmd: + /* ALTER INDEX ENABLE|DISABLE */ + ENABLE_P + { + AlterTableCmd *n = makeNode(AlterTableCmd); + n->subtype = AT_EnableIndex; + $$ = (Node *) n; + } + | DISABLE_P + { + AlterTableCmd *n = makeNode(AlterTableCmd); + n->subtype = AT_DisableIndex; + $$ = (Node *) n; + } + ; alter_table_cmd: /* ALTER TABLE ADD */ ADD_P columnDef @@ -8185,7 +8219,7 @@ defacl_privilege_target: IndexStmt: CREATE opt_unique INDEX opt_concurrently opt_single_name ON relation_expr access_method_clause '(' index_params ')' - opt_include opt_unique_null_treatment opt_reloptions OptTableSpace where_clause + opt_include opt_unique_null_treatment opt_reloptions OptTableSpace where_clause opt_index_enabled { IndexStmt *n = makeNode(IndexStmt); @@ -8200,6 +8234,7 @@ IndexStmt: CREATE opt_unique INDEX opt_concurrently opt_single_name n->options = $14; n->tableSpace = $15; n->whereClause = $16; + n->isenabled = $17; n->excludeOpNames = NIL; n->idxcomment = NULL; n->indexOid = InvalidOid; @@ -8217,7 +8252,7 @@ IndexStmt: CREATE opt_unique INDEX opt_concurrently opt_single_name } | CREATE opt_unique INDEX opt_concurrently IF_P NOT EXISTS name ON relation_expr access_method_clause '(' index_params ')' - opt_include opt_unique_null_treatment opt_reloptions OptTableSpace where_clause + opt_include opt_unique_null_treatment opt_reloptions OptTableSpace where_clause opt_index_enabled { IndexStmt *n = makeNode(IndexStmt); @@ -8232,6 +8267,7 @@ IndexStmt: CREATE opt_unique INDEX opt_concurrently opt_single_name n->options = $17; n->tableSpace = $18; n->whereClause = $19; + n->isenabled = $20; n->excludeOpNames = NIL; n->idxcomment = NULL; n->indexOid = InvalidOid; @@ -8254,6 +8290,12 @@ opt_unique: | /*EMPTY*/ { $$ = false; } ; +opt_index_enabled: + ENABLE_P { $$ = true; } + | DISABLE_P { $$ = false; } + | /*EMPTY*/ { $$ = true; } + ; + access_method_clause: USING name { $$ = $2; } | /*EMPTY*/ { $$ = DEFAULT_INDEX_TYPE; } diff --git a/src/backend/parser/parse_utilcmd.c b/src/backend/parser/parse_utilcmd.c index 9c1541e1fea..488de757bc2 100644 --- a/src/backend/parser/parse_utilcmd.c +++ b/src/backend/parser/parse_utilcmd.c @@ -1745,6 +1745,7 @@ generateClonedIndexStmt(RangeVar *heapRel, Relation source_idx, index->concurrent = false; index->if_not_exists = false; index->reset_default_tblspc = false; + index->isenabled = idxrec->indisenabled; /* * We don't try to preserve the name of the source index; instead, just @@ -2366,6 +2367,8 @@ transformIndexConstraint(Constraint *constraint, CreateStmtContext *cxt) index->concurrent = false; index->if_not_exists = false; index->reset_default_tblspc = constraint->reset_default_tblspc; + /* Ensure indexes for constraints are created as enabled by default */ + index->isenabled = true; /* * If it's ALTER TABLE ADD CONSTRAINT USING INDEX, look up the index and diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c index 9e90acedb91..3f6a89efaf4 100644 --- a/src/backend/utils/adt/ruleutils.c +++ b/src/backend/utils/adt/ruleutils.c @@ -1565,6 +1565,10 @@ pg_get_indexdef_worker(Oid indexrelid, int colno, else appendStringInfo(&buf, " WHERE %s", str); } + + /* Add DISABLE clause if the index is disabled */ + if (!idxrec->indisenabled) + appendStringInfoString(&buf, " DISABLE"); } /* Clean up */ diff --git a/src/backend/utils/cache/relcache.c b/src/backend/utils/cache/relcache.c index 9f54a9e72b7..5e59a73d655 100644 --- a/src/backend/utils/cache/relcache.c +++ b/src/backend/utils/cache/relcache.c @@ -2318,6 +2318,7 @@ RelationReloadIndexInfo(Relation relation) relation->rd_index->indisready = index->indisready; relation->rd_index->indislive = index->indislive; relation->rd_index->indisreplident = index->indisreplident; + relation->rd_index->indisenabled = index->indisenabled; /* Copy xmin too, as that is needed to make sense of indcheckxmin */ HeapTupleHeaderSetXmin(relation->rd_indextuple->t_data, diff --git a/src/include/catalog/index.h b/src/include/catalog/index.h index 4daa8bef5ee..a390ab76789 100644 --- a/src/include/catalog/index.h +++ b/src/include/catalog/index.h @@ -65,6 +65,7 @@ extern void index_check_primary_key(Relation heapRel, #define INDEX_CREATE_IF_NOT_EXISTS (1 << 4) #define INDEX_CREATE_PARTITIONED (1 << 5) #define INDEX_CREATE_INVALID (1 << 6) +#define INDEX_CREATE_ENABLED (1 << 7) extern Oid index_create(Relation heapRelation, const char *indexRelationName, diff --git a/src/include/catalog/pg_index.h b/src/include/catalog/pg_index.h index 4392b9d221d..766a52193cb 100644 --- a/src/include/catalog/pg_index.h +++ b/src/include/catalog/pg_index.h @@ -44,6 +44,7 @@ CATALOG(pg_index,2610,IndexRelationId) BKI_SCHEMA_MACRO bool indisready; /* is this index ready for inserts? */ bool indislive; /* is this index alive at all? */ bool indisreplident; /* is this index the identity for replication? */ + bool indisenabled; /* is this index enabled for use by queries? */ /* variable-length fields start here, but we allow direct access to indkey */ int2vector indkey BKI_FORCE_NOT_NULL; /* column numbers of indexed cols, diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h index df331b1c0d9..aeb9ac844a1 100644 --- a/src/include/nodes/parsenodes.h +++ b/src/include/nodes/parsenodes.h @@ -2472,6 +2472,8 @@ typedef enum AlterTableType AT_SetIdentity, /* SET identity column options */ AT_DropIdentity, /* DROP IDENTITY */ AT_ReAddStatistics, /* internal to commands/tablecmds.c */ + AT_EnableIndex, /* ENABLE INDEX */ + AT_DisableIndex, /* DISABLE INDEX */ } AlterTableType; typedef struct AlterTableCmd /* one subcommand of an ALTER TABLE */ @@ -3468,6 +3470,7 @@ typedef struct IndexStmt bool if_not_exists; /* just do nothing if index already exists? */ bool reset_default_tblspc; /* reset default_tablespace prior to * executing */ + bool isenabled; /* true if ENABLE (default), false if DISABLE */ } IndexStmt; /* ---------------------- diff --git a/src/include/nodes/pathnodes.h b/src/include/nodes/pathnodes.h index c24a1fc8514..a9833fe2a64 100644 --- a/src/include/nodes/pathnodes.h +++ b/src/include/nodes/pathnodes.h @@ -1239,6 +1239,8 @@ struct IndexOptInfo /* AM's cost estimator */ /* Rather than include amapi.h here, we declare amcostestimate like this */ void (*amcostestimate) (struct PlannerInfo *, struct IndexPath *, double, Cost *, Cost *, Selectivity *, double *, double *) pg_node_attr(read_write_ignore); + /* true if this index is enabled */ + bool enabled; }; /* diff --git a/src/test/regress/expected/create_index.out b/src/test/regress/expected/create_index.out index 15be0043ad4..84461f234b4 100644 --- a/src/test/regress/expected/create_index.out +++ b/src/test/regress/expected/create_index.out @@ -3394,6 +3394,511 @@ ERROR: REINDEX SCHEMA cannot run inside a transaction block END; -- concurrently REINDEX SCHEMA CONCURRENTLY schema_to_reindex; +-- Test enable/disable functionality for indexes +CREATE TABLE index_test( + id INT PRIMARY KEY, + data TEXT, + num INT, + vector INT[], + range INT4RANGE +); +INSERT INTO index_test +SELECT + g, + 'data ' || g, + g % 100, + ARRAY[g, g+1, g+2], + int4range(g, g+10) +FROM generate_series(1, 1000) g; +-- Function for testing +CREATE FUNCTION get_data_length(text) RETURNS INT AS $$ + SELECT length($1); +$$ LANGUAGE SQL IMMUTABLE; +-- Helper function to show index status +CREATE OR REPLACE FUNCTION show_index_status(index_name text) +RETURNS TABLE ( + indexrelid regclass, + indisvalid boolean, + indisready boolean, + indislive boolean, + indisenabled boolean +) AS $$ +BEGIN + RETURN QUERY EXECUTE format(' + SELECT indexrelid::regclass, indisvalid, indisready, indislive, indisenabled + FROM pg_index + WHERE indexrelid = %L::regclass', index_name); +END; +$$ LANGUAGE plpgsql; +-- Create and test each index type +-- 1. Basic single-column index +CREATE INDEX idx_single ON index_test(data); +SELECT show_index_status('idx_single'); + show_index_status +---------------------- + (idx_single,t,t,t,t) +(1 row) + +EXPLAIN (COSTS OFF) SELECT * FROM index_test WHERE data = 'data 500'; + QUERY PLAN +----------------------------------------------- + Bitmap Heap Scan on index_test + Recheck Cond: (data = 'data 500'::text) + -> Bitmap Index Scan on idx_single + Index Cond: (data = 'data 500'::text) +(4 rows) + +ALTER INDEX idx_single DISABLE; +SELECT show_index_status('idx_single'); + show_index_status +---------------------- + (idx_single,t,t,t,f) +(1 row) + +EXPLAIN (COSTS OFF) SELECT * FROM index_test WHERE data = 'data 500'; + QUERY PLAN +------------------------------------- + Seq Scan on index_test + Filter: (data = 'data 500'::text) +(2 rows) + +ALTER INDEX idx_single ENABLE; +SELECT show_index_status('idx_single'); + show_index_status +---------------------- + (idx_single,t,t,t,t) +(1 row) + +-- 2. Multi-column index +CREATE INDEX idx_multi ON index_test(num, data); +SELECT show_index_status('idx_multi'); + show_index_status +--------------------- + (idx_multi,t,t,t,t) +(1 row) + +EXPLAIN (COSTS OFF) SELECT * FROM index_test WHERE num = 50 AND data > 'data 500'; + QUERY PLAN +---------------------------------------------------------------- + Bitmap Heap Scan on index_test + Recheck Cond: ((num = 50) AND (data > 'data 500'::text)) + -> Bitmap Index Scan on idx_multi + Index Cond: ((num = 50) AND (data > 'data 500'::text)) +(4 rows) + +ALTER INDEX idx_multi DISABLE; +SELECT show_index_status('idx_multi'); + show_index_status +--------------------- + (idx_multi,t,t,t,f) +(1 row) + +EXPLAIN (COSTS OFF) SELECT * FROM index_test WHERE num = 50 AND data > 'data 500'; + QUERY PLAN +------------------------------------------------------ + Seq Scan on index_test + Filter: ((data > 'data 500'::text) AND (num = 50)) +(2 rows) + +ALTER INDEX idx_multi ENABLE; +SELECT show_index_status('idx_multi'); + show_index_status +--------------------- + (idx_multi,t,t,t,t) +(1 row) + +-- 3. Partial index +CREATE INDEX idx_partial ON index_test(num) WHERE num < 50; +SELECT show_index_status('idx_partial'); + show_index_status +----------------------- + (idx_partial,t,t,t,t) +(1 row) + +EXPLAIN (COSTS OFF) SELECT * FROM index_test WHERE num = 25; + QUERY PLAN +---------------------------------------- + Bitmap Heap Scan on index_test + Recheck Cond: (num = 25) + -> Bitmap Index Scan on idx_partial + Index Cond: (num = 25) +(4 rows) + +ALTER INDEX idx_partial DISABLE; +SELECT show_index_status('idx_partial'); + show_index_status +----------------------- + (idx_partial,t,t,t,f) +(1 row) + +EXPLAIN (COSTS OFF) SELECT * FROM index_test WHERE num = 25; + QUERY PLAN +-------------------------------------- + Bitmap Heap Scan on index_test + Recheck Cond: (num = 25) + -> Bitmap Index Scan on idx_multi + Index Cond: (num = 25) +(4 rows) + +ALTER INDEX idx_partial ENABLE; +SELECT show_index_status('idx_partial'); + show_index_status +----------------------- + (idx_partial,t,t,t,t) +(1 row) + +-- 4. Expression index +CREATE INDEX idx_expression ON index_test((lower(data))); +SELECT show_index_status('idx_expression'); + show_index_status +-------------------------- + (idx_expression,t,t,t,t) +(1 row) + +EXPLAIN (COSTS OFF) SELECT * FROM index_test WHERE lower(data) = 'data 500'; + QUERY PLAN +------------------------------------------------------ + Bitmap Heap Scan on index_test + Recheck Cond: (lower(data) = 'data 500'::text) + -> Bitmap Index Scan on idx_expression + Index Cond: (lower(data) = 'data 500'::text) +(4 rows) + +ALTER INDEX idx_expression DISABLE; +SELECT show_index_status('idx_expression'); + show_index_status +-------------------------- + (idx_expression,t,t,t,f) +(1 row) + +EXPLAIN (COSTS OFF) SELECT * FROM index_test WHERE lower(data) = 'data 500'; + QUERY PLAN +-------------------------------------------- + Seq Scan on index_test + Filter: (lower(data) = 'data 500'::text) +(2 rows) + +ALTER INDEX idx_expression ENABLE; +SELECT show_index_status('idx_expression'); + show_index_status +-------------------------- + (idx_expression,t,t,t,t) +(1 row) + +-- 5. GIN index +CREATE INDEX idx_gin ON index_test USING gin(vector); +SELECT show_index_status('idx_gin'); + show_index_status +------------------- + (idx_gin,t,t,t,t) +(1 row) + +EXPLAIN (COSTS OFF) SELECT * FROM index_test WHERE vector @> ARRAY[500]; + QUERY PLAN +---------------------------------------------------- + Bitmap Heap Scan on index_test + Recheck Cond: (vector @> '{500}'::integer[]) + -> Bitmap Index Scan on idx_gin + Index Cond: (vector @> '{500}'::integer[]) +(4 rows) + +ALTER INDEX idx_gin DISABLE; +SELECT show_index_status('idx_gin'); + show_index_status +------------------- + (idx_gin,t,t,t,f) +(1 row) + +EXPLAIN (COSTS OFF) SELECT * FROM index_test WHERE vector @> ARRAY[500]; + QUERY PLAN +------------------------------------------ + Seq Scan on index_test + Filter: (vector @> '{500}'::integer[]) +(2 rows) + +ALTER INDEX idx_gin ENABLE; +SELECT show_index_status('idx_gin'); + show_index_status +------------------- + (idx_gin,t,t,t,t) +(1 row) + +-- 6. GiST index +CREATE INDEX idx_gist ON index_test USING gist(range); +SELECT show_index_status('idx_gist'); + show_index_status +-------------------- + (idx_gist,t,t,t,t) +(1 row) + +EXPLAIN (COSTS OFF) SELECT * FROM index_test WHERE range && int4range(100, 110); + QUERY PLAN +------------------------------------------------------- + Bitmap Heap Scan on index_test + Recheck Cond: (range && '[100,110)'::int4range) + -> Bitmap Index Scan on idx_gist + Index Cond: (range && '[100,110)'::int4range) +(4 rows) + +ALTER INDEX idx_gist DISABLE; +SELECT show_index_status('idx_gist'); + show_index_status +-------------------- + (idx_gist,t,t,t,f) +(1 row) + +EXPLAIN (COSTS OFF) SELECT * FROM index_test WHERE range && int4range(100, 110); + QUERY PLAN +--------------------------------------------- + Seq Scan on index_test + Filter: (range && '[100,110)'::int4range) +(2 rows) + +ALTER INDEX idx_gist ENABLE; +SELECT show_index_status('idx_gist'); + show_index_status +-------------------- + (idx_gist,t,t,t,t) +(1 row) + +-- 7. Covering index +CREATE INDEX idx_covering ON index_test(num) INCLUDE (data); +SELECT show_index_status('idx_covering'); + show_index_status +------------------------ + (idx_covering,t,t,t,t) +(1 row) + +EXPLAIN (COSTS OFF) SELECT num, data FROM index_test WHERE num = 50; + QUERY PLAN +----------------------------------------- + Bitmap Heap Scan on index_test + Recheck Cond: (num = 50) + -> Bitmap Index Scan on idx_covering + Index Cond: (num = 50) +(4 rows) + +ALTER INDEX idx_covering DISABLE; +SELECT show_index_status('idx_covering'); + show_index_status +------------------------ + (idx_covering,t,t,t,f) +(1 row) + +EXPLAIN (COSTS OFF) SELECT num, data FROM index_test WHERE num = 50; + QUERY PLAN +-------------------------------------- + Bitmap Heap Scan on index_test + Recheck Cond: (num = 50) + -> Bitmap Index Scan on idx_multi + Index Cond: (num = 50) +(4 rows) + +ALTER INDEX idx_covering ENABLE; +SELECT show_index_status('idx_covering'); + show_index_status +------------------------ + (idx_covering,t,t,t,t) +(1 row) + +-- 8. Unique index +CREATE UNIQUE INDEX idx_unique ON index_test(id, data); +SELECT show_index_status('idx_unique'); + show_index_status +---------------------- + (idx_unique,t,t,t,t) +(1 row) + +EXPLAIN (COSTS OFF) SELECT * FROM index_test WHERE id = 500 AND data = 'data 500'; + QUERY PLAN +---------------------------------------------------------- + Index Scan using idx_unique on index_test + Index Cond: ((id = 500) AND (data = 'data 500'::text)) +(2 rows) + +ALTER INDEX idx_unique DISABLE; +SELECT show_index_status('idx_unique'); + show_index_status +---------------------- + (idx_unique,t,t,t,f) +(1 row) + +EXPLAIN (COSTS OFF) SELECT * FROM index_test WHERE id = 500 AND data = 'data 500'; + QUERY PLAN +------------------------------------------------ + Index Scan using index_test_pkey on index_test + Index Cond: (id = 500) + Filter: (data = 'data 500'::text) +(3 rows) + +ALTER INDEX idx_unique ENABLE; +SELECT show_index_status('idx_unique'); + show_index_status +---------------------- + (idx_unique,t,t,t,t) +(1 row) + +-- 9. Function-based index +CREATE INDEX idx_func ON index_test(get_data_length(data)); +SELECT show_index_status('idx_func'); + show_index_status +-------------------- + (idx_func,t,t,t,t) +(1 row) + +EXPLAIN (COSTS OFF) SELECT * FROM index_test WHERE get_data_length(data) = 10; + QUERY PLAN +----------------------------------------- + Bitmap Heap Scan on index_test + Recheck Cond: (length(data) = 10) + -> Bitmap Index Scan on idx_func + Index Cond: (length(data) = 10) +(4 rows) + +ALTER INDEX idx_func DISABLE; +SELECT show_index_status('idx_func'); + show_index_status +-------------------- + (idx_func,t,t,t,f) +(1 row) + +EXPLAIN (COSTS OFF) SELECT * FROM index_test WHERE get_data_length(data) = 10; + QUERY PLAN +------------------------------- + Seq Scan on index_test + Filter: (length(data) = 10) +(2 rows) + +ALTER INDEX idx_func ENABLE; +SELECT show_index_status('idx_func'); + show_index_status +-------------------- + (idx_func,t,t,t,t) +(1 row) + +-- 10. Join index +CREATE TABLE join_test (id INT PRIMARY KEY, ref_id INT); +INSERT INTO join_test SELECT g, g % 100 FROM generate_series(1, 1000) g; +CREATE INDEX idx_join ON join_test(ref_id); +SELECT show_index_status('idx_join'); + show_index_status +-------------------- + (idx_join,t,t,t,t) +(1 row) + +EXPLAIN (COSTS OFF) +SELECT jt.id, it.data +FROM join_test jt +JOIN index_test it ON jt.ref_id = it.num +WHERE jt.id BETWEEN 100 AND 200; + QUERY PLAN +--------------------------------------------------------------- + Hash Join + Hash Cond: (it.num = jt.ref_id) + -> Seq Scan on index_test it + -> Hash + -> Bitmap Heap Scan on join_test jt + Recheck Cond: ((id >= 100) AND (id <= 200)) + -> Bitmap Index Scan on join_test_pkey + Index Cond: ((id >= 100) AND (id <= 200)) +(8 rows) + +-- Disable all indexes to force seq scan +ALTER INDEX idx_join DISABLE; +ALTER INDEX join_test_pkey DISABLE; +SELECT show_index_status('idx_join'); + show_index_status +-------------------- + (idx_join,t,t,t,f) +(1 row) + +SELECT show_index_status('join_test_pkey'); + show_index_status +-------------------------- + (join_test_pkey,t,t,t,f) +(1 row) + +EXPLAIN (COSTS OFF) +SELECT jt.id, it.data +FROM join_test jt +JOIN index_test it ON jt.ref_id = it.num +WHERE jt.id BETWEEN 100 AND 200; + QUERY PLAN +----------------------------------------------------- + Hash Join + Hash Cond: (it.num = jt.ref_id) + -> Seq Scan on index_test it + -> Hash + -> Seq Scan on join_test jt + Filter: ((id >= 100) AND (id <= 200)) +(6 rows) + +ALTER INDEX idx_join ENABLE; +ALTER INDEX join_test_pkey ENABLE; +SELECT show_index_status('idx_join'); + show_index_status +-------------------- + (idx_join,t,t,t,t) +(1 row) + +SELECT show_index_status('join_test_pkey'); + show_index_status +-------------------------- + (join_test_pkey,t,t,t,t) +(1 row) + +-- Test REINDEX CONCURRENTLY +REINDEX INDEX CONCURRENTLY idx_join; +SELECT show_index_status('idx_join'); + show_index_status +-------------------- + (idx_join,t,t,t,t) +(1 row) + +ALTER INDEX idx_join DISABLE; +SELECT show_index_status('idx_join'); + show_index_status +-------------------- + (idx_join,t,t,t,f) +(1 row) + +REINDEX INDEX CONCURRENTLY idx_join; +SELECT show_index_status('idx_join'); + show_index_status +-------------------- + (idx_join,t,t,t,f) +(1 row) + +SELECT pg_get_indexdef('idx_join'::regclass); + pg_get_indexdef +----------------------------------------------------------------------------------- + CREATE INDEX idx_join ON schema_to_reindex.join_test USING btree (ref_id) DISABLE +(1 row) + +ALTER INDEX idx_join ENABLE; +SELECT pg_get_indexdef('idx_join'::regclass); + pg_get_indexdef +--------------------------------------------------------------------------- + CREATE INDEX idx_join ON schema_to_reindex.join_test USING btree (ref_id) +(1 row) + +-- Test enable/disable index with indcheckxmin being true +CREATE TABLE indcheckxmin_test(id int PRIMARY KEY); +INSERT INTO indcheckxmin_test VALUES (1), (2), (3); +CREATE INDEX CONCURRENTLY idx_checkxmin ON indcheckxmin_test(id); +BEGIN; +-- Simulate indcheckxmin being true +UPDATE pg_index SET indcheckxmin = true WHERE indexrelid = 'idx_checkxmin'::regclass; +ALTER INDEX idx_checkxmin DISABLE; -- expect fail +ERROR: cannot enable/disable index while indcheckxmin is true +HINT: Wait for all transactions that might see inconsistent HOT chains to complete +ROLLBACK; +-- Clean up +DROP TABLE index_test; +DROP TABLE join_test; +DROP FUNCTION get_data_length; +DROP FUNCTION show_index_status; +DROP TABLE indcheckxmin_test CASCADE; -- Failure for unauthorized user CREATE ROLE regress_reindexuser NOLOGIN; SET SESSION ROLE regress_reindexuser; diff --git a/src/test/regress/sql/create_index.sql b/src/test/regress/sql/create_index.sql index 6b3852dddd8..48569a0924a 100644 --- a/src/test/regress/sql/create_index.sql +++ b/src/test/regress/sql/create_index.sql @@ -1435,6 +1435,198 @@ END; -- concurrently REINDEX SCHEMA CONCURRENTLY schema_to_reindex; +-- Test enable/disable functionality for indexes + +CREATE TABLE index_test( + id INT PRIMARY KEY, + data TEXT, + num INT, + vector INT[], + range INT4RANGE +); + +INSERT INTO index_test +SELECT + g, + 'data ' || g, + g % 100, + ARRAY[g, g+1, g+2], + int4range(g, g+10) +FROM generate_series(1, 1000) g; + +-- Function for testing +CREATE FUNCTION get_data_length(text) RETURNS INT AS $$ + SELECT length($1); +$$ LANGUAGE SQL IMMUTABLE; + +-- Helper function to show index status +CREATE OR REPLACE FUNCTION show_index_status(index_name text) +RETURNS TABLE ( + indexrelid regclass, + indisvalid boolean, + indisready boolean, + indislive boolean, + indisenabled boolean +) AS $$ +BEGIN + RETURN QUERY EXECUTE format(' + SELECT indexrelid::regclass, indisvalid, indisready, indislive, indisenabled + FROM pg_index + WHERE indexrelid = %L::regclass', index_name); +END; +$$ LANGUAGE plpgsql; + +-- Create and test each index type +-- 1. Basic single-column index +CREATE INDEX idx_single ON index_test(data); +SELECT show_index_status('idx_single'); +EXPLAIN (COSTS OFF) SELECT * FROM index_test WHERE data = 'data 500'; +ALTER INDEX idx_single DISABLE; +SELECT show_index_status('idx_single'); +EXPLAIN (COSTS OFF) SELECT * FROM index_test WHERE data = 'data 500'; +ALTER INDEX idx_single ENABLE; +SELECT show_index_status('idx_single'); + +-- 2. Multi-column index +CREATE INDEX idx_multi ON index_test(num, data); +SELECT show_index_status('idx_multi'); +EXPLAIN (COSTS OFF) SELECT * FROM index_test WHERE num = 50 AND data > 'data 500'; +ALTER INDEX idx_multi DISABLE; +SELECT show_index_status('idx_multi'); +EXPLAIN (COSTS OFF) SELECT * FROM index_test WHERE num = 50 AND data > 'data 500'; +ALTER INDEX idx_multi ENABLE; +SELECT show_index_status('idx_multi'); + +-- 3. Partial index +CREATE INDEX idx_partial ON index_test(num) WHERE num < 50; +SELECT show_index_status('idx_partial'); +EXPLAIN (COSTS OFF) SELECT * FROM index_test WHERE num = 25; +ALTER INDEX idx_partial DISABLE; +SELECT show_index_status('idx_partial'); +EXPLAIN (COSTS OFF) SELECT * FROM index_test WHERE num = 25; +ALTER INDEX idx_partial ENABLE; +SELECT show_index_status('idx_partial'); + +-- 4. Expression index +CREATE INDEX idx_expression ON index_test((lower(data))); +SELECT show_index_status('idx_expression'); +EXPLAIN (COSTS OFF) SELECT * FROM index_test WHERE lower(data) = 'data 500'; +ALTER INDEX idx_expression DISABLE; +SELECT show_index_status('idx_expression'); +EXPLAIN (COSTS OFF) SELECT * FROM index_test WHERE lower(data) = 'data 500'; +ALTER INDEX idx_expression ENABLE; +SELECT show_index_status('idx_expression'); + +-- 5. GIN index +CREATE INDEX idx_gin ON index_test USING gin(vector); +SELECT show_index_status('idx_gin'); +EXPLAIN (COSTS OFF) SELECT * FROM index_test WHERE vector @> ARRAY[500]; +ALTER INDEX idx_gin DISABLE; +SELECT show_index_status('idx_gin'); +EXPLAIN (COSTS OFF) SELECT * FROM index_test WHERE vector @> ARRAY[500]; +ALTER INDEX idx_gin ENABLE; +SELECT show_index_status('idx_gin'); + +-- 6. GiST index +CREATE INDEX idx_gist ON index_test USING gist(range); +SELECT show_index_status('idx_gist'); +EXPLAIN (COSTS OFF) SELECT * FROM index_test WHERE range && int4range(100, 110); +ALTER INDEX idx_gist DISABLE; +SELECT show_index_status('idx_gist'); +EXPLAIN (COSTS OFF) SELECT * FROM index_test WHERE range && int4range(100, 110); +ALTER INDEX idx_gist ENABLE; +SELECT show_index_status('idx_gist'); + +-- 7. Covering index +CREATE INDEX idx_covering ON index_test(num) INCLUDE (data); +SELECT show_index_status('idx_covering'); +EXPLAIN (COSTS OFF) SELECT num, data FROM index_test WHERE num = 50; +ALTER INDEX idx_covering DISABLE; +SELECT show_index_status('idx_covering'); +EXPLAIN (COSTS OFF) SELECT num, data FROM index_test WHERE num = 50; +ALTER INDEX idx_covering ENABLE; +SELECT show_index_status('idx_covering'); + +-- 8. Unique index +CREATE UNIQUE INDEX idx_unique ON index_test(id, data); +SELECT show_index_status('idx_unique'); +EXPLAIN (COSTS OFF) SELECT * FROM index_test WHERE id = 500 AND data = 'data 500'; +ALTER INDEX idx_unique DISABLE; +SELECT show_index_status('idx_unique'); +EXPLAIN (COSTS OFF) SELECT * FROM index_test WHERE id = 500 AND data = 'data 500'; +ALTER INDEX idx_unique ENABLE; +SELECT show_index_status('idx_unique'); + +-- 9. Function-based index +CREATE INDEX idx_func ON index_test(get_data_length(data)); +SELECT show_index_status('idx_func'); +EXPLAIN (COSTS OFF) SELECT * FROM index_test WHERE get_data_length(data) = 10; +ALTER INDEX idx_func DISABLE; +SELECT show_index_status('idx_func'); +EXPLAIN (COSTS OFF) SELECT * FROM index_test WHERE get_data_length(data) = 10; +ALTER INDEX idx_func ENABLE; +SELECT show_index_status('idx_func'); + +-- 10. Join index +CREATE TABLE join_test (id INT PRIMARY KEY, ref_id INT); +INSERT INTO join_test SELECT g, g % 100 FROM generate_series(1, 1000) g; +CREATE INDEX idx_join ON join_test(ref_id); +SELECT show_index_status('idx_join'); + +EXPLAIN (COSTS OFF) +SELECT jt.id, it.data +FROM join_test jt +JOIN index_test it ON jt.ref_id = it.num +WHERE jt.id BETWEEN 100 AND 200; + +-- Disable all indexes to force seq scan +ALTER INDEX idx_join DISABLE; +ALTER INDEX join_test_pkey DISABLE; +SELECT show_index_status('idx_join'); +SELECT show_index_status('join_test_pkey'); + +EXPLAIN (COSTS OFF) +SELECT jt.id, it.data +FROM join_test jt +JOIN index_test it ON jt.ref_id = it.num +WHERE jt.id BETWEEN 100 AND 200; + +ALTER INDEX idx_join ENABLE; +ALTER INDEX join_test_pkey ENABLE; +SELECT show_index_status('idx_join'); +SELECT show_index_status('join_test_pkey'); + +-- Test REINDEX CONCURRENTLY +REINDEX INDEX CONCURRENTLY idx_join; +SELECT show_index_status('idx_join'); + +ALTER INDEX idx_join DISABLE; +SELECT show_index_status('idx_join'); +REINDEX INDEX CONCURRENTLY idx_join; +SELECT show_index_status('idx_join'); + +SELECT pg_get_indexdef('idx_join'::regclass); +ALTER INDEX idx_join ENABLE; +SELECT pg_get_indexdef('idx_join'::regclass); + +-- Test enable/disable index with indcheckxmin being true + +CREATE TABLE indcheckxmin_test(id int PRIMARY KEY); +INSERT INTO indcheckxmin_test VALUES (1), (2), (3); +CREATE INDEX CONCURRENTLY idx_checkxmin ON indcheckxmin_test(id); +BEGIN; +-- Simulate indcheckxmin being true +UPDATE pg_index SET indcheckxmin = true WHERE indexrelid = 'idx_checkxmin'::regclass; +ALTER INDEX idx_checkxmin DISABLE; -- expect fail +ROLLBACK; + +-- Clean up +DROP TABLE index_test; +DROP TABLE join_test; +DROP FUNCTION get_data_length; +DROP FUNCTION show_index_status; +DROP TABLE indcheckxmin_test CASCADE; + -- Failure for unauthorized user CREATE ROLE regress_reindexuser NOLOGIN; SET SESSION ROLE regress_reindexuser; -- 2.39.5 (Apple Git-154)