Find missing index of Foreign Keys in PosgresSQL

SQL

/*
frameworks-related specifics:

Django generates indexes on foreign keys automatically under the hood. You can change it by providing db_index=False to ForeignKey constructor - https://docs.djangoproject.com/en/1.10/ref/models/fields/#foreignkey

A database index is automatically created on the ForeignKey. You can disable this by setting db_index to False. You may want to avoid the overhead of an index if you are creating a foreign key for consistency rather than joins, or if you will be creating an alternative index like a partial or multiple column index.

Almost all real-life cases require having these indexes. But Postgres doesn’t create them automatically as MySQL (innodb) does. It’s a significant difference

So, all migration tools or/and ORM systems except of Django require to do these indexes for Postgres in addition. I mean SQLAlchemy, Knex-migrations etc.
*/



WITH existing_indexes AS (
    SELECT
        CONCAT(t.relname, '.', a.attname) as qualified_column_name
    --     t.relname as table_name,
    --     ns.nspname as namespace,
    --     i.relname as index_name,
    --     a.attname as column_name
    FROM
        pg_class t,
        pg_class i,
        pg_index ix,
        pg_attribute a,
        pg_namespace ns
    WHERE
        t.oid = ix.indrelid
        AND i.oid = ix.indexrelid
        AND a.attrelid = t.oid
        AND a.attnum = ANY(ix.indkey)
        AND t.relkind = 'r'
        AND ns.nspname = 'public'
        AND ix.indisprimary = FALSE
        AND t.relname NOT LIKE 'pg_%'
    ORDER BY
        t.relname,
        i.relname
)
SELECT
   CONCAT(
       'CREATE INDEX IF NOT EXISTS xxx__', conrelid::regclass, '__', a.attname, '__btree', ' '
       'ON ', conrelid::regclass, ' ',
       'USING btree', ' '
       '(', a.attname, ')'
   ) AS create_index_ddl_query,
   CONCAT(
       'DROP INDEX IF EXISTS xxx__', conrelid::regclass, '__', a.attname, '__btree'
   ) AS drop_index_ddl_query,
   pg_catalog.pg_get_constraintdef(c.oid, true) as condef,
   n.nspname,
   a.attname,
   confrelid::regclass AS foreign_table_name,
   c.conrelid::regclass,
   c.conname,
   pg_get_constraintdef(c.oid),
   pgc.reltuples::bigint
FROM pg_constraint c
INNER JOIN pg_class pgc ON (c.conrelid = pgc.oid)
INNER JOIN pg_namespace n ON n.oid = c.connamespace
CROSS JOIN LATERAL unnest(c.conkey) ak(k)
INNER JOIN pg_attribute a ON a.attrelid = c.conrelid AND a.attnum = ak.k
LEFT OUTER JOIN pg_description des ON (des.objoid=c.oid AND des.classoid='pg_constraint'::regclass)
WHERE
    contype = 'f'
    AND concat(conrelid::regclass, '.', a.attname) NOT IN (
        SELECT qualified_column_name FROM existing_indexes
    )
ORDER BY pgc.reltuples DESC;