PostgreSQL SELECT statement to find/create foreign keys that are missing indexes

pgsql-find-create-missing-indexes.sql

-- find missing indexes
SELECT
	conrelid::regclass, conname, reltuples::bigint
FROM
	pg_constraint
JOIN pg_class 
	ON conrelid = pg_class.oid
WHERE 
	contype = 'f'
	AND NOT EXISTS (
		SELECT 1
		FROM
			pg_index
		WHERE
			indrelid = conrelid
			AND (
				SELECT
					ARRAY(
						SELECT
							conkey[i]
						FROM
							generate_series(array_lower(conkey, 1), array_upper(conkey, 1)) i
						ORDER BY
							1
					)
			) = (
				SELECT
					ARRAY(
						SELECT
							indkey[i]
						FROM
							generate_series(array_lower(indkey, 1), array_upper(indkey, 1)) i
						ORDER BY
							1
					)
			)
	)
ORDER BY
	reltuples DESC

-- generate statements
SELECT 'CREATE INDEX idx_' || table_name || '_' || column_name || ' ON '
   || foreign_schema || '.' || table_name || '(' || column_name || ');'
FROM (
	SELECT
	    n.nspname AS schema,
	    cl.relname AS table_name,
	    a.attname AS column_name,
	    ct.conname AS key_name,
	    nf.nspname AS foreign_schema,
	    clf.relname AS foreign_table_name,
	    af.attname AS foreign_column_name,
	    pg_get_constraintdef(ct.oid) AS create_sql
	FROM 
		pg_catalog.pg_attribute a
	JOIN pg_catalog.pg_class cl 
		ON a.attrelid = cl.oid AND cl.relkind = 'r'
	JOIN pg_catalog.pg_namespace n 
		ON n.oid = cl.relnamespace
	JOIN pg_catalog.pg_constraint ct 
		ON a.attrelid = ct.conrelid 
		AND ct.confrelid != 0
		AND ct.conkey[1] = a.attnum
	JOIN pg_catalog.pg_class clf 
		ON ct.confrelid = clf.oid 
		AND clf.relkind	= 'r'
	JOIN pg_catalog.pg_namespace nf 
		ON nf.oid = clf.relnamespace
	JOIN pg_catalog.pg_attribute af
		ON af.attrelid = ct.confrelid
		AND af.attnum = ct.confkey[1]
) X