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.

  • 1
    If you can find an algorithm which defines where should a FK be, then it can be done, yes. Furthermore, looking at your JOINs you can spot candidates, too. Indexes on all these columns are usually a must, but there might be others that would make sense. – dezso Nov 24 '15 at 15:14
  • Thanks @dezso, actually looking on JOINs is one of the things I was thinking on, but cannot be done on code side, as there are way too many apps using the DB. That's why I was wondering on a DB approach to "peek" into query history / logs or something like that. – Gonzalo Vasquez Nov 24 '15 at 15:17
  • If you don't log everything already, you can start it now, collect the data and then analyze later - be prepared with free disk space for the log amount that might be generated. A database-wide log_statement = all might be needed. Don't forget to switch it off later :) – dezso Nov 24 '15 at 15:33
  • @dezso Ok, we'll start logging for sometime (depending) on free disk (as you suggested), but what tool could we use later for log processing? – Gonzalo Vasquez Nov 24 '15 at 15:34
  • You could use grep -i join :D Seriously, pgbadger could help you finding the typical queries - with some parameters, you may find most of the interesting ones. – dezso Nov 24 '15 at 15:55

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 $1[i]
        FROM generate_series(array_lower($1, 1), array_upper($1, 1)) i
    ORDER BY 1
' language sql;

  SELECT conrelid::regclass
    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.

| improve this answer | |
  • While this link may answer the question, it is better to include the essential parts of the answer here and provide the link for reference. Link-only answers can become invalid if the linked page changes. - From Review – RolandoMySQLDBA Nov 24 '15 at 16:39
  • You're right, thanks. I've updated the answer to include the query. – Károly Nagy Nov 24 '15 at 17:07
  • @KárolyNagy great tip for missing indexes from foreign keys, will add to the log/pgbadger procedure – Gonzalo Vasquez Nov 24 '15 at 17:42
  • The pgexperts scripts have worked quite well for me in production. They have an added benefit of being provided by major contributors to PostgreSQL as well. – Kassandry Nov 24 '15 at 23:14

This is hard to do generically without knowing naming conventions.

Assuming all tables start with t_ and assuming foreign key columns end with _id then this query will (at least) list most foreign keys, both ones that are missing and present. It will also suggest the name of the destination table.

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
        tc.constraint_name, tc.table_name, kcu.column_name, 
        ccu.table_name AS foreign_table_name,
        ccu.column_name AS foreign_column_name 
        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 
    data_to_find.source_column_name = foreign_keys.column_name
ORDER BY foreign_key, match_found, source_table_name, source_column_name
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.