DBA: находим бесполезные индексы

WITH sch AS (
  SELECT
    'public'::text sch -- schema
)
, def AS (
  SELECT
    clr.relname nmt
  , cli.relname nmi
  , pg_get_indexdef(cli.oid) def
  , cli.oid clioid
  , clr
  , cli
  , idx
, (
    SELECT
      array_agg(T::text ORDER BY f.i)
    FROM
      (
        SELECT
          clr.oid rel
        , i
        , idx.indkey[i] ik
        FROM
          generate_subscripts(idx.indkey, 1) i
      ) f
    JOIN
      pg_attribute T
        ON (T.attrelid, T.attnum) = (f.rel, f.ik)
  ) fld$
  FROM
    pg_class clr
  JOIN
    pg_index idx
      ON idx.indrelid = clr.oid AND
      idx.indexprs IS NULL
  JOIN
    pg_class cli
      ON cli.oid = idx.indexrelid
  JOIN
    pg_namespace nsp
      ON nsp.oid = cli.relnamespace AND
      nsp.nspname = (TABLE sch)
  WHERE
    NOT idx.indisunique AND
    idx.indisready AND
    idx.indisvalid
  ORDER BY
    clr.relname, cli.relname
)
, fld AS (
  SELECT
    *
  , ARRAY(
      SELECT
        (att::pg_attribute).attname
      FROM
        unnest(fld$) att
    ) nmf$
  , ARRAY(
      SELECT
        (
          SELECT
            typname
          FROM
            pg_type
          WHERE
            oid = (att::pg_attribute).atttypid
        )
      FROM
        unnest(fld$) att
    ) tpf$
  , CASE
      WHEN def ~ ' WHERE ' THEN regexp_replace(def, E'.* WHERE ', '')
    END wh
  FROM
    def
)
, pre AS (
  SELECT
    nmt
  , wh
  , nmf$
  , tpf$
  , nmi
  , def
  FROM
    fld
  ORDER BY
    1, 2, 3
)
SELECT DISTINCT
  Y.*
FROM
  pre X
JOIN
  pre Y
    ON Y.nmi <> X.nmi AND
    (Y.nmt, Y.wh) IS NOT DISTINCT FROM (X.nmt, X.wh) AND
    (
      Y.nmf$[1:array_length(X.nmf$, 1)] = X.nmf$ OR
      X.nmf$[1:array_length(Y.nmf$, 1)] = Y.nmf$
    )
ORDER BY
  1, 2, 3;

WITH sch AS (
  SELECT
    'public'::text sch -- schema
)
, def AS (
  SELECT
    clr.relname nmt
  , cli.relname nmi
  , pg_get_indexdef(cli.oid) def
  , cli.oid clioid
  , clr
  , cli
  , idx
, (
    SELECT
      array_agg(T::text ORDER BY f.i)
    FROM
      (
        SELECT
          clr.oid rel
        , i
        , idx.indkey[i] ik
        FROM
          generate_subscripts(idx.indkey, 1) i
      ) f
    JOIN
      pg_attribute T
        ON (T.attrelid, T.attnum) = (f.rel, f.ik)
  ) fld$
, (
    SELECT
      array_agg(replace(opcname::text, '_ops', '') ORDER BY f.i)
    FROM
      (
        SELECT
          clr.oid rel
        , i
        , idx.indclass[i] ik
        FROM
          generate_subscripts(idx.indclass, 1) i
      ) f
    JOIN
      pg_opclass T
        ON T.oid = f.ik
  ) opc$
  FROM
    pg_class clr
  JOIN
    pg_index idx
      ON idx.indrelid = clr.oid
  JOIN
    pg_class cli
      ON cli.oid = idx.indexrelid
  JOIN
    pg_namespace nsp
      ON nsp.oid = cli.relnamespace AND
      nsp.nspname = (TABLE sch)
  WHERE
    NOT idx.indisunique AND
    idx.indisready AND
    idx.indisvalid
  ORDER BY
    clr.relname, cli.relname
)
, fld AS (
  SELECT
    *
  , ARRAY(
      SELECT
        (att::pg_attribute).attname
      FROM
        unnest(fld$) att
    ) nmf$
  , ARRAY(
      SELECT
        (
          SELECT
            typname
          FROM
            pg_type
          WHERE
            oid = (att::pg_attribute).atttypid
        )
      FROM
        unnest(fld$) att
    ) tpf$
  FROM
    def
)
SELECT
  nmt
, nmi
, def
, nmf$
, tpf$
, opc$
FROM
  fld
