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!
