CREATE INDEX CONCURRENTLY ix_clientids2 ON gorfs.inode_segments USING btree (full_path);
Thanks for the help, guys!
Melvin, that Query you sent is very interesting..
SELECT n.nspname as schema, i.relname as table, i.indexrelname as index, i.idx_scan, i.idx_tup_read, i.idx_tup_fetch, CASE WHEN idx.indisprimary THEN 'pkey' WHEN idx.indisunique THEN 'uidx' ELSE 'idx' END AS type, pg_get_indexdef(idx.indexrelid), CASE WHEN idx.indisvalid THEN 'valid' ELSE 'INVALID' END as statusi, pg_relation_size(quote_ident(n.nspname)|| '.' || quote_ident(i.relname)) as size_in_bytes, pg_size_pretty(pg_relation_size(quote_ident(n.nspname)|| '.' || quote_ident(i.relname))) as size FROM pg_stat_all_indexes i JOIN pg_class c ON (c.oid = i.relid) JOIN pg_namespace n ON (n.oid = c.relnamespace) JOIN pg_index idx ON (idx.indexrelid = i.indexrelid ) WHERE n.nspname NOT LIKE 'pg_%' ORDER BY 1, 2, 3;
I've found more then 100 indexes that the columns:
"idx_scan", "idx_tup_read" and "idx_tup_fetch" are 0.
So, it's safe to say that they are not being used, is that right?
But some indexes have almost 100GB on the size column. This means they are not being used now, but they could be used in the past?