Hi all,
I ran a query to search for unused indexes, and get some free space in my DB:
SELECT
--*,
relid::regclass AS table,
indexrelid::regclass AS index,
--pg_size_pretty(pg_relation_size(indexrelid::regclass)) AS index_size,
pg_relation_size(indexrelid::regclass) AS index_size,
idx_tup_read,
idx_tup_fetch,
idx_scan
FROM
pg_stat_user_indexes
JOIN pg_index USING (indexrelid)
WHERE
idx_scan = 0
AND indisunique IS FALSE;
The query returns the columns:
idx_tup_read,
idx_tup_fetch, idx_scan
What I did was:
1 - Run the query above
2 - select one index and drop it
3 - Found some slow queries... When I saw it, the query was using one of the index I've dropped.
4 - Re-created the index
5 - Ran the query with explain analyze (The query was indeed hitting the index)
6 - re-ran the first query above, and still.. the index wasn't being used from those statistics
7 - ?
So, my problem is: the statistics are not running? What happened to the statistics?
Do you guys know how can I update the stats?
