dump_missing_indexes.sql

-- extracted from pgcluu https://github.com/darold/pgcluu/blob/0366836dfcbf4285d08bdd36eab58f18b40d2255/pgcluu_collectd#L2473
--
-- dump_missing_indexes generate a ddl scirpt to create missing indexes on foreign keys
--
SELECT
    date_trunc('seconds',
        now()),
    current_database(),
    relname,
    'CREATE INDEX CONCURRENTLY idx_' || relname || '_' || array_to_string(column_name_list,
        '_') || ' ON ' || conrelid || ' (' || array_to_string(column_name_list,
        ',') || ')' AS ddl
FROM (
    SELECT
        DISTINCT conrelid,
        array_agg(attname) AS column_name_list,
        array_agg(attnum) AS column_list
    FROM
        pg_attribute
        JOIN (
            SELECT
                conrelid::regclass,
                conname,
                unnest(conkey) AS column_index
            FROM (
                SELECT
                    DISTINCT conrelid,
                    conname,
                    conkey
                FROM
                    pg_constraint
                    JOIN pg_class ON pg_class.oid = pg_constraint.conrelid
                    JOIN pg_namespace ON pg_namespace.oid = pg_class.relnamespace
                WHERE
                    nspname !~ '^pg_'
                    AND nspname <> 'information_schema'
                    AND pg_constraint.contype = 'f') fkey) fkey ON fkey.conrelid = pg_attribute.attrelid
            AND fkey.column_index = pg_attribute.attnum
        GROUP BY
            conrelid,
            conname) candidate_index
        JOIN pg_class ON pg_class.oid = candidate_index.conrelid
    LEFT JOIN pg_index ON pg_index.indrelid = conrelid
    AND indkey::text = array_to_string(column_list,
        ' ')
WHERE
    pg_index.indrelid IS NULL