Slide 1
Slide 1 text
Removing Unused Indexes
January 2021
Andy Atkinson
andyatkinson.com
Slide 2
Slide 2 text
Benefits of indexes
Query performance
Reduce query execution time (reduce "cost")
Enforce uniqueness (unique constraint)
Sorted
Slide 3
Slide 3 text
Downsides of Indexes
Use disk space.
Slow down UPDATE
, INSERT
and DELETE
operations
Indexed columns prevent H.O.T. updates
They contribute to VACUUM
time
They add to query planning time
Space contributes to backup and restore time
Slide 4
Slide 4 text
How did we start down this path?
Exceeding IOPS limits on cloud provider
For very large tables we were seeing very infrequent and long running autovacuum
Removing indexes helps improve write rate
Slide 5
Slide 5 text
Discovery Process 1 of 2
Make sure these are set to on
SHOW track_activities;
SHOW track_counts;
PG tracks all index scans!
Watch out for indexes used only by infrequent jobs
Run unused indexes query on primary and replicas
Slide 6
Slide 6 text
Discovery Process 2 of 2
Query to find indexes with 0 scans. Credit: Cybertec
SELECT s.schemaname,
s.relname AS tablename,
s.indexrelname AS indexname,
pg_relation_size(s.indexrelid) AS index_size
FROM pg_catalog.pg_stat_user_indexes s
JOIN pg_catalog.pg_index i ON s.indexrelid = i.indexrelid
WHERE s.idx_scan = 0 -- has never been scanned
AND 0 <>ALL (i.indkey) -- no index column is an expression
AND NOT i.indisunique -- is not a UNIQUE index
AND NOT EXISTS -- does not enforce a constraint
(SELECT 1 FROM pg_catalog.pg_constraint c
WHERE c.conindid = s.indexrelid)
ORDER BY pg_relation_size(s.indexrelid) DESC;
Slide 7
Slide 7 text
Removal Process
Fetch the create index statement prior to the removal, as a recovery plan.
SELECT pg_get_indexdef(indexrelid) AS index_query
FROM pg_index WHERE indrelid = 'users'::regclass;
Perform the create index...
with the CONCURRENTLY
option
Rails: We drop them concurrently, which requires a migration file with
disable_ddl_transaction!
and we use the concurrently
option.
Rails: We check for the presence of the index first e.g. index_exists?(:posts,
:title)
in case the index wasn't added to all environments
Slide 8
Slide 8 text
Future investigations: Corruption and bloat
Both tables and indexes may have bloat
To address bloat, reindex
the index
To reindex on PG < 12 consider tools like pg_repack
On PG >= 12, consider REINDEX INDEX CONCURRENTLY idx
;
Slide 9
Slide 9 text
References
Some helpful posts on removing unused indexes, common DBA tasks, queries for index
maintenance, and index bloat and index types.
1. https://www.cybertec-postgresql.com/en/get-rid-of-your-unused-indexes
2. Common DBA tasks for PostgreSQL
3. https://www.percona.com/blog/2020/03/31/useful-queries-for-postgresql-index-
maintenance
4. Significant database and index bloat
5. https://thoughtbot.com/blog/postgres-index-types
Slide 10
Slide 10 text
Summary
Remove unused indexes to improve write rate
Find unused indexes by looking for zero scans
For frequently utilized indexes that are bloated, remove the bloat