Add support for metrics from pg_stat_user_indexes?

[lsunsi]

Proposal

Stats from the pg_stat_user_indexes group are very useful in general. One particular use case I have is figuring out unused indexes, which is hard to do without metrics over time.

I'm secretly hoping I'm missing something, because as soon as I turned this exporter on I expected to see information about indexes and I couldn't find any and couldn't find where to enable them.

[michael-todorovic]

I was searching for that as well and the closest we have is statio_user_indexes (disabled by default from the flags doc). I started to look at it from the pg view:

select * from pg_statio_user_indexes order by idx_blks_read desc;

I believe this can be a starting point until we can get pg_stat_user_indexes in the exporter :smile:

Edit: Looking at statio_user_indexes code from the exporter, I could likely provide a PR for pg_stat_user_indexes

[michael-todorovic]

I added a PR to add support for pg_stat_user_indexes. I tested it on pg15 and 16. This also supports new field last_idx_scan from pg16

[thomasgl-orange]

Thanks @michael-todorovic, came here looking for exactly this feature, was glad to see there was already a recent PR!

I've tested #1071 on a pg14 database. It worked fine, I got the _idx_scan_total, _idx_tup_fetch and _idx_tup_read metrics I was looking for. Some notes:

• on my DB (gitlab schema), that's a significant number of additional metrics (3 x 3900 = 11700), so it's good I think that this new collector is disabled by default
• when I've first deployed this patched version (with this collector enabled), I've been worried by a significant increase of the collectors scrape time (as in sum (pg_scrape_collector_duration_seconds), and also by (collector) overall). It turned out that this postgres-exporter pod was already slightly CPU-throttled before (I had not noticed), and the extra work from this collector added only to the throttled time. Increasing CPU limit solved that, and now I can say that pg_scrape_collector_duration_seconds{collector="stat_user_indexes"} is nothing unusual compared to the other ones, which is good.

So, yes, :+1: from a random happy beta-tester.

[michael-todorovic]

@thomasgl-orange glad this works for you :tada: I'm currently patching again the PR, this wasn't sufficient enough in my case. I'm adding informations about indexes, such as is_primary, is_unique, is_ready, is_valid and size. With a bit of promql, we would be able to identify big indexes that aren't for primary keys and that are rarely used (but still hurting iops :sweat_smile: ). It's almost done. On my side, I'm switching to multi-target mode and I have one basic exporter with "living" data which is scraped often and another one with only slower metrics (like indexes here) which is scraped once in a while