I gotta have my orange juice.

Jesu, Juva

PostgreSQL foreign keys and indexes

with one comment

[PostgreSQL]If you’re a frequent user of MySQL, you may be familiar with the fact that all MySQL table constraints automatically create indexes for you.  This is true of the InnoDB foreign key constraints, for which “an index is created on the referencing table automatically if it does not exist.”

If you’re switching or considering a switch to PostgreSQL, you should be aware that not all PostgreSQL table constraints will automatically create indexes for for you.  In PostgreSQL, a UNIQUE or PRIMARY KEY constraint on one or more fields will implicitly create an index for you.  However, in PostgreSQL a FOREIGN KEY constraint will not automatically create an index for you.

For each of your foreign key constraints, you should evaluate whether you want to create an index.  You may want to do this for optimizing your own queries, but be aware that it can also help to speed up DELETE queries on the referenced table and UPDATE queries on the referenced field.  This is because any foreign key reference must be located to enforce whatever ON DELETE and ON UPDATE behavior is in effect for the constraint.

Written by Scott Moonen

December 19, 2008 at 11:15 am

One Response

Subscribe to comments with RSS.

  1. This e-mail refers to “https://scottmoonen.com/2008/12/19/postgresql-foreign-keys-and-indexes/”. Firstly THANKS your info. , much appreciated. However, an update info. is: –
    PostGreSQL ver. 9.0 provides “New Foreigh Key” GUI with check box “Auto FK index” checked by default


    Walter Goydych

    April 3, 2011 at 12:22 am

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: