Can foreign keys hurt query performance

Question

As I understood from this post, there are some scenarios where foreign keys can improve query performance.

I've heard the opposite claim though, that because of referential integrity checks, foreign keys can actually hurt query performance. Under which conditions (if at all) is this true?

---

1) The term query seems to be misleading. I am interested in all kinds of performance penalties.

2) Does anyone have any real-world numbers about the negative impact on INSERT, DELETE or UPDATE statements (I know it depends on the specific system, but nevertheless any kind of real-world measurements would be appreciated)?

Answer 1

if a foreign key is needed for referential integrity then the presence of the foreign key should form the baseline for performance

you might as well ask if a car can go faster if you don't put seats in - a well formed car includes seats as much as a well formed database includes foreign keys

Answer 2

I'm assuming that for INSERT queries, constraints - including foreign key constraints - will slow performance somewhat. The database has to check that whatever you've told it to insert is something that your constraints allow it to insert.

For SELECT queries, foreign key constraints shouldn't make any changes to performance.

Since INSERTS are almost always very quick, the small amount of extra time won't be noticeable, except in fringe cases. (Building a several gigabyte database, you might want to disable constraints and then re-enable later, as long as you're sure the data is good.)