Skip to main content
Background Image
  1. PostgreSQL Posts/

Finding Unused Indexes

·376 words·2 mins· ·
Ruohang Feng
Author
Ruohang Feng
Pigsty Founder, @Vonng
Table of Contents

Author: Vonng

Indexes are useful, but they’re not free. Unused indexes are a waste. Use the following SQL to identify unused indexes:

  • First, exclude indexes used to implement constraints (can’t be dropped)
  • Expression indexes (containing field 0 in pg_index.indkey)
  • Then find indexes with zero index scans (you can also use a more lenient condition, such as fewer than 1000 scans)

Finding Unused Indexes
#

  • View name: monitor.v_bloat_indexes
  • Calculation time: 1 second, suitable for daily/manual checks, not suitable for frequent polling
  • Verified versions: 9.3 ~ 10
  • Function: Shows current database index bloat situation

Works well on versions 9.3 and 10.4. View definition:

-- CREATE SCHEMA IF NOT EXISTS monitor;
-- DROP VIEW IF EXISTS monitor.pg_stat_dummy_indexes;

CREATE OR REPLACE VIEW monitor.pg_stat_dummy_indexes AS
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 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;

COMMENT ON VIEW monitor.pg_stat_dummy_indexes IS 'monitor unused indexes'
-- Human-readable manual query
SELECT s.schemaname,
       s.relname AS tablename,
       s.indexrelname AS indexname,
       pg_size_pretty(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 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;

Batch Generate Index Drop Commands
#

SELECT 'DROP INDEX CONCURRENTLY IF EXISTS "' 
	|| s.schemaname || '"."' || s.indexrelname || '";'
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 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;

Finding Duplicate Indexes
#

Check if there are indexes working on the same columns of the same table, but be careful with partial indexes.

SELECT
  indrelid :: regclass              AS table_name,
  array_agg(indexrelid :: regclass) AS indexes
FROM pg_index
GROUP BY
  indrelid, indkey
HAVING COUNT(*) > 1;

Related

The Versatile file_fdw — Reading System Information from Your Database
·846 words·4 mins
With file_fdw, you can easily view operating system information, fetch network data, and feed various data sources into your database for unified viewing and management.
Batch Configure SSH Passwordless Login
·297 words·2 mins
Quick configuration for passwordless login to all machines
Wireshark Packet Capture Protocol Analysis
·982 words·5 mins
Wireshark is a very useful tool, especially suitable for analyzing network protocols. Here’s a simple introduction to using Wireshark for packet capture and PostgreSQL protocol analysis.
Common Linux Statistics CLI Tools
·2379 words·12 mins
top, free, vmstat, iostat: Quick reference for four commonly used CLI tools
Installing PostGIS from Source
·630 words·3 mins
PostGIS is PostgreSQL’s killer extension, but compiling and installing it isn’t easy.
Go Database Tutorial: database/sql
·4249 words·20 mins
Similar to JDBC, Go also has a standard database access interface. This article details how to use database/sql in Go and important considerations.