Re: [PATCH] Re: Proposal to Enable/Disable Index using ALTER INDEX

Shayon Mukherjee Mon, 07 Apr 2025 13:48:34 -0700

On Mon, Apr 7, 2025 at 4:01 PM Sami Imseih <samims...@gmail.com> wrote:


> Thanks for the update!
>
> The changes in v15 look good to me. The patch does need to be rebased,
> and I also think you should add a tab-complete for CREATE INDEX
>
>
> simseih@bcd07415af92 postgresql % git diff
> diff --git a/src/bin/psql/tab-complete.in.c
> b/src/bin/psql/tab-complete.in.c
> index 8e2eb50205e..f1853a68ccc 100644
> --- a/src/bin/psql/tab-complete.in.c
> +++ b/src/bin/psql/tab-complete.in.c
> @@ -3434,6 +3434,8 @@ match_previous_words(int pattern_id,
>                          !TailMatches("POLICY", MatchAny, MatchAny,
> MatchAny, MatchAny, MatchAny) &&
>                          !TailMatches("FOR", MatchAny, MatchAny, MatchAny))
>                 COMPLETE_WITH("(");
> +       else if (TailMatches("*)"))
> +               COMPLETE_WITH("VISIBLE", "INVISIBLE");
>
>         /* CREATE OR REPLACE */
>         else if (Matches("CREATE", "OR"))
>
> IMO, with the above in place, this patch is RFC.
>
>
>
Thank you Sami, really appreciate it!

Attached v16 with feedback and rebased.

Thanks
Shayon

From b1833753476d2c7d062c6d3900b2671a09d32e12 Mon Sep 17 00:00:00 2001
From: Shayon Mukherjee <shayonj@gmail.com>
Date: Sun, 12 Jan 2025 14:34:48 -0500
Subject: [PATCH v16] Introduce the ability to set index visibility using ALTER
 INDEX

This patch introduces index visibility control using ALTER INDEX and CREATE INDEX
commands.

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 patch can be applied for testing as well.

Note: The patch has gone through a few iterations. Earlier versions of the patch had the ENABLE/DISABLE grammar.
The current version has the VISIBLE/INVISIBLE grammar. So, you will the local variable names using the new grammar accordingly.

Implementation details:

- New Grammar:
  * ALTER INDEX ... VISIBLE/INVISIBLE
  * CREATE INDEX ... INVISIBLE

- Default state is visible. Indexes are only invisible when explicitly
  instructed via CREATE INDEX ... INVISIBLE or ALTER INDEX ... INVISIBLE.

- Primary Key and Unique constraint indexes are always visible. The
  VISIBLE/INVISIBLE grammar is supported for these types of indexes and they can
  be made invisible via ALTER INDEX ... INVISIBLE.

- ALTER INDEX ... VISIBLE/INVISIBLE performs update on the relevant row in pg_index
  catalog

- pg_get_indexdef() supports the new functionality and grammar. This change is
  reflected in \d output for tables and pg_dump. We show the INVISIBLE syntax accordingly.

- Added force_invisible_index GUC parameter that forces the planner to use invisible
  indexes. This is useful for testing and validating index behavior without changing
  their visibility state. Based on feedback from Sami S [2]

- Updated create_index.sql regression test to cover the new grammar and verify
  that invisible indexes are not used in queries. The test covers:

  - 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 indisvisible attribute to the IndexOptInfo structure.

- Modifies get_relation_info in plancat.c to skip invisible indexes entirely, thus reducing the number of places we need to check if an index is invisible 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 understand perhaps (?).

- No changes are made to stop the index from getting maintained. This way we ensure no
  data loss or corruption when index is made visible again.

- TOAST indexes are supported and visible by default as well.

- REINDEX CONCURRENTLY is supported as well and existing state of pg_index.indisvisible
  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 invisible).

- Incorporated DavidR's feedback from [4] around documentation and also you will see that by skipping invisible indexes entirely from get_relation_info in plancat.c (as mentioned above), we address the other mentioned issues as well.

- Lastly, protects against the case where indcheckxmin is true by raising ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE.

Looking forward to any and all feedback on this patch, including but not limited to code quality, tests, fundamental logic.

[1] https://www.postgresql.org/message-id/CANqtF-oXKe0M%3D0QOih6H%2BsZRjE2BWAbkW_1%2B9nMEAMLxUJg5jA%40mail.gmail.com
[2] https://www.postgresql.org/message-id/CAA5RZ0udzydObMDi65C59-oq54B9ZmjSZ1wVH3h%2Bv4XiVm6QDA%40mail.gmail.com
[3] https://www.postgresql.org/message-id/3465209.1727202064%40sss.pgh.pa.us
[4] https://www.postgresql.org/message-id/CAApHDvpUNu%3DiVcdJ74sypvgeaCF%2Btfpyb8VRhZaF7DTd1xVr7g%40mail.gmail.com
---
 doc/src/sgml/catalogs.sgml                    |  11 +
 doc/src/sgml/config.sgml                      |  16 +
 doc/src/sgml/indices.sgml                     |  21 +
 doc/src/sgml/ref/alter_index.sgml             |  36 +
 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              |  92 +-
 src/backend/optimizer/util/plancat.c          |  30 +-
 src/backend/parser/gram.y                     |  56 +-
 src/backend/parser/parse_utilcmd.c            |   3 +
 src/backend/utils/adt/ruleutils.c             |   4 +
 src/backend/utils/cache/relcache.c            |   1 +
 src/backend/utils/misc/guc_tables.c           |  10 +
 src/backend/utils/misc/postgresql.conf.sample |   1 +
 src/bin/pg_dump/pg_dump.c                     |  21 +-
 src/bin/pg_dump/pg_dump.h                     |   2 +
 src/bin/pg_dump/t/002_pg_dump.pl              |  29 +
 src/bin/psql/describe.c                       |  15 +-
 src/bin/psql/tab-complete.in.c                |   5 +-
 src/include/catalog/index.h                   |   2 +-
 src/include/catalog/pg_index.h                |   2 +
 src/include/nodes/parsenodes.h                |   4 +
 src/include/optimizer/optimizer.h             |   5 +
 src/include/parser/kwlist.h                   |   2 +
 src/include/utils/guc_hooks.h                 |   1 +
 src/test/modules/test_ddl_deparse/Makefile    |   3 +-
 .../test_ddl_deparse/expected/alter_index.out |  29 +
 src/test/modules/test_ddl_deparse/meson.build |   1 +
 .../test_ddl_deparse/sql/alter_index.sql      |  18 +
 .../test_ddl_deparse/test_ddl_deparse.c       |   6 +
 src/test/regress/expected/create_index.out    | 795 ++++++++++++++++++
 src/test/regress/sql/create_index.sql         | 321 +++++++
 35 files changed, 1584 insertions(+), 26 deletions(-)
 create mode 100644 src/test/modules/test_ddl_deparse/expected/alter_index.out
 create mode 100644 src/test/modules/test_ddl_deparse/sql/alter_index.sql

diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml
index cbd4e40a32..3307df883b 100644
--- a/doc/src/sgml/catalogs.sgml
+++ b/doc/src/sgml/catalogs.sgml
@@ -4618,6 +4618,17 @@ SCRAM-SHA-256$<replaceable>&lt;iteration count&gt;</replaceable>:<replaceable>&l
        partial index.
       </para></entry>
      </row>
+
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>indisvisible</structfield> <type>bool</type>
+      </para>
+      <para>
+       If true, the index is currently visible to the planner and may be used for queries.
+       If false, the index is invisible and may not be used for queries,
+       but is still updated when the table is modified. Default is true.
+      </para></entry>
+     </row>
     </tbody>
    </tgroup>
   </table>
diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index a8542fe41c..32087c07f9 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -5778,6 +5778,22 @@ ANY <replaceable class="parameter">num_sync</replaceable> ( <replaceable class="
       </listitem>
      </varlistentry>
 
+     <varlistentry id="guc-force-invisible-index" xreflabel="use_invisible_index">
+      <term><varname>use_invisible_index</varname> (<type>boolean</type>)
+      <indexterm>
+       <primary><varname>use_invisible_index</varname> configuration parameter</primary>
+      </indexterm>
+      </term>
+      <listitem>
+       <para>
+        Forces the query planner to consider indexes that have been marked as invisible using
+        <command>ALTER INDEX ... INVISIBLE</command>. This parameter is useful for selective
+        use of invisible indexes in specific application contexts. The default
+        is <literal>off</literal>.
+       </para>
+      </listitem>
+     </varlistentry>
+
      </variablelist>
      </sect2>
      <sect2 id="runtime-config-query-constants">
diff --git a/doc/src/sgml/indices.sgml b/doc/src/sgml/indices.sgml
index 9c4f76abf0..97bb599a05 100644
--- a/doc/src/sgml/indices.sgml
+++ b/doc/src/sgml/indices.sgml
@@ -1673,6 +1673,27 @@ CREATE INDEX test1c_content_y_index ON test1c (content COLLATE "y");
      <productname>PostgreSQL</productname> developers to examine the issue.
     </para>
    </listitem>
+
+   <listitem>
+    <para>
+     Invisible indexes provide a convenient way to experiment with indexes
+     without needing to fully drop and recreate them. You can create a new index as
+     invisible with <command>CREATE INDEX ... INVISIBLE</command> or mark
+     an existing index invisible with <command>ALTER INDEX ... INVISIBLE</command>.
+     When an index is invisible, the planner will ignore it by default.
+     To test the index's effect on performance, set the
+     <varname>use_invisible_index</varname> parameter to <literal>on</literal>.
+     This allows you to compare query performance with and without the index
+     before making it visible to all queries with
+     <command>ALTER INDEX ... VISIBLE</command>.
+    </para>
+    <para>
+     Similarly, before dropping an existing index that appears unused,
+     consider marking it invisible to verify that query performance doesn't
+     degrade. Check <structname>pg_stat_user_indexes</structname>.<structfield>idx_scan</structfield>
+     to identify potentially unused indexes.
+    </para>
+   </listitem>
   </itemizedlist>
  </sect1>
 </chapter>
diff --git a/doc/src/sgml/ref/alter_index.sgml b/doc/src/sgml/ref/alter_index.sgml
index 1d42d05d85..a49d28a52d 100644
--- a/doc/src/sgml/ref/alter_index.sgml
+++ b/doc/src/sgml/ref/alter_index.sgml
@@ -31,6 +31,7 @@ ALTER INDEX [ IF EXISTS ] <replaceable class="parameter">name</replaceable> ALTE
     SET STATISTICS <replaceable class="parameter">integer</replaceable>
 ALTER INDEX ALL IN TABLESPACE <replaceable class="parameter">name</replaceable> [ OWNED BY <replaceable class="parameter">role_name</replaceable> [, ... ] ]
     SET TABLESPACE <replaceable class="parameter">new_tablespace</replaceable> [ NOWAIT ]
+ALTER INDEX [ IF EXISTS ] <replaceable class="parameter">name</replaceable> { VISIBLE | INVISIBLE }
 </synopsis>
  </refsynopsisdiv>
 
@@ -159,6 +160,27 @@ ALTER INDEX ALL IN TABLESPACE <replaceable class="parameter">name</replaceable>
     </listitem>
    </varlistentry>
 
+   <varlistentry>
+    <term><literal>VISIBLE</literal></term>
+    <listitem>
+     <para>
+      Make the specified index visible. The index will be used for query planning.
+     </para>
+    </listitem>
+   </varlistentry>
+
+   <varlistentry>
+    <term><literal>INVISIBLE</literal></term>
+    <listitem>
+     <para>
+      Make the specified index invisible. The index will not be used for queries.
+      This can be useful for testing query performance with and without specific
+      indexes. If performance degrades after making an index invisible, it can be easily
+      be made visible using <literal>VISIBLE</literal>.
+     </para>
+    </listitem>
+   </varlistentry>
+
   </variablelist>
   </para>
 
@@ -301,6 +323,20 @@ CREATE INDEX coord_idx ON measured (x, y, (z + t));
 ALTER INDEX coord_idx ALTER COLUMN 3 SET STATISTICS 1000;
 </programlisting></para>
 
+  <para>
+   To make an index visible:
+<programlisting>
+ALTER INDEX idx_name VISIBLE;
+</programlisting>
+  </para>
+
+  <para>
+   To make an index invisible:
+<programlisting>
+ALTER INDEX idx_name INVISIBLE;
+</programlisting>
+  </para>
+
  </refsect1>
 
  <refsect1>
diff --git a/doc/src/sgml/ref/create_index.sgml b/doc/src/sgml/ref/create_index.sgml
index 208389e800..90d7d90a2b 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 ] <replaceable class=
     [ WITH ( <replaceable class="parameter">storage_parameter</replaceable> [= <replaceable class="parameter">value</replaceable>] [, ... ] ) ]
     [ TABLESPACE <replaceable class="parameter">tablespace_name</replaceable> ]
     [ WHERE <replaceable class="parameter">predicate</replaceable> ]
+    [ INVISIBLE ]
 </synopsis>
  </refsynopsisdiv>
 
@@ -380,6 +381,19 @@ CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [ IF NOT EXISTS ] <replaceable class=
       </listitem>
      </varlistentry>
 
+    <varlistentry>
+      <term><literal>INVISIBLE</literal></term>
+      <listitem>
+       <para>
+        Creates the index in an invisible state (default visible). An invisible index is
+        not used by the query planner for queries, 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 planning, allowing you to make it
+        visible later at a more convenient time. The index can be made visible later
+        using <command>ALTER INDEX ... VISIBLE</command>.
+       </para>
+      </listitem>
+     </varlistentry>
     </variablelist>
 
   <refsect2 id="sql-createindex-storage-parameters" xreflabel="Index Storage Parameters">
@@ -701,6 +715,14 @@ Indexes:
     partitioned index is a metadata only operation.
    </para>
 
+  <para>
+   When creating an index with the <literal>INVISIBLE</literal> option, the index
+   will be 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, so it can be made visible
+   later without needing to be rebuilt. By default all new indexes are visible.
+  </para>
+
   </refsect2>
  </refsect1>
 
@@ -980,6 +1002,13 @@ SELECT * FROM points
 CREATE INDEX CONCURRENTLY sales_quantity_index ON sales_table (quantity);
 </programlisting></para>
 
+  <para>
+   To create an index on the table <literal>test_table</literal> with the default
+   name, but have it initially invisible:
+<programlisting>
+CREATE INDEX ON test_table (col1) INVISIBLE;
+</programlisting>
+  </para>
  </refsect1>
 
  <refsect1>
diff --git a/src/backend/bootstrap/bootparse.y b/src/backend/bootstrap/bootparse.y
index 9833f52c1b..ea21511a20 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->isvisible = 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->isvisible = 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 739a92bdcc..2d8ceb60ce 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 isvisible);
 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 isvisible)
 {
 	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_indisvisible - 1] = BoolGetDatum(isvisible);
 	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_VISIBLE:
+ *			create the index as visible to query planner
  * 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		isvisible = (flags & INDEX_CREATE_VISIBLE) != 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,
+						isvisible);
 
 	/*
 	 * 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 visibility 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
+	 * visible, we also set the VISIBLE flag to maintain the same state in the
+	 * new index.
+	 */
+	createFlags = INDEX_CREATE_SKIP_BUILD | INDEX_CREATE_CONCURRENT;
+	if (indexForm->indisvisible)
+		createFlags |= INDEX_CREATE_VISIBLE;
+
 	/*
 	 * 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 874a8fc89a..7721d16fd3 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_VISIBLE, 0, true, true, NULL);
 
 	table_close(toast_rel, NoLock);
 
diff --git a/src/backend/commands/indexcmds.c b/src/backend/commands/indexcmds.c
index 33c2106c17..693f23e8d6 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->isvisible)
+		flags |= INDEX_CREATE_VISIBLE;
+	else
+		flags &= ~INDEX_CREATE_VISIBLE;
 
 	/*
 	 * 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 686f1850ca..a9d4fd7002 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -738,7 +738,8 @@ 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 ATExecSetIndexVisibility(Relation rel, bool visible);
+static bool GetIndexVisibility(Oid indexOid);
 
 /* ----------------------------------------------------------------
  *		DefineRelation
@@ -4833,6 +4834,11 @@ AlterTableGetLockLevel(List *cmds)
 				cmd_lockmode = ShareUpdateExclusiveLock;
 				break;
 
+			case AT_SetIndexVisible:
+			case AT_SetIndexInvisible:
+				cmd_lockmode = ShareUpdateExclusiveLock;
+				break;
+
 			default:			/* oops */
 				elog(ERROR, "unrecognized alter table type: %d",
 					 (int) cmd->subtype);
