Re: Unused indexes - PostgreSQL 9.2

On Tue, May 10, 2016 at 4:40 PM, Lucas Possamai <drum(dot)lucas(at)gmail(dot)com>
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.