Check for Invalid Indexes in YSQL

If an online CREATE INDEX command fails, an invalid index may be left behind. These indexes are not usable in queries, so they should be dropped.

Example:

				
					yugabyte=# CREATE TABLE test(c1 INT PRIMARY KEY, c2 INT, c3 INT, c4 INT);
CREATE TABLE

yugabyte=# INSERT INTO test SELECT g, g, g, 1 FROM generate_series(1, 1000000) g;
INSERT 0 1000000
				
			

Now I will create a few indexes, making sure that a few are invalid.

				
					yugabyte=# CREATE UNIQUE INDEX test_c2_i ON test(c2);
CREATE INDEX

yugabyte=# CREATE UNIQUE INDEX test_c3_i ON test(c3);
^CCancel request sent
ERROR:  canceling statement due to user request

yugabyte=# CREATE UNIQUE INDEX test_c4_i ON test(c4);
ERROR:  ERROR:  duplicate key value violates unique constraint "test_c4_i"
				
			
We can use the meta-command \d to describe the table, which will show the table’s indexes, including their status, if invalid.
				
					yugabyte=# \d test
                Table "public.test"
 Column |  Type   | Collation | Nullable | Default
--------+---------+-----------+----------+---------
 c1     | integer |           | not null |
 c2     | integer |           |          |
 c3     | integer |           |          |
 c4     | integer |           |          |
Indexes:
    "test_pkey" PRIMARY KEY, lsm (c1 HASH)
    "test_c2_i" UNIQUE, lsm (c2 HASH)
    "test_c3_i" UNIQUE, lsm (c3 HASH) INVALID
    "test_c4_i" UNIQUE, lsm (c4 HASH) INVALID
				
			

Notice that the test_c3_i and test_c4_i indexes are listed as invalid.

You can also query the pg_index and pg_class system tables directly to find all indexes that are invalid.

				
					yugabyte=# CREATE TABLE another_test AS SELECT 1 c1 UNION ALL SELECT 1;
SELECT 2

yugabyte=# CREATE UNIQUE INDEX another_test_i ON another_test(c1);
ERROR:  ERROR:  duplicate key value violates unique constraint "another_test_i"

yugabyte=# SELECT relnamespace::regnamespace AS schema_name,
yugabyte-#        indrelid::regclass AS table_name,
yugabyte-#        relname AS index_name
yugabyte-#   FROM pg_index i
yugabyte-#   JOIN pg_class c ON c.oid = i.indexrelid
yugabyte-#  WHERE NOT indisvalid
yugabyte-#  ORDER BY 1, 2, 3;
 schema_name |  table_name  |   index_name
-------------+--------------+----------------
 public      | test         | test_c3_i
 public      | test         | test_c4_i
 public      | another_test | another_test_i
(3 rows)
				
			

Here’s a DDL statement to create a view named invalid_indexes_vw which you can query periodically to find invalid indexes and an easy way to get rid of ’em!

				
					CREATE OR REPLACE VIEW invalid_indexes_vw AS
SELECT relnamespace::regnamespace AS schema_name,
       indrelid::regclass AS table_name,
       relname AS index_name,
       'DROP INDEX ' || relnamespace::regnamespace || '.' || relname || ';' AS drop_ddl 
  FROM pg_index i
  JOIN pg_class c ON c.oid = i.indexrelid
 WHERE NOT indisvalid
 ORDER BY 1, 2, 3;
				
			

Example:

				
					yugabyte=# CREATE OR REPLACE VIEW invalid_indexes_vw AS
yugabyte-# SELECT relnamespace::regnamespace AS schema_name,
yugabyte-#        indrelid::regclass AS table_name,
yugabyte-#        relname AS index_name,
yugabyte-#        'DROP INDEX ' || relnamespace::regnamespace || '.' || relname || ';' AS drop_ddl
yugabyte-#   FROM pg_index i
yugabyte-#   JOIN pg_class c ON c.oid = i.indexrelid
yugabyte-#  WHERE NOT indisvalid
yugabyte-#  ORDER BY 1, 2, 3;
CREATE VIEW

yugabyte=# SELECT * FROM invalid_indexes_vw;
 schema_name |  table_name  |   index_name   |             drop_ddl
-------------+--------------+----------------+-----------------------------------
 public      | test         | test_c3_i      | DROP INDEX public.test_c3_i;
 public      | test         | test_c4_i      | DROP INDEX public.test_c4_i;
 public      | another_test | another_test_i | DROP INDEX public.another_test_i;
(3 rows)

yugabyte=# SELECT drop_ddl FROM invalid_indexes_vw;
             drop_ddl
-----------------------------------
 DROP INDEX public.test_c3_i;
 DROP INDEX public.test_c4_i;
 DROP INDEX public.another_test_i;
(3 rows)

yugabyte=# \gexec
DROP INDEX
DROP INDEX
DROP INDEX

yugabyte=# SELECT * FROM invalid_indexes_vw;
 schema_name | table_name | index_name | drop_ddl
-------------+------------+------------+----------
(0 rows)
				
			

Have Fun!

The lovely Yellowstone River