1. Finds unused indexes (indexes with idx_scan = 0, excluding primary keys and unique constraints).
2. Finds duplicate indexes (same columns, same order).

You can run this in DBeaver or psql, and it should be safe for inspection — it doesn’t drop anything, just reports.

1. Find Unused Indexes

— Unused indexes (not used since last stats reset)
SELECT
schemaname,
relname AS table_name,
indexrelname AS index_name,
idx_scan,
pg_size_pretty(pg_relation_size(i.indexrelid)) AS index_size
FROM
pg_stat_user_indexes ui
JOIN
pg_index i ON ui.indexrelid = i.indexrelid
WHERE
idx_scan = 0 — Not used at all
AND NOT i.indisprimary — Not a primary key
AND NOT i.indisunique — Not a unique constraint
ORDER BY
pg_relation_size(i.indexrelid) DESC;

2. Find Duplicate Indexes

— Duplicate indexes (same table, same column order, same method)
WITH index_columns AS (
SELECT
t.relname AS table_name,
i.relname AS index_name,
a.attname AS column_name,
r.ordinality,
am.amname AS index_type,
i.oid AS index_oid
FROM
pg_class t
JOIN
pg_index ix ON t.oid = ix.indrelid
JOIN
pg_class i ON i.oid = ix.indexrelid
JOIN
pg_am am ON i.relam = am.oid
JOIN
unnest(ix.indkey) WITH ORDINALITY AS r(attnum, ordinality) ON TRUE
JOIN
pg_attribute a ON a.attnum = r.attnum AND a.attrelid = t.oid
WHERE
t.relkind = ‘r’
)
SELECT
ic1.table_name,
ic1.index_name AS index1,
ic2.index_name AS index2,
STRING_AGG(ic1.column_name, ‘, ‘ ORDER BY ic1.ordinality) AS columns,
ic1.index_type
FROM
index_columns ic1
JOIN
index_columns ic2
ON ic1.table_name = ic2.table_name
AND ic1.column_name = ic2.column_name
AND ic1.ordinality = ic2.ordinality
AND ic1.index_name < ic2.index_name
AND ic1.index_type = ic2.index_type
GROUP BY
ic1.table_name, ic1.index_name, ic2.index_name, ic1.index_type
HAVING
COUNT() = ( SELECT COUNT() FROM index_columns sub
WHERE sub.index_name = ic1.index_name
)
ORDER BY
ic1.table_name;

Notes:

• You can pair these with DROP INDEX statements after review.
• Consider exporting the results before making changes.
• You can reset stats with SELECT pg_stat_reset(); before testing usage cleanly.

How to wrap this into a reusable report or view you can run weekly

Create View: Unused Indexes

CREATE OR REPLACE VIEW report_unused_indexes AS
SELECT
schemaname,
relname AS table_name,
indexrelname AS index_name,
idx_scan,
pg_size_pretty(pg_relation_size(i.indexrelid)) AS index_size
FROM
pg_stat_user_indexes ui
JOIN
pg_index i ON ui.indexrelid = i.indexrelid
WHERE
idx_scan = 0 — Not used at all
AND NOT i.indisprimary — Not a primary key
AND NOT i.indisunique — Not a unique constraint
ORDER BY
pg_relation_size(i.indexrelid) DESC;

Create View: Duplicate Indexes

CREATE OR REPLACE VIEW report_duplicate_indexes AS
WITH index_columns AS (
SELECT
t.relname AS table_name,
i.relname AS index_name,
a.attname AS column_name,
r.ordinality,
am.amname AS index_type,
i.oid AS index_oid
FROM
pg_class t
JOIN
pg_index ix ON t.oid = ix.indrelid
JOIN
pg_class i ON i.oid = ix.indexrelid
JOIN
pg_am am ON i.relam = am.oid
JOIN
unnest(ix.indkey) WITH ORDINALITY AS r(attnum, ordinality) ON TRUE
JOIN
pg_attribute a ON a.attnum = r.attnum AND a.attrelid = t.oid
WHERE
t.relkind = ‘r’
)
SELECT
ic1.table_name,
ic1.index_name AS index1,
ic2.index_name AS index2,
STRING_AGG(ic1.column_name, ‘, ‘ ORDER BY ic1.ordinality) AS columns,
ic1.index_type
FROM
index_columns ic1
JOIN
index_columns ic2
ON ic1.table_name = ic2.table_name
AND ic1.column_name = ic2.column_name
AND ic1.ordinality = ic2.ordinality
AND ic1.index_name < ic2.index_name
AND ic1.index_type = ic2.index_type
GROUP BY
ic1.table_name, ic1.index_name, ic2.index_name, ic1.index_type
HAVING
COUNT() = ( SELECT COUNT() FROM index_columns sub
WHERE sub.index_name = ic1.index_name
)
ORDER BY
ic1.table_name;

How to Use in DBeaver:

