Re: Unused indexes - PostgreSQL 9.2

[Melvin Davidson <melvin6925@xxxxxxxxx>]

On Tue, May 10, 2016 at 4:40 PM, Lucas Possamai <drum.lucas@xxxxxxxxx> wrote:

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?

My crystal ball is not working,  you have a PostgreSQL version?

in postgresql.conf are track_activities and track_counts both on?

Did you ANALYZE the table after you re-added the index?

--

Melvin Davidson
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.