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®.
http://clk.atdmt.com/MRT/go/127032870/direct/01/
/* 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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
