Re: Foreign key slows down copy/insert

> I am new to cross references between tables, and I am trying to
> understand how they impact performance. From reading the documentation I
> was under the impression that deffering foreign keys would yield about
> the same performance as dropping them before a copy, and adding them
> after. However, I cannot see this in my test case.

Even if you defer them, it just defers the check, doesn't eliminate it...

> I have a table A with an int column ID that references table B column
> ID. Table B has about 150k rows, and has an index on B.ID. When trying
> to copy 1 million rows into A, I get the following \timings:
>
> 1) drop FK, copy (200s), add FK (5s)
> 2) add FK defferable initially deffered, copy (I aborted after 30min)
> 3) add FK defferable initially deffered, begin, copy (200s), commit (I
> aborted after 30min)
>
> How do I explain why test cases 2 and 3 do not come close to case 1? Am
> I missing something obvious?

Deferring makes no difference to FK checking speed...

> Since the database I am working on has many FKs, I would rather not have
> to drop/add them when I am loading large data sets.

Well, that's what people do - even pg_dump will restore data and add the
foreign key afterward...

Chris