Sometimes you want to temporarily disable an index … for example, to test the optimizer’s behavior without it, benchmark a new candidate index, or confirm whether a specific one is being used.
YugabyteDB’s YSQL layer doesn’t offer ALTER INDEX DISABLE, but you can manually toggle a flag in the system catalog to achieve the same effect, safely, if you know what you’re doing.
The Raw SQL: Disable and Re-enable an Index
If you just want to try this once or understand what’s happening under the hood, here’s the basic approach.
Say we have this table which has two secondary indexes:
yugabyte=# \d test
Table "public.test"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
c1 | integer | | not null |
v1 | text | | |
v2 | text | | |
Indexes:
"test_pkey" PRIMARY KEY, lsm (c1 HASH)
"test_v1_idx" lsm (v1 HASH) INCLUDE (c1, v2)
"test_v2_idx" lsm (v2 HASH) INCLUDE (c1, v2)
A simple query may use the test_v1_idx index:
yugabyte=# EXPLAIN SELECT * FROM test WHERE v1 = 'A' AND v2 = 'A';
QUERY PLAN
-------------------------------------------------------------------------------
Index Only Scan using test_v1_idx on test (cost=0.00..5.18 rows=10 width=68)
Index Cond: (v1 = 'A'::text)
Storage Filter: (v2 = 'A'::text)
(3 rows)
Suppose we want to test the performance of the test_v2_idx index, or simply verify whether it can be used by this query, without dropping the existing test_v1_idx index. In that case, we can just disable the test_v1_idx index.
To disable the test_v1_idx, we can use this SQL:
-- Execute as a superuser
BEGIN;
SET yb_non_ddl_txn_for_sys_tables_allowed = true;
UPDATE pg_index
SET indisvalid = false
WHERE indexrelid = 'test_v1_idx'::regclass;
-- Make the change visible cluster-wide
UPDATE pg_yb_catalog_version
SET current_version = current_version + 1
WHERE db_oid = (SELECT oid FROM pg_database WHERE datname = current_database());
RESET yb_non_ddl_txn_for_sys_tables_allowed;
COMMIT;
After executing the SQL, describing the table now shows that the test_v1_idx index is disabled (marked as INVALID):
yugabyte=# \d test;
Table "public.test"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
c1 | integer | | not null |
v1 | text | | |
v2 | text | | |
Indexes:
"test_pkey" PRIMARY KEY, lsm (c1 HASH)
"test_v1_idx" lsm (v1 HASH) INCLUDE (c1, v2) INVALID
"test_v2_idx" lsm (v2 HASH) INCLUDE (c1, v2)
Once disabled, the index remains on disk but is ignored by the optimizer. Reviewing the EXPLAIN PLAN for the query now shows that the test_v2_idx index is chosen:
yugabyte=# EXPLAIN SELECT * FROM test WHERE v1 = 'A' AND v2 = 'A';
QUERY PLAN
--------------------------------------------------------------------------
Index Scan using test_v2_idx on test (cost=0.00..5.28 rows=10 width=68)
Index Cond: (v2 = 'A'::text)
Storage Filter: (v1 = 'A'::text)
(3 rows)
We can re-enable the test_v1_idx index using:
-- Execute as a superuser
BEGIN;
SET yb_non_ddl_txn_for_sys_tables_allowed = true;
UPDATE pg_index
SET indisvalid = true
WHERE indexrelid = 'test_v1_idx'::regclass;
-- Make the change visible cluster-wide
UPDATE pg_yb_catalog_version
SET current_version = current_version + 1
WHERE db_oid = (SELECT oid FROM pg_database WHERE datname = current_database());
RESET yb_non_ddl_txn_for_sys_tables_allowed;
COMMIT;
After running the SQL, the index test_v1_idx is no longer INVALID:
yugabyte=# \d test;
Table "public.test"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
c1 | integer | | not null |
v1 | text | | |
v2 | text | | |
Indexes:
"test_pkey" PRIMARY KEY, lsm (c1 HASH)
"test_v1_idx" lsm (v1 HASH) INCLUDE (c1, v2)
"test_v2_idx" lsm (v2 HASH) INCLUDE (c1, v2)
Encapsulating It in a Function
Typing those transaction blocks each time you want to disable/re-enable an index isn’t ideal.
Here’s a single, schema-safe function that accepts:
●
p_index_qualified: schema-qualified name likesales.test_v1_idx●
p_action:'enable'or'disable'
-- One function to enable/disable a specific index (schema-qualified)
-- Execute as a superuser
CREATE OR REPLACE FUNCTION yb_set_index_state(p_index_qualified text, p_action text)
RETURNS void
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
DECLARE
v_oid oid;
v_enable boolean;
v_db_oid oid;
v_schema text;
v_index text;
v_is_index boolean;
v_is_con boolean;
BEGIN
-- Require schema qualification to avoid search_path surprises
IF position('.' IN p_index_qualified) = 0 THEN
RAISE EXCEPTION 'Please provide a schema-qualified index name like schema.index_name (got: "%")',
p_index_qualified;
END IF;
-- Validate action
IF lower(p_action) NOT IN ('enable','disable') THEN
RAISE EXCEPTION 'Invalid action "%". Use "enable" or "disable".', p_action;
END IF;
v_enable := (lower(p_action) = 'enable');
-- Resolve to an OID (works with schema-qualified text)
v_oid := to_regclass(p_index_qualified);
IF v_oid IS NULL THEN
RAISE EXCEPTION 'Index "%" not found in this database.', p_index_qualified;
END IF;
-- Confirm it is an index and capture schema/name
SELECT (c.relkind = 'i'), n.nspname, c.relname
INTO v_is_index, v_schema, v_index
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.oid = v_oid;
IF NOT v_is_index THEN
RAISE EXCEPTION '"%" is not an index (%.%)', p_index_qualified, v_schema, v_index;
END IF;
-- Refuse to modify constraint-backed indexes (PK/UNIQUE)
SELECT EXISTS(SELECT 1 FROM pg_constraint WHERE conindid = v_oid)
INTO v_is_con;
IF v_is_con THEN
RAISE EXCEPTION 'Refusing to % constraint-backed index %.%.',
lower(p_action), v_schema, v_index;
END IF;
-- Allow catalog writes in a non-DDL txn (scoped to this transaction)
PERFORM set_config('yb_non_ddl_txn_for_sys_tables_allowed', 'true', true);
-- Flip validity flag
UPDATE pg_index
SET indisvalid = v_enable
WHERE indexrelid = v_oid;
-- Bump YB catalog version so change is visible cluster-wide
SELECT oid INTO v_db_oid
FROM pg_database
WHERE datname = current_database();
UPDATE pg_yb_catalog_version
SET current_version = current_version + 1
WHERE db_oid = v_db_oid;
-- Restore setting (also auto-resets at xact end)
PERFORM set_config('yb_non_ddl_txn_for_sys_tables_allowed', 'false', true);
RAISE NOTICE 'Index %.% set to % (indisvalid=%).',
v_schema, v_index, lower(p_action), v_enable;
END;
$$;
Recommended hardening: Restrict who can call it!
-- Execute all as a superuser
REVOKE ALL ON FUNCTION yb_set_index_state(text, text) FROM PUBLIC;
-- Example: delegate to a controlled ops role
-- GRANT EXECUTE ON FUNCTION yb_set_index_state(text, text) TO yb_ops;
Function usage:
-- Disable a specific index (schema-qualified)
SELECT yb_set_index_state('public.test_v1_idx', 'disable');
-- Re-enable it later
SELECT yb_set_index_state('public.test_v1_idx', 'enable');
-- Check its state
SELECT indexrelid::regclass AS index_name, indisvalid
FROM pg_index
WHERE indexrelid = 'public.test_v1_idx'::regclass;
Example:
yugabyte=# SELECT yb_set_index_state('public.test_v1_idx', 'disable');
NOTICE: Index public.test_v1_idx set to disable (indisvalid=f).
yb_set_index_state
--------------------
(1 row)
yugabyte=# SELECT indexrelid::regclass AS index_name, indisvalid
yugabyte-# FROM pg_index
yugabyte-# WHERE indexrelid = 'public.test_v1_idx'::regclass;
index_name | indisvalid
-------------+------------
test_v1_idx | f
(1 row)
yugabyte=# SELECT yb_set_index_state('public.test_v1_idx', 'enable');
NOTICE: Index public.test_v1_idx set to enable (indisvalid=t).
yb_set_index_state
--------------------
(1 row)
yugabyte=# SELECT indexrelid::regclass AS index_name, indisvalid
yugabyte-# FROM pg_index
yugabyte-# WHERE indexrelid = 'public.test_v1_idx'::regclass;
index_name | indisvalid
-------------+------------
test_v1_idx | t
(1 row)
⚠️ Warnings & Best Practices (Read This)
● Don’t touch constraint indexes: Disabling indexes that back PRIMARY KEY / UNIQUE constraints can break integrity or lead to undefined behavior. The function rejects these by design.
● Forgetting to re-enable hurts: A disabled index still consumes disk and is maintained by writes, giving you the cost without the benefit. Track what you disable and re-enable promptly (or drop it if truly unneeded).
● Advanced operation: You’re editing system catalogs (
pg_index,pg_yb_catalog_version). Use in lower environments first; change-control it in prod.● Version awareness: Internal catalogs evolve. Validate on your target YugabyteDB version before standardizing.
Summary:
Why would you disable an index?
- ● Plan experiments: Compare performance or plan shapes with and without a specific index.
- ● Index migrations: Validate that a new index is chosen as expected before retiring the old one.
- ● Debugging: Verify whether a slow or surprising plan actually depends on a particular index.
- ● Controlled benchmarks: Force sequential access patterns for cost model analysis.
Used carefully, this method makes A/B testing and index migrations repeatable, safe, and clear, without hand-editing the catalog every time.
Have Fun!