@@ -5268,6 +5274,13 @@ ATPrepCmd(List **wqueue, Relation rel, AlterTableCmd *cmd,
 			/* No command-specific prep needed */
 			pass = AT_PASS_MISC;
 			break;
+		case AT_SetIndexVisible:
+		case AT_SetIndexInvisible:
+			ATSimplePermissions(cmd->subtype, rel, ATT_INDEX | ATT_PARTITIONED_INDEX);
+			ATSimpleRecursion(wqueue, rel, cmd, true, lockmode, context);
+			/* No command-specific prep needed */
+			pass = AT_PASS_MISC;
+			break;
 		default:				/* oops */
 			elog(ERROR, "unrecognized alter table type: %d",
 				 (int) cmd->subtype);
@@ -5664,6 +5677,12 @@ ATExecCmd(List **wqueue, AlteredTableInfo *tab,
 		case AT_DetachPartitionFinalize:
 			address = ATExecDetachPartitionFinalize(rel, ((PartitionCmd *) cmd->def)->name);
 			break;
+		case AT_SetIndexVisible:
+			ATExecSetIndexVisibility(rel, true);
+			break;
+		case AT_SetIndexInvisible:
+			ATExecSetIndexVisibility(rel, false);
+			break;
 		default:				/* oops */
 			elog(ERROR, "unrecognized alter table type: %d",
 				 (int) cmd->subtype);
@@ -6614,6 +6633,8 @@ alter_table_type_to_string(AlterTableType cmdtype)
 			return "DROP COLUMN";
 		case AT_AddIndex:
 		case AT_ReAddIndex:
+		case AT_SetIndexVisible:
+		case AT_SetIndexInvisible:
 			return NULL;		/* not real grammar */
 		case AT_AddConstraint:
 		case AT_ReAddConstraint:
@@ -15594,6 +15615,8 @@ ATPostAlterTypeParse(Oid oldId, Oid oldRelId, Oid refRelId, char *cmd,
 			stmt->reset_default_tblspc = true;
 			/* keep the index's comment */
 			stmt->idxcomment = GetComment(oldId, RelationRelationId, 0);
+			/* preserve the index's visibility status */
+			stmt->isvisible = GetIndexVisibility(oldId);
 
 			newcmd = makeNode(AlterTableCmd);
 			newcmd->subtype = AT_ReAddIndex;
@@ -15624,6 +15647,8 @@ ATPostAlterTypeParse(Oid oldId, Oid oldRelId, Oid refRelId, char *cmd,
 					indstmt->idxcomment = GetComment(indoid,
 													 RelationRelationId, 0);
 					indstmt->reset_default_tblspc = true;
+					/* preserve the index's visibility status */
+					indstmt->isvisible = GetIndexVisibility(indoid);
 
 					cmd->subtype = AT_ReAddIndex;
 					tab->subcmds[AT_PASS_OLD_INDEX] =
@@ -21981,3 +22006,68 @@ GetAttributeStorage(Oid atttypid, const char *storagemode)
 
 	return cstorage;
 }
+
+/*
+ * ATExecSetIndexVisibility
+ * Performs a catalog update to mark an index as visible or invisible in pg_index.
+ */
+static void
+ATExecSetIndexVisibility(Relation rel, bool visible)
+{
+	Oid			indexOid = RelationGetRelid(rel);
+	Oid			heapOid;
+	Relation	pg_index;
+	Relation	heapRel;
+	HeapTuple	indexTuple;
+	Form_pg_index indexForm;
+
+	heapOid = IndexGetRelation(indexOid, false);
+	heapRel = table_open(heapOid, AccessShareLock);
+	pg_index = table_open(IndexRelationId, RowExclusiveLock);
+
+	indexTuple = SearchSysCacheCopy1(INDEXRELID, ObjectIdGetDatum(indexOid));
+	if (!HeapTupleIsValid(indexTuple))
+		elog(ERROR, "cache lookup failed for index %u", indexOid);
+
+	indexForm = (Form_pg_index) GETSTRUCT(indexTuple);
+	if (indexForm->indcheckxmin)
+		ereport(ERROR,
+				errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
+				errmsg("cannot update index visibility while indcheckxmin is true"),
+				errhint("Wait for all transactions that might see inconsistent HOT chains to complete"));
+
+	if (indexForm->indisvisible != visible)
+	{
+		indexForm->indisvisible = visible;
+		CatalogTupleUpdate(pg_index, &indexTuple->t_self, indexTuple);
+
+		CacheInvalidateRelcache(heapRel);
+		InvokeObjectPostAlterHook(IndexRelationId, indexOid, 0);
+		CommandCounterIncrement();
+	}
+
+	heap_freetuple(indexTuple);
+	table_close(pg_index, RowExclusiveLock);
+	table_close(heapRel, AccessShareLock);
+}
+
+/*
+* Get index visibility status from pg_index
+*/
+static bool
+GetIndexVisibility(Oid indexOid)
+{
+	HeapTuple	indexTuple;
+	Form_pg_index indexForm;
+	bool		isvisible;
+
+	indexTuple = SearchSysCache1(INDEXRELID, ObjectIdGetDatum(indexOid));
+	if (!HeapTupleIsValid(indexTuple))
+		elog(ERROR, "cache lookup failed for index %u", indexOid);
+
+	indexForm = (Form_pg_index) GETSTRUCT(indexTuple);
+	isvisible = indexForm->indisvisible;
+	ReleaseSysCache(indexTuple);
+
+	return isvisible;
+}
diff --git a/src/backend/optimizer/util/plancat.c b/src/backend/optimizer/util/plancat.c
index 59233b6473..6d938d3589 100644
--- a/src/backend/optimizer/util/plancat.c
+++ b/src/backend/optimizer/util/plancat.c
@@ -47,7 +47,9 @@
 #include "storage/bufmgr.h"
 #include "tcop/tcopprot.h"
 #include "utils/builtins.h"
+#include "utils/guc_hooks.h"
 #include "utils/lsyscache.h"
+#include "utils/plancache.h"
 #include "utils/partcache.h"
 #include "utils/rel.h"
 #include "utils/snapmgr.h"
@@ -55,6 +57,7 @@
 
 /* GUC parameter */
 int			constraint_exclusion = CONSTRAINT_EXCLUSION_PARTITION;
+bool		use_invisible_index = false;
 
 /* Hook for plugins to get control in get_relation_info() */
 get_relation_info_hook_type get_relation_info_hook = NULL;
@@ -256,13 +259,13 @@ get_relation_info(PlannerInfo *root, Oid relationObjectId, bool inhparent,
 			index = indexRelation->rd_index;
 
 			/*
-			 * Ignore invalid indexes, since they can't safely be used for
-			 * queries.  Note that this is OK because the data structure we
-			 * are constructing is only used by the planner --- the executor
-			 * still needs to insert into "invalid" indexes, if they're marked
-			 * indisready.
+			 * Skip invalid indexes, and invisible indexes unless
+			 * use_invisible_index is set. This is OK because the data
+			 * structure we are constructing is only used by the planner - the
+			 * executor still needs to insert into these indexes if they're
+			 * marked indisready.
 			 */
-			if (!index->indisvalid)
+			if (!index->indisvalid || (!index->indisvisible && !use_invisible_index))
 			{
 				index_close(indexRelation, NoLock);
 				continue;
@@ -2612,3 +2615,18 @@ set_baserel_partition_constraint(Relation relation, RelOptInfo *rel)
 		rel->partition_qual = partconstr;
 	}
 }
+
+/*
+ * assign_use_invisible_index
+ * GUC assign_hook for "use_invisible_index" GUC variable.
+ * Resets the plan cache when the value changes.
+ */
+void
+assign_use_invisible_index(bool newval, void *extra)
+{
+	if (use_invisible_index != newval)
+	{
+		use_invisible_index = newval;
+		ResetPlanCache();
+	}
+}
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 3c4268b271..9a1b23c645 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 <ival>	add_drop opt_asc_desc opt_nulls_order
 
 %type <node>	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 <list>	alter_table_cmds alter_type_cmds
 %type <list>    alter_identity_column_option_list
 %type <defelt>  alter_identity_column_option
@@ -491,6 +491,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
 %type <boolean> opt_unique opt_verbose opt_full
 %type <boolean> opt_freeze opt_analyze opt_default
 %type <defelt>	opt_binary copy_delimiter
+%type <boolean>  opt_index_visibility
 
 %type <boolean> copy_from opt_program
 
@@ -733,7 +734,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
 	IDENTITY_P IF_P ILIKE IMMEDIATE IMMUTABLE IMPLICIT_P IMPORT_P IN_P INCLUDE
 	INCLUDING INCREMENT INDENT INDEX INDEXES INHERIT INHERITS INITIALLY INLINE_P
 	INNER_P INOUT INPUT_P INSENSITIVE INSERT INSTEAD INT_P INTEGER
-	INTERSECT INTERVAL INTO INVOKER IS ISNULL ISOLATION
+	INTERSECT INTERVAL INTO INVISIBLE INVOKER IS ISNULL ISOLATION
 
 	JOIN JSON JSON_ARRAY JSON_ARRAYAGG JSON_EXISTS JSON_OBJECT JSON_OBJECTAGG
 	JSON_QUERY JSON_SCALAR JSON_SERIALIZE JSON_TABLE JSON_VALUE
@@ -784,7 +785,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
 	UNLISTEN UNLOGGED UNTIL UPDATE USER USING
 
 	VACUUM VALID VALIDATE VALIDATOR VALUE_P VALUES VARCHAR VARIADIC VARYING
-	VERBOSE VERSION_P VIEW VIEWS VIRTUAL VOLATILE
+	VERBOSE VERSION_P VIEW VIEWS VIRTUAL VISIBLE VOLATILE
 
 	WHEN WHERE WHITESPACE_P WINDOW WITH WITHIN WITHOUT WORK WRAPPER WRITE
 
@@ -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 <name> VISIBLE|INVISIBLE */
+		VISIBLE
+			{
+				AlterTableCmd *n = makeNode(AlterTableCmd);
+				n->subtype = AT_SetIndexVisible;
+				$$ = (Node *) n;
+			}
+		| INVISIBLE
+			{
+				AlterTableCmd *n = makeNode(AlterTableCmd);
+				n->subtype = AT_SetIndexInvisible;
+				$$ = (Node *) n;
+			}
+		;
 alter_table_cmd:
 			/* ALTER TABLE <name> ADD <coldef> */
 			ADD_P columnDef
@@ -8190,7 +8224,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_visibility
 				{
 					IndexStmt *n = makeNode(IndexStmt);
 
@@ -8205,6 +8239,7 @@ IndexStmt:	CREATE opt_unique INDEX opt_concurrently opt_single_name
 					n->options = $14;
 					n->tableSpace = $15;
 					n->whereClause = $16;
+					n->isvisible = $17;
 					n->excludeOpNames = NIL;
 					n->idxcomment = NULL;
 					n->indexOid = InvalidOid;
@@ -8222,7 +8257,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_visibility
 				{
 					IndexStmt *n = makeNode(IndexStmt);
 
@@ -8237,6 +8272,7 @@ IndexStmt:	CREATE opt_unique INDEX opt_concurrently opt_single_name
 					n->options = $17;
 					n->tableSpace = $18;
 					n->whereClause = $19;
+					n->isvisible = $20;
 					n->excludeOpNames = NIL;
 					n->idxcomment = NULL;
 					n->indexOid = InvalidOid;
@@ -8259,6 +8295,12 @@ opt_unique:
 			| /*EMPTY*/								{ $$ = false; }
 		;
 
+opt_index_visibility:
+			VISIBLE                       { $$ = true; }
+			| INVISIBLE                   { $$ = false; }
+			| /*EMPTY*/                   { $$ = true; }
+		;
+
 access_method_clause:
 			USING name								{ $$ = $2; }
 			| /*EMPTY*/								{ $$ = DEFAULT_INDEX_TYPE; }
@@ -17836,6 +17878,7 @@ unreserved_keyword:
 			| INSENSITIVE
 			| INSERT
 			| INSTEAD
+			| INVISIBLE
 			| INVOKER
 			| ISOLATION
 			| KEEP
@@ -18022,6 +18065,7 @@ unreserved_keyword:
 			| VIEW
 			| VIEWS
 			| VIRTUAL
+			| VISIBLE
 			| VOLATILE
 			| WHITESPACE_P
 			| WITHIN
@@ -18431,6 +18475,7 @@ bare_label_keyword:
 			| INT_P
 			| INTEGER
 			| INTERVAL
+			| INVISIBLE
 			| INVOKER
 			| IS
 			| ISOLATION
@@ -18679,6 +18724,7 @@ bare_label_keyword:
 			| VIEW
 			| VIEWS
 			| VIRTUAL
+			| VISIBLE
 			| VOLATILE
 			| WHEN
 			| WHITESPACE_P
diff --git a/src/backend/parser/parse_utilcmd.c b/src/backend/parser/parse_utilcmd.c
index 62015431fd..0619833c09 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->isvisible = idxrec->indisvisible;
 
 	/*
 	 * 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 visible by default */
+	index->isvisible = 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 9e90acedb9..199781c1ab 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 INVISIBLE clause if the index is invisible */
+		if (!idxrec->indisvisible)
+			appendStringInfoString(&buf, " INVISIBLE");
 	}
 
 	/* Clean up */
diff --git a/src/backend/utils/cache/relcache.c b/src/backend/utils/cache/relcache.c
index 2905ae86a2..b1d9dd769c 100644
--- a/src/backend/utils/cache/relcache.c
+++ b/src/backend/utils/cache/relcache.c
@@ -2344,6 +2344,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->indisvisible = index->indisvisible;
 
 		/* Copy xmin too, as that is needed to make sense of indcheckxmin */
 		HeapTupleHeaderSetXmin(relation->rd_indextuple->t_data,
diff --git a/src/backend/utils/misc/guc_tables.c b/src/backend/utils/misc/guc_tables.c
index f596fda568..902af28105 100644
--- a/src/backend/utils/misc/guc_tables.c
+++ b/src/backend/utils/misc/guc_tables.c
@@ -2130,6 +2130,16 @@ struct config_bool ConfigureNamesBool[] =
 		true,
 		NULL, NULL, NULL
 	},
+	{
+		{"use_invisible_index", PGC_USERSET, QUERY_TUNING_OTHER,
+			gettext_noop("Forces the planner to consider invisible indexes."),
+			NULL,
+			GUC_EXPLAIN
+		},
+		&use_invisible_index,
+		false,
+		NULL, assign_use_invisible_index, NULL
+	},
 
 	/* End-of-list marker */
 	{
diff --git a/src/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sample
index 25fe90a430..b1c15a7fd8 100644
--- a/src/backend/utils/misc/postgresql.conf.sample
+++ b/src/backend/utils/misc/postgresql.conf.sample
@@ -428,6 +428,7 @@
 #enable_group_by_reordering = on
 #enable_distinct_reordering = on
 #enable_self_join_elimination = on
+#use_invisible_index = off		# forces planner to consider invisible indexes
 
 # - Planner Cost Constants -
 
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index 25264f8c9f..aed8461f7a 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -7647,7 +7647,8 @@ getIndexes(Archive *fout, TableInfo tblinfo[], int numTables)
 				i_tablespace,
 				i_indreloptions,
 				i_indstatcols,
-				i_indstatvals;
+				i_indstatvals,
+				i_indisvisible;
 
 	/*
 	 * We want to perform just one query against pg_index.  However, we
@@ -7690,7 +7691,7 @@ getIndexes(Archive *fout, TableInfo tblinfo[], int numTables)
 
 	appendPQExpBufferStr(query,
 						 "pg_catalog.pg_get_indexdef(i.indexrelid) AS indexdef, "
-						 "i.indkey, i.indisclustered, "
+						 "i.indkey, i.indisclustered, i.indisvisible, "
 						 "c.contype, c.conname, "
 						 "c.condeferrable, c.condeferred, "
 						 "c.tableoid AS contableoid, "
@@ -7712,6 +7713,13 @@ getIndexes(Archive *fout, TableInfo tblinfo[], int numTables)
 		appendPQExpBufferStr(query,
 							 "false AS indisreplident, ");
 
+	if (fout->remoteVersion >= 180000)
+		appendPQExpBufferStr(query,
+							 "i.indisvisible, ");
+	else
+		appendPQExpBufferStr(query,
+							 "true AS indisvisible, ");
+
 	if (fout->remoteVersion >= 110000)
 		appendPQExpBufferStr(query,
 							 "inh.inhparent AS parentidx, "
@@ -7826,6 +7834,7 @@ getIndexes(Archive *fout, TableInfo tblinfo[], int numTables)
 	i_indreloptions = PQfnumber(res, "indreloptions");
 	i_indstatcols = PQfnumber(res, "indstatcols");
 	i_indstatvals = PQfnumber(res, "indstatvals");
+	i_indisvisible = PQfnumber(res, "indisvisible");
 
 	indxinfo = (IndxInfo *) pg_malloc(ntups * sizeof(IndxInfo));
 
@@ -7916,7 +7925,7 @@ getIndexes(Archive *fout, TableInfo tblinfo[], int numTables)
 								  &indAttNames, &nindAttNames))
 					pg_fatal("could not parse %s array", "indattnames");
 			}
-
+			indxinfo[j].indisvisible = (PQgetvalue(res, j, i_indisvisible)[0] == 't');
 			relstats = getRelationStatistics(fout, &indxinfo[j].dobj, relpages,
 											 PQgetvalue(res, j, i_reltuples),
 											 relallvisible, relallfrozen, indexkind,
@@ -18217,6 +18226,12 @@ dumpConstraint(Archive *fout, const ConstraintInfo *coninfo)
 			}
 
 			appendPQExpBufferStr(q, ";\n");
+
+			if (!indxinfo->indisvisible)
+			{
+				appendPQExpBuffer(q, "ALTER INDEX %s INVISIBLE;\n",
+								  fmtQualifiedDumpable(indxinfo));
+			}
 		}
 
 		/*
diff --git a/src/bin/pg_dump/pg_dump.h b/src/bin/pg_dump/pg_dump.h
index b426b5e473..55d0203df3 100644
--- a/src/bin/pg_dump/pg_dump.h
+++ b/src/bin/pg_dump/pg_dump.h
@@ -427,6 +427,8 @@ typedef struct _indxInfo
 
 	/* if there is an associated constraint object, its dumpId: */
 	DumpId		indexconstraint;
+
+	bool		indisvisible;
 } IndxInfo;
 
 typedef struct _indexAttachInfo
diff --git a/src/bin/pg_dump/t/002_pg_dump.pl b/src/bin/pg_dump/t/002_pg_dump.pl
index 6c03eca8e5..4bc579eb49 100644
--- a/src/bin/pg_dump/t/002_pg_dump.pl
+++ b/src/bin/pg_dump/t/002_pg_dump.pl
@@ -2185,6 +2185,35 @@ my %tests = (
 		like => {},
 	},
 
+	'ALTER TABLE ONLY test_table ADD CONSTRAINT ... PRIMARY KEY INVISIBLE' => {
+		create_order => 97,
+		create_sql => 'CREATE TABLE dump_test.test_table_invisible (
+					   id int PRIMARY KEY
+					  );
+					  ALTER INDEX dump_test.test_table_invisible_pkey INVISIBLE;',
+		regexp => qr/^
+			\QALTER TABLE ONLY dump_test.test_table_invisible\E\n\s+
+			\QADD CONSTRAINT test_table_invisible_pkey PRIMARY KEY (id);\E\n
+			\QALTER INDEX dump_test.test_table_invisible_pkey INVISIBLE;\E
+			/xm,
+		like => { %full_runs, %dump_test_schema_runs, section_post_data => 1 },
+		unlike => { exclude_dump_test_schema => 1, only_dump_measurement => 1 },
+	},
+
+	'CREATE INDEX ... INVISIBLE' => {
+		create_order => 98,
+		create_sql => 'CREATE TABLE dump_test.test_table_mixed_indexes (
+					   id int,
+					   value int
+					  );
+					  CREATE INDEX idx_visible ON dump_test.test_table_mixed_indexes(value);
+					  CREATE UNIQUE INDEX idx_invisible ON dump_test.test_table_mixed_indexes(value) INVISIBLE;
+					  ALTER INDEX dump_test.idx_invisible INVISIBLE;',
+		regexp => qr/CREATE UNIQUE INDEX idx_invisible ON dump_test\.test_table_mixed_indexes USING btree \(value\) INVISIBLE;/m,
+		like => { %full_runs, %dump_test_schema_runs, section_post_data => 1 },
+		unlike => { exclude_dump_test_schema => 1, only_dump_measurement => 1 },
+	},
+
 	'CREATE AGGREGATE dump_test.newavg' => {
 		create_order => 25,
 		create_sql => 'CREATE AGGREGATE dump_test.newavg (
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index 1d08268393..bdb7397814 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -2329,6 +2329,11 @@ describeOneTableDetails(const char *schemaname,
 		else
 			appendPQExpBufferStr(&buf, "false AS indnullsnotdistinct,\n");
 
+		if (pset.sversion >= 180000)
+			appendPQExpBufferStr(&buf, "i.indisvisible,\n");
+		else
+			appendPQExpBufferStr(&buf, "true AS indisvisible,\n");
+
 		appendPQExpBuffer(&buf, "  a.amname, c2.relname, "
 						  "pg_catalog.pg_get_expr(i.indpred, i.indrelid, true)\n"
 						  "FROM pg_catalog.pg_index i, pg_catalog.pg_class c, pg_catalog.pg_class c2, pg_catalog.pg_am a\n"
@@ -2354,9 +2359,10 @@ describeOneTableDetails(const char *schemaname,
 			char	   *deferred = PQgetvalue(result, 0, 5);
 			char	   *indisreplident = PQgetvalue(result, 0, 6);
 			char	   *indnullsnotdistinct = PQgetvalue(result, 0, 7);
-			char	   *indamname = PQgetvalue(result, 0, 8);
-			char	   *indtable = PQgetvalue(result, 0, 9);
-			char	   *indpred = PQgetvalue(result, 0, 10);
+			char	   *indisvisible = PQgetvalue(result, 0, 8);
+			char	   *indamname = PQgetvalue(result, 0, 9);
+			char	   *indtable = PQgetvalue(result, 0, 10);
+			char	   *indpred = PQgetvalue(result, 0, 11);
 
 			if (strcmp(indisprimary, "t") == 0)
 				printfPQExpBuffer(&tmpbuf, _("primary key, "));
@@ -2393,6 +2399,9 @@ describeOneTableDetails(const char *schemaname,
 			if (strcmp(indisreplident, "t") == 0)
 				appendPQExpBufferStr(&tmpbuf, _(", replica identity"));
 
+			if (strcmp(indisvisible, "t") != 0)
+				appendPQExpBufferStr(&tmpbuf, _(", invisible"));
+
 			printTableAddFooter(&cont, tmpbuf.data);
 
 			/*
diff --git a/src/bin/psql/tab-complete.in.c b/src/bin/psql/tab-complete.in.c
index c916b9299a..f1853a68cc 100644
--- a/src/bin/psql/tab-complete.in.c
+++ b/src/bin/psql/tab-complete.in.c
@@ -2419,7 +2419,8 @@ match_previous_words(int pattern_id,
 	else if (Matches("ALTER", "INDEX", MatchAny))
 		COMPLETE_WITH("ALTER COLUMN", "OWNER TO", "RENAME TO", "SET",
 					  "RESET", "ATTACH PARTITION",
-					  "DEPENDS ON EXTENSION", "NO DEPENDS ON EXTENSION");
+					  "DEPENDS ON EXTENSION", "NO DEPENDS ON EXTENSION",
+					  "INVISIBLE", "VISIBLE");
 	else if (Matches("ALTER", "INDEX", MatchAny, "ATTACH"))
 		COMPLETE_WITH("PARTITION");
 	else if (Matches("ALTER", "INDEX", MatchAny, "ATTACH", "PARTITION"))
@@ -3433,6 +3434,8 @@ match_previous_words(int pattern_id,
 			 !TailMatches("POLICY", MatchAny, MatchAny, MatchAny, MatchAny, MatchAny) &&
 			 !TailMatches("FOR", MatchAny, MatchAny, MatchAny))
 		COMPLETE_WITH("(");
+	else if (TailMatches("*)"))
+		COMPLETE_WITH("VISIBLE", "INVISIBLE");
 
 	/* CREATE OR REPLACE */
 	else if (Matches("CREATE", "OR"))
diff --git a/src/include/catalog/index.h b/src/include/catalog/index.h
index 4daa8bef5e..75bef5bd88 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_VISIBLE				(1 << 7)
 
 extern Oid	index_create(Relation heapRelation,
 						 const char *indexRelationName,
@@ -175,7 +176,6 @@ extern void RestoreReindexState(const void *reindexstate);
 
 extern void IndexSetParentIndex(Relation partitionIdx, Oid parentOid);
 
-
 /*
  * itemptr_encode - Encode ItemPointer as int64/int8
  *
diff --git a/src/include/catalog/pg_index.h b/src/include/catalog/pg_index.h
index 4392b9d221..ec8e5dc4bf 100644
--- a/src/include/catalog/pg_index.h
+++ b/src/include/catalog/pg_index.h
@@ -44,6 +44,8 @@ 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		indisvisible BKI_DEFAULT(t);	/* is this index visible 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 4610fc6129..92a14b82f5 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_SetIndexVisible,			/* Set INDEX visible */
+	AT_SetIndexInvisible,		/* Set INDEX invisible */
 } AlterTableType;
 
 typedef struct AlterTableCmd	/* one subcommand of an ALTER TABLE */
@@ -3470,6 +3472,8 @@ 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		isvisible;		/* true if VISIBLE (default), false if
+								 * INVISIBLE */
 } IndexStmt;
 
 /* ----------------------
diff --git a/src/include/optimizer/optimizer.h b/src/include/optimizer/optimizer.h
index 546828b54b..3b5ca55bae 100644
--- a/src/include/optimizer/optimizer.h
+++ b/src/include/optimizer/optimizer.h
@@ -212,4 +212,9 @@ extern List *pull_var_clause(Node *node, int flags);
 extern Node *flatten_join_alias_vars(PlannerInfo *root, Query *query, Node *node);
 extern Node *flatten_group_exprs(PlannerInfo *root, Query *query, Node *node);
 
+/*
+ * GUC to tell planner to consider invisible indexes.
+ */
+extern bool use_invisible_index;
+
 #endif							/* OPTIMIZER_H */
diff --git a/src/include/parser/kwlist.h b/src/include/parser/kwlist.h
index a4af3f717a..77da5117cb 100644
--- a/src/include/parser/kwlist.h
+++ b/src/include/parser/kwlist.h
@@ -229,6 +229,7 @@ PG_KEYWORD("integer", INTEGER, COL_NAME_KEYWORD, BARE_LABEL)
 PG_KEYWORD("intersect", INTERSECT, RESERVED_KEYWORD, AS_LABEL)
 PG_KEYWORD("interval", INTERVAL, COL_NAME_KEYWORD, BARE_LABEL)
 PG_KEYWORD("into", INTO, RESERVED_KEYWORD, AS_LABEL)
+PG_KEYWORD("invisible", INVISIBLE, UNRESERVED_KEYWORD, BARE_LABEL)
 PG_KEYWORD("invoker", INVOKER, UNRESERVED_KEYWORD, BARE_LABEL)
 PG_KEYWORD("is", IS, TYPE_FUNC_NAME_KEYWORD, BARE_LABEL)
 PG_KEYWORD("isnull", ISNULL, TYPE_FUNC_NAME_KEYWORD, AS_LABEL)
@@ -493,6 +494,7 @@ PG_KEYWORD("version", VERSION_P, UNRESERVED_KEYWORD, BARE_LABEL)
 PG_KEYWORD("view", VIEW, UNRESERVED_KEYWORD, BARE_LABEL)
 PG_KEYWORD("views", VIEWS, UNRESERVED_KEYWORD, BARE_LABEL)
 PG_KEYWORD("virtual", VIRTUAL, UNRESERVED_KEYWORD, BARE_LABEL)
+PG_KEYWORD("visible", VISIBLE, UNRESERVED_KEYWORD, BARE_LABEL)
 PG_KEYWORD("volatile", VOLATILE, UNRESERVED_KEYWORD, BARE_LABEL)
 PG_KEYWORD("when", WHEN, RESERVED_KEYWORD, BARE_LABEL)
 PG_KEYWORD("where", WHERE, RESERVED_KEYWORD, AS_LABEL)
diff --git a/src/include/utils/guc_hooks.h b/src/include/utils/guc_hooks.h
index 799fa7ace6..dd5128fd87 100644
--- a/src/include/utils/guc_hooks.h
+++ b/src/include/utils/guc_hooks.h
@@ -178,5 +178,6 @@ extern bool check_synchronized_standby_slots(char **newval, void **extra,
 extern void assign_synchronized_standby_slots(const char *newval, void *extra);
 extern bool check_idle_replication_slot_timeout(int *newval, void **extra,
 												GucSource source);
+extern void assign_use_invisible_index(bool newval, void *extra);
 
 #endif							/* GUC_HOOKS_H */
diff --git a/src/test/modules/test_ddl_deparse/Makefile b/src/test/modules/test_ddl_deparse/Makefile
index 3a57a95c84..747d8b5835 100644
--- a/src/test/modules/test_ddl_deparse/Makefile
+++ b/src/test/modules/test_ddl_deparse/Makefile
@@ -27,7 +27,8 @@ REGRESS = test_ddl_deparse \
 	alter_type_enum \
 	opfamily \
 	defprivs \
-	matviews
+	matviews \
+	alter_index
 
 EXTRA_INSTALL = contrib/pg_stat_statements
 
diff --git a/src/test/modules/test_ddl_deparse/expected/alter_index.out b/src/test/modules/test_ddl_deparse/expected/alter_index.out
new file mode 100644
index 0000000000..f502baf1c5
--- /dev/null
+++ b/src/test/modules/test_ddl_deparse/expected/alter_index.out
@@ -0,0 +1,29 @@
+-- Test index visibility commands
+CREATE TABLE visibility_test (id int);
+NOTICE:  DDL test: type simple, tag CREATE TABLE
+CREATE INDEX idx_vis ON visibility_test(id);
+NOTICE:  DDL test: type simple, tag CREATE INDEX
+ALTER INDEX idx_vis INVISIBLE;
+NOTICE:  DDL test: type alter table, tag ALTER INDEX
+NOTICE:    subcommand: type SET INVISIBLE desc <NULL>
+ALTER INDEX idx_vis VISIBLE;
+NOTICE:  DDL test: type alter table, tag ALTER INDEX
+NOTICE:    subcommand: type SET VISIBLE desc <NULL>
+ALTER INDEX idx_vis INVISIBLE;
+NOTICE:  DDL test: type alter table, tag ALTER INDEX
+NOTICE:    subcommand: type SET INVISIBLE desc <NULL>
+REINDEX INDEX idx_vis;
+NOTICE:  DDL test: type simple, tag REINDEX
+CREATE SCHEMA visibility_schema;
+NOTICE:  DDL test: type simple, tag CREATE SCHEMA
+CREATE TABLE visibility_schema.test2 (id int);
+NOTICE:  DDL test: type simple, tag CREATE TABLE
+CREATE INDEX idx_vis2 ON visibility_schema.test2(id);
+NOTICE:  DDL test: type simple, tag CREATE INDEX
+ALTER INDEX visibility_schema.idx_vis2 INVISIBLE;
+NOTICE:  DDL test: type alter table, tag ALTER INDEX
+NOTICE:    subcommand: type SET INVISIBLE desc <NULL>
+-- Clean up
+DROP SCHEMA visibility_schema CASCADE;
+NOTICE:  drop cascades to table visibility_schema.test2
+DROP TABLE visibility_test CASCADE;
diff --git a/src/test/modules/test_ddl_deparse/meson.build b/src/test/modules/test_ddl_deparse/meson.build
index bff65ba633..6654b5e46d 100644
--- a/src/test/modules/test_ddl_deparse/meson.build
+++ b/src/test/modules/test_ddl_deparse/meson.build
@@ -48,6 +48,7 @@ tests += {
       'opfamily',
       'defprivs',
       'matviews',
+      'alter_index',
     ],
   },
 }
diff --git a/src/test/modules/test_ddl_deparse/sql/alter_index.sql b/src/test/modules/test_ddl_deparse/sql/alter_index.sql
new file mode 100644
index 0000000000..7a34dc6692
--- /dev/null
+++ b/src/test/modules/test_ddl_deparse/sql/alter_index.sql
@@ -0,0 +1,18 @@
+-- Test index visibility commands
+CREATE TABLE visibility_test (id int);
+CREATE INDEX idx_vis ON visibility_test(id);
+
+ALTER INDEX idx_vis INVISIBLE;
+ALTER INDEX idx_vis VISIBLE;
+
+ALTER INDEX idx_vis INVISIBLE;
+REINDEX INDEX idx_vis;
+
+CREATE SCHEMA visibility_schema;
+CREATE TABLE visibility_schema.test2 (id int);
+CREATE INDEX idx_vis2 ON visibility_schema.test2(id);
+ALTER INDEX visibility_schema.idx_vis2 INVISIBLE;
+
+-- Clean up
+DROP SCHEMA visibility_schema CASCADE;
+DROP TABLE visibility_test CASCADE;
diff --git a/src/test/modules/test_ddl_deparse/test_ddl_deparse.c b/src/test/modules/test_ddl_deparse/test_ddl_deparse.c
index 193669f2bc..8d7c7e5894 100644
--- a/src/test/modules/test_ddl_deparse/test_ddl_deparse.c
+++ b/src/test/modules/test_ddl_deparse/test_ddl_deparse.c
@@ -308,6 +308,12 @@ get_altertable_subcmdinfo(PG_FUNCTION_ARGS)
 			case AT_ReAddStatistics:
 				strtype = "(re) ADD STATS";
 				break;
+			case AT_SetIndexVisible:
+				strtype = "SET VISIBLE";
+				break;
+			case AT_SetIndexInvisible:
+				strtype = "SET INVISIBLE";
+				break;
 		}
 
 		if (subcmd->recurse)
diff --git a/src/test/regress/expected/create_index.out b/src/test/regress/expected/create_index.out
index 9ade7b835e..2ba2397a12 100644
--- a/src/test/regress/expected/create_index.out
+++ b/src/test/regress/expected/create_index.out
@@ -3542,6 +3542,801 @@ ERROR:  REINDEX SCHEMA cannot run inside a transaction block
 END;
 -- concurrently
 REINDEX SCHEMA CONCURRENTLY schema_to_reindex;
+-- Test index visibility functionality
+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,
+    indisvisible boolean
+) AS $$
+BEGIN
+    RETURN QUERY EXECUTE format('
+        SELECT indexrelid::regclass, indisvalid, indisready, indislive, indisvisible
+        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 INVISIBLE;
+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 VISIBLE;
+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 INVISIBLE;
+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 VISIBLE;
+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 INVISIBLE;
+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 VISIBLE;
+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 INVISIBLE;
+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 VISIBLE;
+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 INVISIBLE;
+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 VISIBLE;
+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 INVISIBLE;
+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 VISIBLE;
+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 INVISIBLE;
+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 VISIBLE;
+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 INVISIBLE;
+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 VISIBLE;
+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 INVISIBLE;
+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 VISIBLE;
+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)
+
+-- Set all indexes to invisible to force seq scan
+ALTER INDEX idx_join INVISIBLE;
+ALTER INDEX join_test_pkey INVISIBLE;
+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 VISIBLE;
+ALTER INDEX join_test_pkey VISIBLE;
+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 INVISIBLE;
+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) INVISIBLE
+(1 row)
+
+ALTER INDEX idx_join VISIBLE;
+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 index visibility 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 INVISIBLE;  -- expect fail
+ERROR:  cannot update index visibility while indcheckxmin is true
+HINT:  Wait for all transactions that might see inconsistent HOT chains to complete
+ROLLBACK;
+-- Test prepared statements with index visibility
+CREATE TABLE prep_idx_test (id int, data text);
+CREATE INDEX prep_idx1 ON prep_idx_test(data);
+INSERT INTO prep_idx_test SELECT g, 'data ' || g FROM generate_series(1,100) g;
+-- Test with visible index
+PREPARE idx_plan AS SELECT * FROM prep_idx_test WHERE data = $1;
+EXPLAIN (COSTS OFF) EXECUTE idx_plan('data 1');
+                 QUERY PLAN                  
+---------------------------------------------
+ Bitmap Heap Scan on prep_idx_test
+   Recheck Cond: (data = 'data 1'::text)
+   ->  Bitmap Index Scan on prep_idx1
+         Index Cond: (data = 'data 1'::text)
+(4 rows)
+
+EXECUTE idx_plan('data 1');
+ id |  data  
+----+--------
+  1 | data 1
+(1 row)
+
+-- Test with invisible index
+ALTER INDEX prep_idx1 INVISIBLE;
+EXPLAIN (COSTS OFF) EXECUTE idx_plan('data 1');
+            QUERY PLAN             
+-----------------------------------
+ Seq Scan on prep_idx_test
+   Filter: (data = 'data 1'::text)
+(2 rows)
+
+EXECUTE idx_plan('data 1');
+ id |  data  
+----+--------
+  1 | data 1
+(1 row)
+
+-- Test with use_invisible_index
+SET use_invisible_index TO on;
+EXPLAIN (COSTS OFF) EXECUTE idx_plan('data 1');
+                 QUERY PLAN                  
+---------------------------------------------
+ Bitmap Heap Scan on prep_idx_test
+   Recheck Cond: (data = 'data 1'::text)
+   ->  Bitmap Index Scan on prep_idx1
+         Index Cond: (data = 'data 1'::text)
+(4 rows)
+
+EXECUTE idx_plan('data 1');
+ id |  data  
+----+--------
+  1 | data 1
+(1 row)
+
+SET use_invisible_index TO off;
+-- Assert plan cache is reset and index is not used
+EXPLAIN (COSTS OFF) EXECUTE idx_plan('data 1');
+            QUERY PLAN             
+-----------------------------------
+ Seq Scan on prep_idx_test
+   Filter: (data = 'data 1'::text)
+(2 rows)
+
+-- Make index visible and test again
+ALTER INDEX prep_idx1 VISIBLE;
+EXPLAIN (COSTS OFF) EXECUTE idx_plan('data 1');
+                 QUERY PLAN                  
+---------------------------------------------
+ Bitmap Heap Scan on prep_idx_test
+   Recheck Cond: (data = 'data 1'::text)
+   ->  Bitmap Index Scan on prep_idx1
+         Index Cond: (data = 'data 1'::text)
+(4 rows)
+
+EXECUTE idx_plan('data 1');
+ id |  data  
+----+--------
+  1 | data 1
+(1 row)
+
+-- Test index visibility with partitioned tables
+CREATE TABLE part_tbl(id int, data text) PARTITION BY RANGE(id);
+CREATE TABLE part1 PARTITION OF part_tbl FOR VALUES FROM (1) TO (100);
+CREATE TABLE part2 PARTITION OF part_tbl FOR VALUES FROM (100) TO (200);
+INSERT INTO part_tbl
+SELECT g, 'data ' || g
+FROM generate_series(1, 199) g;
+CREATE INDEX idx_part_tbl ON part_tbl(data);
+SELECT c.relname, i.indisvisible
+FROM pg_index i
+JOIN pg_class c ON i.indexrelid = c.oid
+WHERE c.relname LIKE 'idx_part_tbl%'
+ORDER BY c.relname;
+   relname    | indisvisible 
+--------------+--------------
+ idx_part_tbl | t
+(1 row)
+
+-- Force use of indexes to avoid flaky test results
+SET enable_seqscan = off;
+EXPLAIN (COSTS OFF)
+SELECT * FROM part_tbl WHERE data = 'data 50';
+                        QUERY PLAN                         
+-----------------------------------------------------------
+ Append
+   ->  Index Scan using part1_data_idx on part1 part_tbl_1
+         Index Cond: (data = 'data 50'::text)
+   ->  Index Scan using part2_data_idx on part2 part_tbl_2
+         Index Cond: (data = 'data 50'::text)
+(5 rows)
+
+ALTER INDEX idx_part_tbl INVISIBLE;
+SELECT c.relname, i.indisvisible
+FROM pg_index i
+JOIN pg_class c ON i.indexrelid = c.oid
+WHERE c.relname LIKE 'idx_part_tbl%'
+ORDER BY c.relname;
+   relname    | indisvisible 
+--------------+--------------
+ idx_part_tbl | f
+(1 row)
+
+-- Check query plan after setting invisible (should use seq scan)
+EXPLAIN (COSTS OFF)
+SELECT * FROM part_tbl WHERE data = 'data 50';
+                QUERY PLAN                
+------------------------------------------
+ Append
+   ->  Seq Scan on part1 part_tbl_1
+         Disabled: true
+         Filter: (data = 'data 50'::text)
+   ->  Seq Scan on part2 part_tbl_2
+         Disabled: true
+         Filter: (data = 'data 50'::text)
+(7 rows)
+
+SET use_invisible_index TO on;
+EXPLAIN (COSTS OFF)
+SELECT * FROM part_tbl WHERE data = 'data 50';
+                        QUERY PLAN                         
+-----------------------------------------------------------
+ Append
+   ->  Index Scan using part1_data_idx on part1 part_tbl_1
+         Index Cond: (data = 'data 50'::text)
+   ->  Index Scan using part2_data_idx on part2 part_tbl_2
+         Index Cond: (data = 'data 50'::text)
+(5 rows)
+
+SET use_invisible_index TO off;
+EXPLAIN (COSTS OFF)
+SELECT * FROM part_tbl WHERE data = 'data 50';
+                QUERY PLAN                
+------------------------------------------
+ Append
+   ->  Seq Scan on part1 part_tbl_1
+         Disabled: true
+         Filter: (data = 'data 50'::text)
+   ->  Seq Scan on part2 part_tbl_2
+         Disabled: true
+         Filter: (data = 'data 50'::text)
+(7 rows)
+
+ALTER INDEX idx_part_tbl VISIBLE;
+SELECT c.relname, i.indisvisible
+FROM pg_index i
+JOIN pg_class c ON i.indexrelid = c.oid
+WHERE c.relname LIKE 'idx_part_tbl%'
+ORDER BY c.relname;
+   relname    | indisvisible 
+--------------+--------------
+ idx_part_tbl | t
+(1 row)
+
+EXPLAIN (COSTS OFF)
+SELECT * FROM part_tbl WHERE data = 'data 50';
+                        QUERY PLAN                         
+-----------------------------------------------------------
+ Append
+   ->  Index Scan using part1_data_idx on part1 part_tbl_1
+         Index Cond: (data = 'data 50'::text)
+   ->  Index Scan using part2_data_idx on part2 part_tbl_2
+         Index Cond: (data = 'data 50'::text)
+(5 rows)
+
+SET enable_seqscan = on;
+-- Test REINDEX maintains visibility setting
+ALTER INDEX idx_part_tbl INVISIBLE;
+REINDEX INDEX idx_part_tbl;
+SELECT c.relname, i.indisvisible
+FROM pg_index i
+JOIN pg_class c ON i.indexrelid = c.oid
+WHERE c.relname LIKE 'idx_part_tbl%'
+ORDER BY c.relname;
+   relname    | indisvisible 
+--------------+--------------
+ idx_part_tbl | f
+(1 row)
+
+-- Test that index visibility is preserved after ALTER TABLE
+CREATE TABLE vis_test(id INT PRIMARY KEY, data text);
+\d vis_test
+        Table "schema_to_reindex.vis_test"
+ Column |  Type   | Collation | Nullable | Default 
+--------+---------+-----------+----------+---------
+ id     | integer |           | not null | 
+ data   | text    |           |          | 
+Indexes:
+    "vis_test_pkey" PRIMARY KEY, btree (id)
+
+ALTER INDEX vis_test_pkey INVISIBLE;
+\d vis_test
+        Table "schema_to_reindex.vis_test"
+ Column |  Type   | Collation | Nullable | Default 
+--------+---------+-----------+----------+---------
+ id     | integer |           | not null | 
+ data   | text    |           |          | 
+Indexes:
+    "vis_test_pkey" PRIMARY KEY, btree (id) INVISIBLE
+
+ALTER TABLE vis_test ALTER COLUMN id SET DATA TYPE bigint;
+\d vis_test
+        Table "schema_to_reindex.vis_test"
+ Column |  Type  | Collation | Nullable | Default 
+--------+--------+-----------+----------+---------
+ id     | bigint |           | not null | 
+ data   | text   |           |          | 
+Indexes:
+    "vis_test_pkey" PRIMARY KEY, btree (id) INVISIBLE
+
+ALTER TABLE vis_test ALTER COLUMN id SET DATA TYPE int;
+\d vis_test
+        Table "schema_to_reindex.vis_test"
+ Column |  Type   | Collation | Nullable | Default 
+--------+---------+-----------+----------+---------
+ id     | integer |           | not null | 
+ data   | text    |           |          | 
+Indexes:
+    "vis_test_pkey" PRIMARY KEY, btree (id) INVISIBLE
+
+ALTER INDEX vis_test_pkey VISIBLE;
+\d vis_test
+        Table "schema_to_reindex.vis_test"
+ Column |  Type   | Collation | Nullable | Default 
+--------+---------+-----------+----------+---------
+ id     | integer |           | not null | 
+ data   | text    |           |          | 
+Indexes:
+    "vis_test_pkey" PRIMARY KEY, btree (id)
+
+-- Test CREATE INDEX with INVISIBLE option
+CREATE TABLE invis_test(id int, data text);
+INSERT INTO invis_test SELECT g, 'data ' || g FROM generate_series(1,100) g;
+CREATE INDEX idx_invis1 ON invis_test(data) INVISIBLE;
+\d invis_test
+       Table "schema_to_reindex.invis_test"
+ Column |  Type   | Collation | Nullable | Default 
+--------+---------+-----------+----------+---------
+ id     | integer |           |          | 
+ data   | text    |           |          | 
+Indexes:
+    "idx_invis1" btree (data) INVISIBLE
+
+\d idx_invis1
+Index "schema_to_reindex.idx_invis1"
+ Column | Type | Key? | Definition 
+--------+------+------+------------
+ data   | text | yes  | data
+btree, for table "schema_to_reindex.invis_test", invisible
+
+ALTER INDEX idx_invis1 VISIBLE;
+\d invis_test
+       Table "schema_to_reindex.invis_test"
+ Column |  Type   | Collation | Nullable | Default 
+--------+---------+-----------+----------+---------
+ id     | integer |           |          | 
+ data   | text    |           |          | 
+Indexes:
+    "idx_invis1" btree (data)
+
+\d idx_invis1
+Index "schema_to_reindex.idx_invis1"
+ Column | Type | Key? | Definition 
+--------+------+------+------------
+ data   | text | yes  | data
+btree, for table "schema_to_reindex.invis_test"
+
+-- 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;
+DEALLOCATE idx_plan;
+DROP TABLE prep_idx_test;
+DROP TABLE part_tbl CASCADE;
+DROP TABLE vis_test;
+DROP TABLE invis_test;
 -- 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 e21ff42651..b4bea3f9d9 100644
--- a/src/test/regress/sql/create_index.sql
+++ b/src/test/regress/sql/create_index.sql
@@ -1496,6 +1496,327 @@ END;
 -- concurrently
 REINDEX SCHEMA CONCURRENTLY schema_to_reindex;
 
+-- Test index visibility functionality
+
+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,
+    indisvisible boolean
+) AS $$
+BEGIN
+    RETURN QUERY EXECUTE format('
+        SELECT indexrelid::regclass, indisvalid, indisready, indislive, indisvisible
+        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 INVISIBLE;
+SELECT show_index_status('idx_single');
+EXPLAIN (COSTS OFF) SELECT * FROM index_test WHERE data = 'data 500';
+ALTER INDEX idx_single VISIBLE;
+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 INVISIBLE;
+SELECT show_index_status('idx_multi');
+EXPLAIN (COSTS OFF) SELECT * FROM index_test WHERE num = 50 AND data > 'data 500';
+ALTER INDEX idx_multi VISIBLE;
+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 INVISIBLE;
+SELECT show_index_status('idx_partial');
+EXPLAIN (COSTS OFF) SELECT * FROM index_test WHERE num = 25;
+ALTER INDEX idx_partial VISIBLE;
+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 INVISIBLE;
+SELECT show_index_status('idx_expression');
+EXPLAIN (COSTS OFF) SELECT * FROM index_test WHERE lower(data) = 'data 500';
+ALTER INDEX idx_expression VISIBLE;
+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 INVISIBLE;
+SELECT show_index_status('idx_gin');
+EXPLAIN (COSTS OFF) SELECT * FROM index_test WHERE vector @> ARRAY[500];
+ALTER INDEX idx_gin VISIBLE;
+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 INVISIBLE;
+SELECT show_index_status('idx_gist');
+EXPLAIN (COSTS OFF) SELECT * FROM index_test WHERE range && int4range(100, 110);
+ALTER INDEX idx_gist VISIBLE;
+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 INVISIBLE;
+SELECT show_index_status('idx_covering');
+EXPLAIN (COSTS OFF) SELECT num, data FROM index_test WHERE num = 50;
+ALTER INDEX idx_covering VISIBLE;
+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 INVISIBLE;
+SELECT show_index_status('idx_unique');
+EXPLAIN (COSTS OFF) SELECT * FROM index_test WHERE id = 500 AND data = 'data 500';
+ALTER INDEX idx_unique VISIBLE;
+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 INVISIBLE;
+SELECT show_index_status('idx_func');
+EXPLAIN (COSTS OFF) SELECT * FROM index_test WHERE get_data_length(data) = 10;
+ALTER INDEX idx_func VISIBLE;
+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;
+
+-- Set all indexes to invisible to force seq scan
+ALTER INDEX idx_join INVISIBLE;
+ALTER INDEX join_test_pkey INVISIBLE;
+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 VISIBLE;
+ALTER INDEX join_test_pkey VISIBLE;
+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 INVISIBLE;
+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 VISIBLE;
+SELECT pg_get_indexdef('idx_join'::regclass);
+
+-- Test index visibility 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 INVISIBLE;  -- expect fail
+ROLLBACK;
+
+-- Test prepared statements with index visibility
+CREATE TABLE prep_idx_test (id int, data text);
+CREATE INDEX prep_idx1 ON prep_idx_test(data);
+INSERT INTO prep_idx_test SELECT g, 'data ' || g FROM generate_series(1,100) g;
+
+-- Test with visible index
+PREPARE idx_plan AS SELECT * FROM prep_idx_test WHERE data = $1;
+EXPLAIN (COSTS OFF) EXECUTE idx_plan('data 1');
+EXECUTE idx_plan('data 1');
+
+-- Test with invisible index
+ALTER INDEX prep_idx1 INVISIBLE;
+EXPLAIN (COSTS OFF) EXECUTE idx_plan('data 1');
+EXECUTE idx_plan('data 1');
+
+-- Test with use_invisible_index
+SET use_invisible_index TO on;
+EXPLAIN (COSTS OFF) EXECUTE idx_plan('data 1');
+EXECUTE idx_plan('data 1');
+SET use_invisible_index TO off;
+-- Assert plan cache is reset and index is not used
+EXPLAIN (COSTS OFF) EXECUTE idx_plan('data 1');
+
+-- Make index visible and test again
+ALTER INDEX prep_idx1 VISIBLE;
+EXPLAIN (COSTS OFF) EXECUTE idx_plan('data 1');
+EXECUTE idx_plan('data 1');
+
+-- Test index visibility with partitioned tables
+CREATE TABLE part_tbl(id int, data text) PARTITION BY RANGE(id);
+CREATE TABLE part1 PARTITION OF part_tbl FOR VALUES FROM (1) TO (100);
+CREATE TABLE part2 PARTITION OF part_tbl FOR VALUES FROM (100) TO (200);
+
+INSERT INTO part_tbl
+SELECT g, 'data ' || g
+FROM generate_series(1, 199) g;
+
+CREATE INDEX idx_part_tbl ON part_tbl(data);
+
+SELECT c.relname, i.indisvisible
+FROM pg_index i
+JOIN pg_class c ON i.indexrelid = c.oid
+WHERE c.relname LIKE 'idx_part_tbl%'
+ORDER BY c.relname;
+
+-- Force use of indexes to avoid flaky test results
+SET enable_seqscan = off;
+
+EXPLAIN (COSTS OFF)
+SELECT * FROM part_tbl WHERE data = 'data 50';
+
+ALTER INDEX idx_part_tbl INVISIBLE;
+
+SELECT c.relname, i.indisvisible
+FROM pg_index i
+JOIN pg_class c ON i.indexrelid = c.oid
+WHERE c.relname LIKE 'idx_part_tbl%'
+ORDER BY c.relname;
+
+-- Check query plan after setting invisible (should use seq scan)
+EXPLAIN (COSTS OFF)
+SELECT * FROM part_tbl WHERE data = 'data 50';
+
+SET use_invisible_index TO on;
+
+EXPLAIN (COSTS OFF)
+SELECT * FROM part_tbl WHERE data = 'data 50';
+
+SET use_invisible_index TO off;
+
+EXPLAIN (COSTS OFF)
+SELECT * FROM part_tbl WHERE data = 'data 50';
+
+ALTER INDEX idx_part_tbl VISIBLE;
+
+SELECT c.relname, i.indisvisible
+FROM pg_index i
+JOIN pg_class c ON i.indexrelid = c.oid
+WHERE c.relname LIKE 'idx_part_tbl%'
+ORDER BY c.relname;
+
+EXPLAIN (COSTS OFF)
+SELECT * FROM part_tbl WHERE data = 'data 50';
+
+SET enable_seqscan = on;
+
+-- Test REINDEX maintains visibility setting
+ALTER INDEX idx_part_tbl INVISIBLE;
+REINDEX INDEX idx_part_tbl;
+
+SELECT c.relname, i.indisvisible
+FROM pg_index i
+JOIN pg_class c ON i.indexrelid = c.oid
+WHERE c.relname LIKE 'idx_part_tbl%'
+ORDER BY c.relname;
+
+-- Test that index visibility is preserved after ALTER TABLE
+CREATE TABLE vis_test(id INT PRIMARY KEY, data text);
+\d vis_test
+
+ALTER INDEX vis_test_pkey INVISIBLE;
+\d vis_test
+
+ALTER TABLE vis_test ALTER COLUMN id SET DATA TYPE bigint;
+\d vis_test
+
+ALTER TABLE vis_test ALTER COLUMN id SET DATA TYPE int;
+\d vis_test
+
+ALTER INDEX vis_test_pkey VISIBLE;
+\d vis_test
+
+-- Test CREATE INDEX with INVISIBLE option
+CREATE TABLE invis_test(id int, data text);
+INSERT INTO invis_test SELECT g, 'data ' || g FROM generate_series(1,100) g;
+
+CREATE INDEX idx_invis1 ON invis_test(data) INVISIBLE;
+\d invis_test
+\d idx_invis1
+
+ALTER INDEX idx_invis1 VISIBLE;
+\d invis_test
+\d idx_invis1
+
+-- 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;
+DEALLOCATE idx_plan;
+DROP TABLE prep_idx_test;
+DROP TABLE part_tbl CASCADE;
+DROP TABLE vis_test;
+DROP TABLE invis_test;
+
 -- Failure for unauthorized user
 CREATE ROLE regress_reindexuser NOLOGIN;
 SET SESSION ROLE regress_reindexuser;
-- 
2.39.5 (Apple Git-154)

Re: [PATCH] Re: Proposal to Enable/Disable Index using ALTER INDEX

Reply via email to