PG_STAT_USER_INDEXES Sistem Tablosu - Index Hakkında Bilgi Verir

indexrelname Sütunu

Örnek -  unused index

Şöyle yaparız

SELECT relname , indexrelname , idx_scan , idx_tup_read , idx_tup_fetch
FROM pg_stat_user_indexes
WHERE schemaname = 'public' and relname = 'foo';

Açıklaması şöyle

relname: The name of the database table.

indexrelname: The name of the user index for which these statistics are relevant.

idx_scan, idx_tup_read & idx_tup_fetch: are different index statistics metrics helps us in determining whether index is being used or not.

indexrelid Sütunu

Örnek

Şöyle yaparız

SELECT
  idx_stat.indexrelid::regclass AS index_name,
  pgstattuple(idx_stat.indexrelid) AS stats
FROM
  pg_stat_user_indexes AS idx_stat

Açıklaması şöyle

The pgstattuple extension in PostgreSQL is used to gather statistical information about the physical tuples (rows) stored in a table. It provides detailed insights into the distribution and characteristics of the tuples, allowing you to analyze the table's data fragmentation, dead tuples, and overall health.