Run those two CREATE VIEW statements.

In DBeaver, go to the Database Navigator, find your database, expand the Views section.

Right-click either report_unused_indexes or report_duplicate_indexes and choose “Read data in SQL Console” or just SELECT * FROM report_unused_indexes;.

Automate as a Weekly Report

You can:

• Schedule a cron job or pgAgent task to log the output to a table or file.
• Create a dashboard in DBeaver or another BI tool using these views.

A. Automating Weekly Cleanup Reports

Option 1: Log to a Table (for audit trail)

First, create a table to log the reports:

CREATE TABLE index_health_log (
report_type TEXT,
report_timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
table_name TEXT,
index1 TEXT,
index2 TEXT, — NULL if unused index
columns TEXT,
index_type TEXT,
index_size TEXT,
idx_scan INTEGER
);

Insert Unused Indexes Weekly:

INSERT INTO index_health_log (report_type, table_name, index1, index_size, idx_scan)
SELECT
‘unused’ AS report_type,
table_name,
index_name,
index_size,
idx_scan
FROM
report_unused_indexes;

Insert Duplicate Indexes Weekly:

INSERT INTO index_health_log (report_type, table_name, index1, index2, columns, index_type)
SELECT
‘duplicate’ AS report_type,
table_name,
index1,
index2,
columns,
index_type
FROM
report_duplicate_indexes;

Option 2: Automate with pgAgent (or cron job)

If you’re using pgAdmin or DBeaver with pgAgent installed:

1. Create a new pgAgent job.
2. Add two SQL steps (one for each insert).
3. Schedule it to run weekly.

If you’re using cron (Linux):

#Save this in index_report.sh

PGPASSWORD=yourpassword psql -U youruser -d yourdb -c “INSERT INTO index_health_log … “

And in your crontab:

0 1 * * 0 /path/to/index_report.sh >> /var/log/index_health.log 2>&1

B. Simple Monitoring Dashboard in DBeaver

You can:

1. Go to Database > Charts > Create New Chart
2. Choose data source: SELECT * FROM index_health_log WHERE report_timestamp > now() - interval '30 days'
3. Add filters:
   • report_type
   • index_size
   • idx_scan
4. Create bar or table views to track:
   • Growth of unused/duplicate indexes over time
   • Which tables are most affected

DBeaver also supports export to CSV or integration with Grafana/Metabase if needed.

All bundled into a script you can run once to set everything up.

1. Creates the views for unused and duplicate indexes

2. Creates a log table to track them over time

3. Adds insert statements you can schedule weekly

4. Gives you a query for dashboarding

🧩 Full PostgreSQL Setup Script

— STEP 1: Create view for unused indexes
CREATE OR REPLACE VIEW report_unused_indexes AS
SELECT
schemaname,
relname AS table_name,
indexrelname AS index_name,
idx_scan,
pg_size_pretty(pg_relation_size(i.indexrelid)) AS index_size
FROM
pg_stat_user_indexes ui
JOIN
pg_index i ON ui.indexrelid = i.indexrelid
WHERE
idx_scan = 0
AND NOT i.indisprimary
AND NOT i.indisunique
ORDER BY
pg_relation_size(i.indexrelid) DESC;

— STEP 2: Create view for duplicate indexes
CREATE OR REPLACE VIEW report_duplicate_indexes AS
WITH index_columns AS (
SELECT
t.relname AS table_name,
i.relname AS index_name,
a.attname AS column_name,
r.ordinality,
am.amname AS index_type,
i.oid AS index_oid
FROM
pg_class t
JOIN
pg_index ix ON t.oid = ix.indrelid
JOIN
pg_class i ON i.oid = ix.indexrelid
JOIN
pg_am am ON i.relam = am.oid
JOIN
unnest(ix.indkey) WITH ORDINALITY AS r(attnum, ordinality) ON TRUE
JOIN
pg_attribute a ON a.attnum = r.attnum AND a.attrelid = t.oid
WHERE
t.relkind = ‘r’
)
SELECT
ic1.table_name,
ic1.index_name AS index1,
ic2.index_name AS index2,
STRING_AGG(ic1.column_name, ‘, ‘ ORDER BY ic1.ordinality) AS columns,
ic1.index_type
FROM
index_columns ic1
JOIN
index_columns ic2
ON ic1.table_name = ic2.table_name
AND ic1.column_name = ic2.column_name
AND ic1.ordinality = ic2.ordinality
AND ic1.index_name < ic2.index_name
AND ic1.index_type = ic2.index_type
GROUP BY
ic1.table_name, ic1.index_name, ic2.index_name, ic1.index_type
HAVING
COUNT() = ( SELECT COUNT() FROM index_columns sub
WHERE sub.index_name = ic1.index_name
)
ORDER BY
ic1.table_name;

