Will removing a foreign key constraint affect performance?

Question

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.

Answer 1

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.