Lista os indices para uso do LLD no zabbix

top-indexes-pgsql-lld.sql

-- Based on https://wiki.postgresql.org/wiki/Disk_Usage
---- To use on zabbix for LLD
---- Sorted to the greatest size to the lowest size

WITH index_stats AS (
    SELECT 
        pg_class.oid,
        nspname AS table_schema, 
        relname AS index_NAME, 
        pg_total_relation_size(pg_class.oid) AS total_bytes
    FROM pg_class
    LEFT JOIN pg_namespace ON pg_namespace.oid = pg_class.relnamespace
    WHERE pg_class.relkind = 'i'
      AND pg_namespace.nspname = 'public'
    ORDER BY total_bytes
), pretty_stats as (
    
    SELECT 
        index_stats.index_NAME as "#INDEXNAME",
        index_stats.total_bytes as "#TOTALBYTES"
    FROM index_stats
)

SELECT json_build_object('DATA', json_agg(row_to_json(data))) FROM pretty_stats as data;