Re: Unused indexes - PostgreSQL 9.2 - Mailing list pgsql-general

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?