Discover missing foreign keys and/or indexes We have a pretty big database (around 700GB of data), which has been inherited to us, and has had several DBAs. With time we've realized that there are several missing foreign keys and indexes. 

I'd like to know if there is some automated way of discovering such potential missing keys/indexes using some kind of log parsing or something alike.
I remembered seeing a query sometime ago in a post so quick searching resulted in:

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
;


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

That will give you what you want.

And also found https://github.com/pgexperts/pgx_scripts/blob/master/indexes/fk_no_index.sql but I haven't used this myself.