Finding foreign keys that are missing indexes

[plu tard <plutard12@xxxxxxxxxxx>]

I'm aware that if you create a foreign key constraint, no indexes are automatically created.

I would like to find a way to programatically inspect all my foreign keys and identify possibly missing indexes on either table (either the table defining the constraint or the table being referenced).

I wasn't able to find anything searching Google or the pg archives.

Attached is a first attempt. Just run the missing-fk-indexes.sql through psql. e.g.,

  psql -q mydb -f missing-fk-indexes.sql

I know the output can be improved, but is this headed toward the right direction and/or is there already a simpler way to accomplish this?

Briefly, it finds all the unique tables/columns referenced by foreign keys. Then it examines all the indexes, looking for any that are a prefix of the fk columns. It writes out any tables/columns where no indexes are found, followed by a list of the fk's that reference those tables/columns.

Also attached is a trivial test schema to run it against.

---

Life on your PC is safer, easier, and more enjoyable with Windows Vista®. See how

/* 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'
;

CREATE TABLE cars (
  car_id serial PRIMARY KEY
  ,make text NOT NULL
  ,model text NOT NULL
);

CREATE TABLE owners (
  owner_id serial PRIMARY KEY
  ,name text
);

CREATE TABLE owner_cars (
  owner_car_id serial PRIMARY KEY
  ,owner_id integer NOT NULL REFERENCES owners ON UPDATE CASCADE
  ,car_id integer NOT NULL REFERENCES cars ON UPDATE CASCADE
);

-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general