1

I have a units table and a reports table. A unit has many reports. So reports table has a foreign key unit_id which corresponds to the id of the unit. Hence, there is a foreign key constraint in reports table.

I began to realize that sometimes reports need to be created even if the unit doesn't exist for them yet. So when I tried to create a report without a unit, I obviously got this error:

 ERROR: insert or update on table "reports" violates foreign key constraint "reports_unit_id_fkey"
  Detail: Key (unit_id)=(7295) is not present in table "units".

My question is if I remove this foreign key constraint and enable reports to be created without a unit, will this affect performance when I try to get all reports associated with a unit?

SELECT * FROM reports WHERE unit_id=7295;

The only other option I have is to insert the reports into some orphans table, but then I would have to copy those orphans over when the unit is created, which is additional work.

1 Answer 1

2

At time of writing (PostgreSQL 9.4 and below), PostgreSQL's optimiser doesn't use foreign key constraints to prove anything or allow for extra optimisations. The only impact removing one can have on performance is a performance improvement due to the removal of foreign key checks.

It's a data integrity enforcement tool, not a performance tool.

1
  • Well, with bigger tables we more or less automatically (as in 'after getting up, I automatically drink a coffee') create indexes to support the joins and DML. This way it can be considered as a performance win :) Also, the performance difference you mention is applicable to DML only. Jul 3, 2014 at 8:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.