Descubra chaves estrangeiras e/ou índices ausentes

Lembrei-me de ter visto uma consulta há algum tempo em uma postagem, então uma pesquisa rápida resultou em:

CREATE FUNCTION pg_temp.sortarray(int2[]) returns int2[] as '
  SELECT ARRAY(
      SELECT $1[i]
        FROM generate_series(array_lower($1, 1), array_upper($1, 1)) i
    ORDER BY 1
  )
' language sql;

  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 pg_temp.sortarray(conkey) = pg_temp.sortarray(indkey)
         )
ORDER BY reltuples DESC
;

De http://mlawire.blogspot.nl/2009/08/postgresql-indexes-on-foreign-keys.html

Isso lhe dará o que você quer.

E também encontrei https://github.com/pgexperts/pgx_scripts/blob/master/indexes/fk_no_index.sql , mas não usei isso sozinho.

Isso é difícil de fazer genericamente sem conhecer as convenções de nomenclatura.

Supondo que todas as tabelas comecem com t_ e assumindo que as colunas de chave estrangeira terminem com _id , essa consulta (pelo menos) listará a maioria das chaves estrangeiras, tanto as ausentes quanto as presentes. Ele também irá sugerir o nome da tabela de destino.

WITH source_data AS
(
    SELECT table_name AS source_table_name, column_name AS source_column_name
    FROM information_schema.columns
    WHERE table_schema = 'public'
    AND table_name LIKE 't_%'
    AND column_name LIKE '%\_id'
), target_data AS
(
    SELECT distinct table_name as target_table_name
    FROM information_schema.columns
    WHERE table_schema = 'public'
    AND table_name like 't_%'
), data_to_find AS
(
    SELECT *, CASE WHEN target_table_name IS NULL THEN 'No Match' ELSE 'Match' END AS match_found
    FROM source_data
    LEFT JOIN target_data ON ('t_' || LEFT(source_column_name, -3)) = target_table_name
), foreign_keys AS
(
    SELECT
        tc.constraint_name, tc.table_name, kcu.column_name, 
        ccu.table_name AS foreign_table_name,
        ccu.column_name AS foreign_column_name 
    FROM 
        information_schema.table_constraints AS tc 
        JOIN information_schema.key_column_usage AS kcu
          ON tc.constraint_name = kcu.constraint_name
        JOIN information_schema.constraint_column_usage AS ccu
          ON ccu.constraint_name = tc.constraint_name
    WHERE constraint_type = 'FOREIGN KEY'
)
SELECT CASE WHEN constraint_name IS NULL THEN 'No Foreign Key' ELSE 'Foreign Key Found' end AS foreign_key, * FROM data_to_find
LEFT JOIN foreign_keys ON 
(
    data_to_find.source_table_name = foreign_keys.table_name 
    AND
    data_to_find.source_column_name = foreign_keys.column_name
)
ORDER BY foreign_key, match_found, source_table_name, source_column_name