WHERE
  'timestamp' = ANY(tpf$[1:array_length(tpf$, 1) - 1]) OR
  'timestamptz' = ANY(tpf$[1:array_length(tpf$, 1) - 1]) OR
  'timestamp' = ANY(opc$[1:array_length(opc$, 1) - 1]) OR
  'timestamptz' = ANY(opc$[1:array_length(opc$, 1) - 1])
ORDER BY
  1, 2;

WITH sch AS (
  SELECT
    'public'::text sch -- schema
)
, def AS (
  SELECT
    clr.relname nmt
  , cli.relname nmi
  , pg_get_indexdef(cli.oid) def
  , cli.oid clioid
  , clr
  , cli
  , idx
, (
    SELECT
      array_agg(T::text ORDER BY f.i)
    FROM
      (
        SELECT
          clr.oid rel
        , i
        , idx.indkey[i] ik
        FROM
          generate_subscripts(idx.indkey, 1) i
      ) f
    JOIN
      pg_attribute T
        ON (T.attrelid, T.attnum) = (f.rel, f.ik)
  ) fld$
, (
    SELECT
      array_agg(replace(opcname::text, '_ops', '') ORDER BY f.i)
    FROM
      (
        SELECT
          clr.oid rel
        , i
        , idx.indclass[i] ik
        FROM
          generate_subscripts(idx.indclass, 1) i
      ) f
    JOIN
      pg_opclass T
        ON T.oid = f.ik
  ) opc$
  FROM
    pg_class clr
  JOIN
    pg_index idx
      ON idx.indrelid = clr.oid
  JOIN
    pg_class cli
      ON cli.oid = idx.indexrelid
  JOIN
    pg_namespace nsp
      ON nsp.oid = cli.relnamespace AND
      nsp.nspname = (TABLE sch)
  WHERE
    NOT idx.indisunique AND
    idx.indisready AND
    idx.indisvalid
  ORDER BY
    clr.relname, cli.relname
)
, fld AS (
  SELECT
    *
  , ARRAY(
      SELECT
        (att::pg_attribute).attname
      FROM
        unnest(fld$) att
    ) nmf$
  , ARRAY(
      SELECT
        (
          SELECT
            typname
          FROM
            pg_type
          WHERE
            oid = (att::pg_attribute).atttypid
        )
      FROM
        unnest(fld$) att
    ) tpf$
  FROM
    def
)
SELECT
  nmt
, nmi
, def
, nmf$
, tpf$
, opc$
FROM
  fld
WHERE
  (
    'bool' = ANY(tpf$) OR
    'bool' = ANY(opc$)
  ) AND
  NOT(
    ARRAY(
      SELECT
        nmf$[i:i+1]::text
      FROM
        generate_series(1, array_length(nmf$, 1) - 1) i
    ) &&
    ARRAY[ -- добавить пары-исключения по вкусу
      '{leaf_pid,leaf_type}'
    ]
  )
ORDER BY
  1, 2;

WITH sch AS (
  SELECT
    'public'::text sch -- schema
)
, def AS (
  SELECT
    clr.relname nmt
  , cli.relname nmi
  , pg_get_indexdef(cli.oid) def
  , cli.oid clioid
  , clr
  , cli
  , idx
, (
    SELECT
      array_agg(T::text ORDER BY f.i)
    FROM
      (
        SELECT
          clr.oid rel
        , i
        , idx.indkey[i] ik
        FROM
          generate_subscripts(idx.indkey, 1) i
      ) f
    JOIN
      pg_attribute T
        ON (T.attrelid, T.attnum) = (f.rel, f.ik)
  ) fld$
  FROM
    pg_class clr
  JOIN
    pg_index idx
      ON idx.indrelid = clr.oid
  JOIN
    pg_class cli
      ON cli.oid = idx.indexrelid
  JOIN
    pg_namespace nsp
      ON nsp.oid = cli.relnamespace AND
      nsp.nspname = (TABLE sch)
  WHERE
    NOT idx.indisunique AND
    idx.indisready AND
    idx.indisvalid AND
    cli.relam = (
      SELECT
        oid
      FROM
        pg_am
      WHERE
        amname = 'btree'
      LIMIT 1
    )
  ORDER BY
    clr.relname, cli.relname
)
, fld AS (
  SELECT
    *
  , ARRAY(
      SELECT
        (att::pg_attribute).attname
      FROM
        unnest(fld$) att
    ) nmf$
  , ARRAY(
      SELECT
        (
          SELECT
            typname
          FROM
            pg_type
          WHERE
            oid = (att::pg_attribute).atttypid
        )
      FROM
        unnest(fld$) att
    ) tpf$
  FROM
    def
)
SELECT
  nmt
