The use of secondary indexes can enhance database performance by enabling the database server to find rows faster.
If you create too many indexes on a table, you may start see a performance degradation on INSERTs into the table.
It’s important to periodically check to see if your indexes are actually being used for your work load.
We can query the pg_stat_user_indexes system table to determine if an index has been scanned.
Example:
yugabyte=> CREATE TABLE test (c1 INT, c2 VARCHAR, c3 VARCHAR);
CREATE TABLE
yugabyte=> INSERT INTO test SELECT 1, 'A', 'X';
INSERT 0 1
yugabyte=> INSERT INTO test SELECT 2, 'B', 'Y';
INSERT 0 1
yugabyte=> INSERT INTO test SELECT 3, 'C', 'Z';
INSERT 0 1
yugabyte=> CREATE INDEX test_c2_idx ON test(c2);
CREATE INDEX
yugabyte=> CREATE INDEX test_c3_idx ON test(c3);
CREATE INDEX
yugabyte=> SELECT indexrelname, idx_scan FROM pg_stat_user_indexes WHERE relname = 'test';
indexrelname | idx_scan
--------------+----------
test_c2_idx | 0
test_c3_idx | 0
(2 rows)
Let’s run a query that uses the test_c2_idx…
yugabyte=> EXPLAIN SELECT c2 FROM test WHERE c2 = 'A';
QUERY PLAN
-------------------------------------------------------------------------------
Index Only Scan using test_c2_idx on test (cost=0.00..5.12 rows=10 width=32)
Index Cond: (c2 = 'A'::text)
(2 rows)
yugabyte=> SELECT c2 FROM test WHERE c2 = 'A';
c2
----
A
(1 row)
yugabyte=> SELECT indexrelname, idx_scan FROM pg_stat_user_indexes WHERE relname = 'test';
indexrelname | idx_scan
--------------+----------
test_c2_idx | 1
test_c3_idx | 0
(2 rows)
yugabyte=> SELECT c2 FROM test WHERE c2 = 'A';
c2
----
A
(1 row)
yugabyte=> SELECT indexrelname, idx_scan FROM pg_stat_user_indexes WHERE relname = 'test';
indexrelname | idx_scan
--------------+----------
test_c2_idx | 2
test_c3_idx | 0
(2 rows)
Note that each time I ran the query, the idx_scan column count increased.
Now let’s try the test_c3_idx…
yugabyte=> EXPLAIN SELECT * FROM test WHERE c3 = 'X';
QUERY PLAN
--------------------------------------------------------------------------
Index Scan using test_c3_idx on test (cost=0.00..5.22 rows=10 width=68)
Index Cond: ((c3)::text = 'X'::text)
(2 rows)
yugabyte=> SELECT * FROM test WHERE c3 = 'X';
c1 | c2 | c3
----+----+----
1 | A | X
(1 row)
yugabyte=> SELECT indexrelname, idx_scan FROM pg_stat_user_indexes WHERE relname = 'test';
indexrelname | idx_scan
--------------+----------
test_c2_idx | 2
test_c3_idx | 1
(2 rows)
Have Fun!
