Is there a tool or method to analyze Postgres, and determine what missing indexes should be created, and which unused indexes should be removed? I have a little experience doing this with the "profiler" tool for SQLServer, but I'm not aware of a similar tool included with Postgres.
9 Answers
It can be found by using following query in postgres console
use db_name
select * from pg_stat_user_indexes;
select * from pg_statio_user_indexes;
For More Details https://www.postgresql.org/docs/current/monitoring-stats.html
CREATE EXTENSION pgstattuple;
CREATE TABLE test(t INT);
INSERT INTO test VALUES(generate_series(1, 100000));
SELECT * FROM pgstatindex('test_idx');
version | 2
tree_level | 2
index_size | 105332736
root_block_no | 412
internal_pages | 40
leaf_pages | 12804
empty_pages | 0
deleted_pages | 13
avg_leaf_density | 9.84
leaf_fragmentation | 21.42
Related