— STEP 3: Create log table
CREATE TABLE index_health_log (
id SERIAL PRIMARY KEY,
report_type TEXT,
report_timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
table_name TEXT,
index1 TEXT,
index2 TEXT, — NULL for unused indexes
columns TEXT,
index_type TEXT,
index_size TEXT,
idx_scan INTEGER
);

— STEP 4: Insert unused indexes into log
INSERT INTO index_health_log (report_type, table_name, index1, index_size, idx_scan)
SELECT
‘unused’ AS report_type,
table_name,
index_name,
index_size,
idx_scan
FROM
report_unused_indexes;

— STEP 5: Insert duplicate indexes into log
INSERT INTO index_health_log (report_type, table_name, index1, index2, columns, index_type)
SELECT
‘duplicate’ AS report_type,
table_name,
index1,
index2,
columns,
index_type
FROM
report_duplicate_indexes;

🖥️ DBeaver Dashboard Query (last 30 days)

SELECT
report_type,
table_name,
index1,
index2,
columns,
index_type,
index_size,
idx_scan,
report_timestamp
FROM
index_health_log
WHERE
report_timestamp > NOW() – INTERVAL ’30 days’
ORDER BY
report_timestamp DESC;

📅 Schedule the Inserts Weekly

If using pgAgent:

• Create a new job with the two INSERT INTO index_health_log... steps.
• Run it every Sunday night.

If using cron:

• Put just the INSERT steps into a .sql file
• Call it with psql from a shell script once a week

✅ index_health_monitor.sql

— ================================================
— INDEX HEALTH MONITORING SETUP SCRIPT
— Creates views, log table, and initial log insert
— ================================================

— STEP 1: Unused Index View
CREATE OR REPLACE VIEW report_unused_indexes AS
SELECT
schemaname,
relname AS table_name,
indexrelname AS index_name,
idx_scan,
pg_size_pretty(pg_relation_size(i.indexrelid)) AS index_size
FROM
pg_stat_user_indexes ui
JOIN
pg_index i ON ui.indexrelid = i.indexrelid
WHERE
idx_scan = 0
AND NOT i.indisprimary
AND NOT i.indisunique
ORDER BY
pg_relation_size(i.indexrelid) DESC;

— STEP 2: Duplicate Indexes View
CREATE OR REPLACE VIEW report_duplicate_indexes AS
WITH index_columns AS (
SELECT
t.relname AS table_name,
i.relname AS index_name,
a.attname AS column_name,
r.ordinality,
am.amname AS index_type,
i.oid AS index_oid
FROM
pg_class t
JOIN
pg_index ix ON t.oid = ix.indrelid
JOIN
pg_class i ON i.oid = ix.indexrelid
JOIN
pg_am am ON i.relam = am.oid
JOIN
unnest(ix.indkey) WITH ORDINALITY AS r(attnum, ordinality) ON TRUE
JOIN
pg_attribute a ON a.attnum = r.attnum AND a.attrelid = t.oid
WHERE
t.relkind = ‘r’
)
SELECT
ic1.table_name,
ic1.index_name AS index1,
ic2.index_name AS index2,
STRING_AGG(ic1.column_name, ‘, ‘ ORDER BY ic1.ordinality) AS columns,
ic1.index_type
FROM
index_columns ic1
JOIN
index_columns ic2
ON ic1.table_name = ic2.table_name
AND ic1.column_name = ic2.column_name
AND ic1.ordinality = ic2.ordinality
AND ic1.index_name < ic2.index_name
AND ic1.index_type = ic2.index_type
GROUP BY
ic1.table_name, ic1.index_name, ic2.index_name, ic1.index_type
HAVING
COUNT() = ( SELECT COUNT() FROM index_columns sub
WHERE sub.index_name = ic1.index_name
)
ORDER BY
ic1.table_name;

— STEP 3: Log Table
CREATE TABLE IF NOT EXISTS index_health_log (
id SERIAL PRIMARY KEY,
report_type TEXT,
report_timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
table_name TEXT,
index1 TEXT,
index2 TEXT, — NULL for unused
columns TEXT,
index_type TEXT,
index_size TEXT,
idx_scan INTEGER
);

— STEP 4: Insert Unused Indexes into Log
INSERT INTO index_health_log (report_type, table_name, index1, index_size, idx_scan)
SELECT
‘unused’ AS report_type,
table_name,
index_name,
index_size,
idx_scan
FROM
report_unused_indexes;

— STEP 5: Insert Duplicate Indexes into Log
INSERT INTO index_health_log (report_type, table_name, index1, index2, columns, index_type)
SELECT
‘duplicate’ AS report_type,
table_name,
index1,
index2,
columns,
index_type
FROM
report_duplicate_indexes;

— Optional: Confirmation output
SELECT ‘✅ Index Health Setup Complete’ AS status;

💡 To Use:

1. Save this as index_health_monitor.sql
2. Open it in DBeaver and run it as a script
3. Schedule steps 4 & 5 (the two INSERT INTO index_health_log...) weekly