Analogue's Blog

home

Les index inutiles de vos bases PostgreSQL

26 Feb 2010

Les index inutiles de vos bases PostgreSQL

        SELECT
                s.schemaname AS "Schema Name",
                s.relname AS "Table Name",
                s.indexrelname AS "Index Name",
                pg_size_pretty(pg_relation_size(s.relid)) AS "Table Size",
                pg_size_pretty(pg_relation_size(s.indexrelid)) AS "Index Size"
        FROM
                pg_stat_user_indexes s
        JOIN
                pg_index i ON i.indexrelid = s.indexrelid
        LEFT JOIN
                pg_constraint c ON i.indrelid = c.conrelid
                AND array_to_string(i.indkey, \' \') = array_to_string(c.conkey, \' \')
        WHERE
                s.schemaname != \'_ob2replication\'
                AND s.idx_scan = 0
                AND i.indisunique IS FALSE
                AND c.confrelid IS NULL
        ORDER BY
                pg_relation_size(s.indexrelid) DESC
        ;

Comments