Thread: Unused indexes - PostgreSQL 9.2

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?

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

Maybe you should have a look on the subject of this email...

 

in postgresql.conf are track_activities and track_counts both on?

yes

 

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

Yes

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

Maybe you should have a look on the subject of this email...

 

in postgresql.conf are track_activities and track_counts both on?

yes

 

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

Yes

Sorry, I was too busy looking at the content.

Has the size / # rows changed recently? If the planner thinks it can load all the rows faster, it will use a seqscan  regardless if you have an index.

If that is the case, you can force index use by doing a

SET enable_seqscan = off  

before executing the query.

Sorry, I was too busy looking at the content.

Has the size / # rows changed recently? If the planner thinks it can load all the rows faster, it will use a seqscan  regardless if you have an index.

If that is the case, you can force index use by doing a

SET enable_seqscan = off  

before executing the query.

Hmm... ok... but the situation is:

1 - I dropped the index

2 - Found a very slow query

3 - The "WHERE" clause was using the index that I've just dropped

4 - I ran the query in my test environment (Same DB as prod) with explain analyze to see if the query was indeed using the index I've dropped

5 - Yes, the query was using the index

6 - re-created the index

7 - The total time went from 2000ms to 200ms

So, I don't think the index was indeed not being used.

I believe the stats are not working, just don't know how to confirm that, as I have nothing on my logs

>Some time ago I changed the pg_stat_temp directory from /var/lib/pgsq/whatever to /tmp

Have you checked the postgres log to see if there are any errors about it not being able to write to the pg_stat_temp dir?

>Some time ago I changed the pg_stat_temp directory from /var/lib/pgsq/whatever to /tmp

Have you checked the postgres log to see if there are any errors about it not being able to write to the pg_stat_temp dir?

Yep.... no errors =\ 

And what happens if you run this query?

SELECT idstat.schemaname AS schema,
       idstat.relname AS table_name,
       indexrelname AS index_name,
       idstat.idx_scan AS times_used,
       pg_size_pretty(pg_relation_size(quote_ident(idstat.schemaname) || '.' || quote_ident(idstat.relname))) AS table_size,
       pg_size_pretty(pg_relation_size(quote_ident(idstat.schemaname) || '.' || quote_ident(indexrelname))) AS index_size,
       n_tup_upd + n_tup_ins + n_tup_del as num_writes,
       indexdef AS definition
FROM pg_stat_user_indexes AS idstat
JOIN pg_indexes ON indexrelname = indexname
JOIN pg_stat_user_tables AS tabstat ON idstat.relname = tabstat.relname
WHERE indexrelname = ' {YOUR QUERY NAME } ';

My bad,  WHERE indexrelname = ' {YOUR INDEX NAME } ';

And what happens if you run this query?

SELECT idstat.schemaname AS schema,
       idstat.relname AS table_name,
       indexrelname AS index_name,
       idstat.idx_scan AS times_used,
       pg_size_pretty(pg_relation_size(quote_ident(idstat.schemaname) || '.' || quote_ident(idstat.relname))) AS table_size,
       pg_size_pretty(pg_relation_size(quote_ident(idstat.schemaname) || '.' || quote_ident(indexrelname))) AS index_size,
       n_tup_upd + n_tup_ins + n_tup_del as num_writes,
       indexdef AS definition
FROM pg_stat_user_indexes AS idstat
JOIN pg_indexes ON indexrelname = indexname
JOIN pg_stat_user_tables AS tabstat ON idstat.relname = tabstat.relname
WHERE indexrelname = ' {YOUR QUERY NAME } ';

On Mon, May 23, 2016 at 4:23 PM, Lucas Possamai <drum.lucas@gmail.com> wrote:
>
> That index has been added just 3 hours ago, and you can see that the
> times_used goes over 41000.... How is that possible??

Well, that is what it is there for, right , to be used?  My ancient
laptop can use an index that many times in less than 20 seconds,
running flat out.

>
> Don't think the query is right.
>
> Can you please check ?

The query seems right to me.

Cheers,

Jeff

On 24 May 2016 at 12:18, Jeff Janes <jeff.janes@gmail.com> wrote:

On Mon, May 23, 2016 at 4:23 PM, Lucas Possamai <drum.lucas@gmail.com> wrote:
>
> That index has been added just 3 hours ago, and you can see that the
> times_used goes over 41000.... How is that possible??

Well, that is what it is there for, right , to be used?  My ancient
laptop can use an index that many times in less than 20 seconds,
running flat out.

Yeah!
But I mean... over 70.000 times in 4 hours? that's a lot of usage! ahhahaha

 

>
> Don't think the query is right.
>
> Can you please check ?

The query seems right to me.

Cheers,

Jeff

I think the query is ok.. just wanna understand if that value is correct :O

If it is.. I'm happy with that. Just shows the work of finding and creating the index worthed it.