Re: Postgres index usage

"Dirschel, Steve" <steve(dot)dirschel(at)thomsonreuters(dot)com> writes:
> I queried that table for a specific index and idx_scan is 0. I
> queried pg_statio_all_indexes and can see idx_blks_read and
> idx_blks_hit have numbers in there. If the index is not being used
> then what it causing idx_blks_read and idx_blks_hit to increase over
> time? I'm wondering if those increase due to DML on the table.

Yes, I think that's the case: index updates will cause the per-block
counters to advance, but only an index search will increment idx_scan.

I'd recommend testing this theory for yourself in an idle database,
though. It's not impossible that Aurora works differently from
community PG.

Another thing to keep in mind is that in versions before PG 15,
the statistics subsystem is (by design) unreliable and might sometimes
miss events under load. This effect isn't big enough to invalidate
a conclusion that an index with idx_scan = 0 isn't being used, but
it's something to keep in mind when running small tests that are
only expected to record a few events.

regards, tom lane