Is my postgres index being used?

Question

I have a large PostgeSQL 9.1 database in production use. I want to free up some disk space. There is a large index, and I'm unsure if it's being used.

select * from pg_stat_all_indexes where indexrelname = 'myindexname';
 relid | indexrelid | schemaname | relname | indexrelname | idx_scan | idx_tup_read | idx_tup_fetch 
-------+------------+------------+-----------------+-----------------------+----------+--------------+---------------
 20625 | 19563303 | public | mytablename | myindexname | 575598 | 281635 | 0
(1 row)

Is this index being used? idx_tup_fetch is 0. Does that mean it's not being used? Can I drop the index without affecting any reads/queries?

This database is in production, real world use. This index was recently rebuilt, and there has been real world use since. If it hasn't been used since then, it will never be.

The index is about 130G and the table 50G.

• Craig Ringer: Looks like it's used to me, it's getting index scans, etc. Consider resetting the stats and then see if the counts continue increasing.

Answer 1

The documentation states the following:

Indexes can be used via either simple index scans or "bitmap" index scans. In a bitmap scan the output of several indexes can be combined via AND or OR rules, so it is difficult to associate individual heap row fetches with specific indexes when a bitmap scan is used. Therefore, a bitmap scan increments the pg_stat_all_indexes.idx_tup_read count(s) for the index(es) it uses, and it increments the pg_stat_all_tables.idx_tup_fetch count for the table, but it does not affect pg_stat_all_indexes.idx_tup_fetch.

Note: The idx_tup_read and idx_tup_fetch counts can be different even without any use of bitmap scans, because idx_tup_read counts index entries retrieved from the index while idx_tup_fetch counts live rows fetched from the table. The latter will be less if any dead or not-yet-committed rows are fetched using the index, or if any heap fetches are avoided by means of an index-only scan.

This gives you two possibilities. The first one you've excluded by rebuilding the index, so one could guess this index is used exclusively via index-only scans and/or bitmap index scans. You can confirm this buy checking the EXPLAIN output of your queries that may use this index.

You can read a bit more about bitmap scans on depesz's page.