/* psql -E to see internal queries */

CREATE TEMP TABLE temp_objects AS
  SELECT c.oid
         ,n.nspname
         ,c.relname
         ,c.relkind
    FROM pg_catalog.pg_class c
         LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
   WHERE 1=1
         AND n.nspname <> 'pg_catalog'
         AND n.nspname !~ '^pg_toast'
         -- AND pg_catalog.pg_table_is_visible(c.oid)
;

/* Find all unique associations used by fk's */
CREATE TEMP TABLE temp_fk AS
  SELECT DISTINCT ON (oid, cols_string)
         *
    FROM (
             SELECT nspname
                    ,relname
                    ,oid
                    ,conkey AS cols
                    ,array_to_string(conkey, ',') AS cols_string
               FROM temp_objects c
                    JOIN pg_catalog.pg_constraint r ON (r.conrelid = c.oid)
              WHERE r.contype = 'f'

              UNION

             SELECT nspname
                    ,relname
                    ,oid
                    ,confkey AS cols
                    ,array_to_string(confkey, ',') AS cols_string
               FROM temp_objects c
                    JOIN pg_catalog.pg_constraint r ON (r.confrelid = c.oid)
              WHERE r.contype = 'f'
         ) z
;

-- SELECT * FROM temp_fk;


/* look for any indexes that could be used by each fk association */
CREATE TEMP TABLE temp_check_indexes AS
SELECT fk.oid
       ,fk.cols
       ,fk.cols_string
       ,index_name || ' on ' || indkey::text AS "index"
       ,i.index_cols_string || ' ~* ' || ('^' || fk.cols_string || '(,|$)') AS "like"
       ,fk.nspname || '.' || fk.relname || '.' || fk.cols_string AS fk_name
       ,index_cols_string ~* ('^' || fk.cols_string || '(,|$)') AS has_index
  FROM temp_fk fk
       JOIN (
         SELECT temp_objects.relname AS index_name
                ,indrelid
                ,indkey AS indkey
                ,array_to_string(indkey::smallint[], ',') AS index_cols_string
           FROM pg_catalog.pg_index
                JOIN temp_objects ON (pg_index.indexrelid = temp_objects.oid)
       ) i ON (fk.oid = i.indrelid)
;

-- SELECT * FROM temp_check_indexes;

/* find all the fk associations where we *didn't* find an index */
CREATE TEMP TABLE temp_possibly_missing_indexes AS
    SELECT oid
           ,cols_string
      FROM temp_check_indexes
  GROUP BY oid, cols_string
    HAVING NOT bool_or(has_index)
;

\echo 'Possibly missing indexes on:'
SELECT t.oid
       ,c.nspname
       ,c.relname
       ,cols_string AS columns
  FROM temp_possibly_missing_indexes t
       JOIN temp_objects c ON c.oid = t.oid
;

\echo 'Foreign keys referencing above:'
SELECT DISTINCT ON (nspname, relname, conname)
       nspname
       ,relname
       ,conname AS "foreign key"
       ,CASE WHEN t.oid = r.conrelid THEN 'constraint_table' ELSE 'references_table' END AS direction
  FROM pg_catalog.pg_constraint r
       JOIN temp_objects c ON (c.oid = r.conrelid)
       JOIN temp_possibly_missing_indexes t ON (
         (t.oid = r.conrelid AND t.cols_string = array_to_string(r.conkey, ','))
         OR
         (t.oid = r.confrelid AND t.cols_string = array_to_string(r.confkey, ','))
       )
 WHERE r.contype = 'f'
;