Slide 1

Slide 1 text

Removing Unused Indexes January 2021 Andy Atkinson

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. 2. Common DBA tasks for PostgreSQL 3. maintenance 4. Significant database and index bloat 5.

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