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.

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?

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.

If it would help I can write this out in a reproducable scenario. I am
using postgresql 7.4.5 at the moment.

Sincerely,

--
Richard van den Berg, CISSP
-------------------------------------------
Trust Factory B.V. | www.dna-portal.net
Bazarstraat 44a | www.trust-factory.com
2518AK The Hague | Phone: +31 70 3620684
The Netherlands | Fax : +31 70 3603009
-------------------------------------------


---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

> 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

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Hello Chris,

Thanks for your answers.

Christopher Kings-Lynne wrote:
> Deferring makes no difference to FK checking speed...

But why then is the speed acceptable if I copy and then manually add the
FK? Is the check done by the FK so much different from when it is done
automatically using an active deffered FK?

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

If I have to go this route, is there a way of automatically dropping and
re-adding FKs? I can probably query pg_constraints and drop the
appropriate ones, but how do I re-add them after the copy/insert?

Sincerely,

--
Richard van den Berg, CISSP
-------------------------------------------
Trust Factory B.V. | www.dna-portal.net
Bazarstraat 44a | www.trust-factory.com
2518AK The Hague | Phone: +31 70 3620684
The Netherlands | Fax : +31 70 3603009
-------------------------------------------

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

>> Deferring makes no difference to FK checking speed...
>
>
> But why then is the speed acceptable if I copy and then manually add the
> FK? Is the check done by the FK so much different from when it is done
> automatically using an active deffered FK?

Yeah I think it uses a different query formulation... Actually I only
assume that deferred fk's don't use that - I guess your experiment
proves that.

>> Well, that's what people do - even pg_dump will restore data and add
>> the foreign key afterward...
>
> If I have to go this route, is there a way of automatically dropping and
> re-adding FKs? I can probably query pg_constraints and drop the
> appropriate ones, but how do I re-add them after the copy/insert?

Actually, you can just "disable" them if you want to be really dirty
You have to be confident that the data you're inserting does satisfy
the FK, however otherwise you can end up with invalid data.

To see how to do that, try pg_dump with --disable-triggers mode enabled.
Just do a data-only dump.

Chris

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

On Thu, 14 Apr 2005, Richard van den Berg wrote:

> Hello Chris,
>
> Thanks for your answers.
>
> Christopher Kings-Lynne wrote:
> > Deferring makes no difference to FK checking speed...
>
> But why then is the speed acceptable if I copy and then manually add the
> FK? Is the check done by the FK so much different from when it is done
> automatically using an active deffered FK?

Yes, because currently the check done by the FK on an insert type activity
is a per-row inserted check while the check done when adding a FK acts on
the entire table in a go which allows better optimization of that case
(while generally being worse on small number inserts especially on large
tables). At some point, if we can work out how to do all the semantics
properly, it'd probably be possible to replace the insert type check with
a per-statement check which would be somewhere in between. That requires
access to the affected rows inside the trigger which I don't believe is
available currently.

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Stephan Szabo <sszabo@megazone.bigpanda.com> writes:
> ... At some point, if we can work out how to do all the semantics
> properly, it'd probably be possible to replace the insert type check with
> a per-statement check which would be somewhere in between. That requires
> access to the affected rows inside the trigger which I don't believe is
> available currently.

Not necessarily. It occurs to me that maybe what we need is "lossy
storage" of the trigger events. If we could detect that the queue of
pending checks for a particular FK is getting large, we could discard
the whole queue and replace it with one entry that says "run the
wholesale check again when we are ready to fire triggers". I'm not
sure how to detect this efficiently, though --- the trigger manager
doesn't presently know anything about FKs being different from
any other kind of trigger.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

Christopher Kings-Lynne wrote:
>> But why then is the speed acceptable if I copy and then manually add
>> the FK? Is the check done by the FK so much different from when it is
>> done automatically using an active deffered FK?
>
> Yeah I think it uses a different query formulation... Actually I only
> assume that deferred fk's don't use that - I guess your experiment
> proves that.

In my tests deferred or not deferred makes no difference in speed. I am
still quite surprised by how huge the difference is.. this makes FKs
quite unusable when added a lot of data to a table.


> Actually, you can just "disable" them if you want to be really dirty

Thanks for the pointer. I got this from the archives:

------------------------
update pg_class set reltriggers=0 where relname = 'YOUR_TABLE_NAME';

to enable them after you are done, do

update pg_class set reltriggers = count(*) from pg_trigger where
pg_class.oid=tgrelid and relname='YOUR_TABLE_NAME';
------------------------

I assume the re-enabling will cause an error when the copy/insert added
data that does not satisfy the FK. In that case I'll indeed end up with
invalid data, but at least I will know about it.

Thanks,

--
Richard van den Berg, CISSP
-------------------------------------------
Trust Factory B.V. | www.dna-portal.net
Bazarstraat 44a | www.trust-factory.com
2518AK The Hague | Phone: +31 70 3620684
The Netherlands | Fax : +31 70 3603009
-------------------------------------------

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

> Thanks for the pointer. I got this from the archives:
>
> ------------------------
> update pg_class set reltriggers=0 where relname = 'YOUR_TABLE_NAME';
>
> to enable them after you are done, do
>
> update pg_class set reltriggers = count(*) from pg_trigger where
> pg_class.oid=tgrelid and relname='YOUR_TABLE_NAME';
> ------------------------
>
> I assume the re-enabling will cause an error when the copy/insert added
> data that does not satisfy the FK. In that case I'll indeed end up with
> invalid data, but at least I will know about it.

No it certainly won't warn you. You have _avoided_ the check entirely.
That's why I was warning you...

If you wanted to be really careful, you could:

being;
lock tables for writes...
turn off triggers
insert
delete where rows don't match fk constraint
turn on triggers
commit;

Chris

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Christopher Kings-Lynne wrote:
> No it certainly won't warn you. You have _avoided_ the check entirely.
> That's why I was warning you...

I figured as much when I realized it was just a simple table update. I
was thinking more of a DB2 style "set integrity" command.

> If you wanted to be really careful, you could:

So I will be re-checking my own FKs. That's not really what I'd expect
from a FK.

My problem with this really is that in my database it is hard to predict
which inserts will be huge (and thus need FKs dissabled), so I would
have to code it around all inserts. Instead I can code my own integirty
logic and avoid using FKs all together.

Thanks,

--
Richard van den Berg, CISSP
-------------------------------------------
Trust Factory B.V. | www.dna-portal.net
Bazarstraat 44a | www.trust-factory.com
2518AK The Hague | Phone: +31 70 3620684
The Netherlands | Fax : +31 70 3603009
-------------------------------------------
Have you visited our new DNA Portal?
-------------------------------------------

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

> My problem with this really is that in my database it is hard to predict
> which inserts will be huge (and thus need FKs dissabled), so I would
> have to code it around all inserts. Instead I can code my own integirty
> logic and avoid using FKs all together.

Just drop the fk and re-add it, until postgres gets more smarts.

Chris

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org