Skip site navigation (1) Skip section navigation (2)

Finding foreign keys that are missing indexes

From: plu tard <plutard12(at)hotmail(dot)com>
To: <pgsql-general(at)postgresql(dot)org>
Subject: Finding foreign keys that are missing indexes
Date: 2008-12-28 07:00:02
Message-ID: BLU115-W30DAE79E6FD8AB13D1E970A6E90@phx.gbl (view raw or flat)
Thread:
Lists: pgsql-general
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/

Attachment: test-schema.sql
Description: text/x-sql (355 bytes)
Attachment: missing-fk-indexes.sql
Description: text/x-sql (3.1 KB)

Responses

pgsql-general by date

Next:From: Raymond O'DonnellDate: 2008-12-28 16:44:28
Subject: Re: having two database clusters?
Previous:From: Tom LaneDate: 2008-12-28 00:08:36
Subject: Re: Weird query sort

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group