-- Drop indici mai utilizzati non unique
-- by mail@meo.bogliolo.name
-- https://www.md-c.it/meo/index.htm#post

-- NB: da utilizzare solo se si sa esattamente cosa si sta facendo...

SELECT indexrelid::regclass as index, relid::regclass as table, 'DROP INDEX ' || indexrelid::regclass || ';' AS ddl
  FROM pg_stat_user_indexes s
  JOIN pg_index USING (indexrelid)
 WHERE idx_scan = 0
   AND NOT EXISTS (SELECT 1 FROM pg_catalog.pg_constraint c WHERE c.conindid = s.indexrelid)
   AND indisunique is false;