, nmi
, nmf$
, tpf$
, def
FROM
  fld
WHERE
  tpf$ && ARRAY(
    SELECT
      typname
    FROM
      pg_type
    WHERE
      typname ~ '^_'
  )
ORDER BY
  1, 2;

WITH sch AS (
  SELECT
    'public'::text sch -- schema
)
, def AS (
  SELECT
    clr.relname nmt
  , cli.relname nmi
  , pg_get_indexdef(cli.oid) def
  , cli.oid clioid
  , clr
  , cli
  FROM
    pg_class clr
  JOIN
    pg_index idx
      ON idx.indrelid = clr.oid
  JOIN
    pg_class cli
      ON cli.oid = idx.indexrelid
  JOIN
    pg_namespace nsp
      ON nsp.oid = cli.relnamespace AND
      nsp.nspname = (TABLE sch)
  WHERE
    NOT idx.indisprimary AND
    idx.indisready AND
    idx.indisvalid AND
    NOT EXISTS(
      SELECT
        NULL
      FROM
        pg_constraint
      WHERE
        conindid = cli.oid
      LIMIT 1
    ) AND
    pg_relation_size(cli.oid) > 1 << 20 -- меньше 1MB нас не интересуют
  ORDER BY
    clr.relname, cli.relname
)
, fld AS (
  SELECT
    *
  , regexp_replace(
      CASE
        WHEN def ~ ' USING btree ' THEN
          regexp_replace(def, E'.* USING btree (.*?)($| WHERE .*)', E'\\1')
      END
    , E' ([a-z]*_pattern_ops|(ASC|DESC)|NULLS\\s?(?:FIRST|LAST))'
    , ''
    , 'ig'
    ) fld
  , CASE
      WHEN def ~ ' WHERE ' THEN regexp_replace(def, E'.* WHERE ', '')
    END wh
  FROM
    def
)
, q AS (
  SELECT
    nmt
  , $q$-- $q$ || quote_ident(nmt) || $q$
      SET search_path = $q$ || quote_ident((TABLE sch)) || $q$, public;
      SELECT
        ARRAY[
          count(*)
        $q$ || string_agg(
          ', coalesce(sum((' || coalesce(wh, 'TRUE') || ')::integer), 0)' || E'\n' ||
          ', coalesce(sum(((' || coalesce(wh, 'TRUE') || ') AND (' || fld || ' IS NULL))::integer), 0)' || E'\n'
        , '' ORDER BY nmi) || $q$
        ]
      FROM
        $q$ || quote_ident((TABLE sch)) || $q$.$q$ || quote_ident(nmt) || $q$
    $q$ q
  , array_agg(clioid ORDER BY nmi) oid$
  , array_agg(nmi ORDER BY nmi) idx$
  , array_agg(fld ORDER BY nmi) fld$
  , array_agg(wh ORDER BY nmi) wh$
  FROM
    fld
  WHERE
    fld IS NOT NULL
  GROUP BY
    1
  ORDER BY
    1
)
, res AS (
  SELECT
    *
  , (
      SELECT
        qty
      FROM
        dblink(
          'dbname=' || current_database() || ' port=' || current_setting('port')
        , q
        ) T(qty bigint[])
    ) qty
  FROM
    q
)
, iter AS (
  SELECT
    *
  , generate_subscripts(idx$, 1) i
  FROM
    res
)
, stat AS (
  SELECT
    nmt table_name
  , idx$[i] index_name
  , pg_relation_size(oid$[i]) index_size
  , pg_size_pretty(pg_relation_size(oid$[i])) index_size_humanize
  , regexp_replace(fld$[i], E'^\\((.*)\\)$', E'\\1') index_fields
  , regexp_replace(wh$[i], E'^\\((.*)\\)$', E'\\1') index_cond
  , qty[1] table_rec_count
  , qty[i * 2] index_rec_count
  , qty[i * 2 + 1] index_rec_count_null
  FROM
    iter
)
SELECT
  *
, CASE
    WHEN table_rec_count > 0
      THEN index_rec_count::double precision / table_rec_count::double precision * 100
    ELSE 0
  END::numeric(32,2) index_cover_prc
, CASE
    WHEN index_rec_count > 0
      THEN index_rec_count_null::double precision / index_rec_count::double precision * 100
    ELSE 0
  END::numeric(32,2) index_null_prc
FROM
  stat
WHERE
  index_rec_count_null * 4 > index_rec_count -- минимум четверть NULL-записей
ORDER BY
  1, 2;