CYBERTEC Logo

Foreign Key Indexing and Performance in PostgreSQL

10.2018 / Category: / Tags: | | | |
A foreign key is no wrong key!
┬ę Laurenz Albe 2018

Foreign key constraints are an important tool to keep your database consistent while also documenting relationships between tables.

A fact that is often ignored is that foreign keys need proper indexing to perform well.

This article will explain that and show you how to search for missing indexes.

Index at the target of a foreign key

In the following, I will call the table, on which the foreign key constraint is defined, the source table and the referenced table the target table.

The referenced columns in the target table must have a primary key or unique constraint. Such constraints are implemented with unique indexes in PostgreSQL. Consequently, the target side of a foreign key is automatically indexed.

This is required so that there is always a well-defined row to which the foreign key points. The index also comes handy if you want to find the row in the target table that matches a row in the source table.

Index at the source of a foreign key

In contrast to the above, PostgreSQL requires no index at the source of a foreign key.

However, such an index is quite useful for finding all source rows that reference a target row. The typical cases where you need that are:

1. You perform a join between the two tables where you explicitly search for the source rows referencing one or a few target rows. If there is an index on the columns at the source, PostgreSQL can use an efficient nested loop join.

This is well known and pretty obvious.

2. You delete rows or update key columns in the target table.

Then PostgreSQL has to check if the foreign key constraint is still satisfied. It does so by searching if there are rows in the source table that would become orphaned by the data modification. Without an index, this requires a sequential scan of the source table.

An example

Let's build a source and a target table:

Query time without an index

Looking up source rows via the link to target and deleting rows from target is unreasonably slow:

Query time with an index

After creating the appropriate index:

the queries are as fast as they should be:

How to check for missing indexes?

The following query will list all foreign key constraints in the database that do not have an index on the source columns:

The result is ordered by the size of the table so that the tables, where the missing index hurts most, are listed on top.

Should I create indexes for all foreign keys?

If the source table is small, you don't need the index, because then a sequential scan is probably cheaper than an index scan anyway.

Also, if you know that you never need the index for a join and you will never delete a row or update a key column in the target table, the index is unnecessary.

There is one simple way to proceed: create all missing indexes, wait a couple of days and then get rid of the indexes that were never used.


In order to receive regular updates on important changes in PostgreSQL, subscribe to our newsletter, or follow us on Twitter, Facebook, or LinkedIn.

5 2 votes
Article Rating
Subscribe
Notify of
guest
8 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
stagei
stagei
3 years ago

Very good article. Did the same thing on Oracle some years ago but is a newbee in regards to Postgres ­čÖé
Thx

Omri Shani
Omri Shani
3 years ago

I think it is better to filter out partial indices. I did it with where indpred is null inside the exists query on pg_index

Ivan Kleshnin
3 years ago

Can we update the "missing check" algorithm to not display indexes covered by compound key?
I mean if we have a purchase table:

purchase
- "id" (PK)
- "accountId" (FK)
- "itemId" (FK)

and a compound unique constraint:

"purchase_accountId_courseId_key" UNIQUE CONSTRAINT, btree ("accountId", "itemId")

this index should cover all queries with accountId and with itemId so two separate indexes would only waste DB space.

laurenz
laurenz
3 years ago
Reply to  Ivan Kleshnin

But that would not be reported by my query, because the unique constraint would be in pg_index. Perhaps I misunderstand you, can you give me a complete example?

Nux
Nux
4 years ago

Nice article :-).

One note about that query for checking indexes -- it only works in PG SQL 9.4 and above. Mostly because of

WITH ORDINALITY and also the

cardinality function.

Rinat Mukhtarov
Rinat Mukhtarov
4 years ago

Sorry, but query from section "How to check for missing indexes?" throw an error:

[42725] ERROR: operator is not unique: smallint[] @> smallint[]
Hint: Could not choose a best candidate operator. You might need to add explicit type casts.
Position: 1000

PostgreSQL 10.5 (Debian 10.5-2.pgdg90+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 6.3.0-18+deb9u1) 6.3.0 20170516, 64-bit

laurenz
laurenz
4 years ago

That probably means that you have created additional operators or type casts. The documentation recommends:

The set of casts between built-in types has been carefully crafted and is best not altered.

Przemysław Fusik
Przemysław Fusik
4 years ago

Hi,

thanks for great article.

It explains a lot.

:+1:

CYBERTEC Logo white
CYBERTEC PostgreSQL International GmbH
R├Âmerstra├če 19
2752 W├Âllersdorf
Austria

+43 (0) 2622 93022-0
office@cybertec.at

Get the newest PostgreSQL Info & Tools


    This site is protected by reCAPTCHA and the Google Privacy Policy & Terms of Service apply.

    ┬ę
    2024
    CYBERTEC PostgreSQL International GmbH
    phone-handsetmagnifiercrosscross-circle
    8
    0
    Would love your thoughts, please comment.x
    ()
    x
    linkedin facebook pinterest youtube rss twitter instagram facebook-blank rss-blank linkedin-blank pinterest youtube twitter instagram