Search Postgresql Archives

Re: Unused indexes - PostgreSQL 9.2

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 





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.


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux