Thread: 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. 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 -------------------------------------------
> 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
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 -------------------------------------------
>> 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
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.
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
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 -------------------------------------------
> 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
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?
-------------------------------------------
> 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
Christopher Kings-Lynne <chriskl@familyhealth.com.au> writes:
> 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:
Probably the better bet is to drop and re-add the FK constraint.
regards, tom lane
On Thu, 14 Apr 2005, Tom Lane wrote: > 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 Yeah, but there's a potentially large middle ground where neither our current plan nor check the entire table is particularly good for that we might be able to handle better. It'd be nice to also fall back to check the entire table for even larger changes.
About the foreign key performance: Maybe foreign key checks could be delayed into the COMMIT phase. In that position, you could check, that there are lots of foreign key checks for each foreign key pending, and do the foreign key check for an area or for the whole table, if it is faster. I have heard, that the database must be in consistent state after COMMIT, but it does not have necessarily to be okay inside a transaction. 1. COMMIT wanted 2. If there are lots of foreign key checks pending, do either an area foreign key check (join result must be 0 rows), or a full table join. 3. If all foreign key checks are okay, complete the COMMIT operation. 4. If a foreign key check fails, go into the ROLLBACK NEEDED state. Maybe Tom Lane meant the same. set option delayed_foreign_keys=true; BEGIN; insert 1000 rows. COMMIT; Regards, Marko Ristola Christopher Kings-Lynne wrote: >> 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
> 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:
You're using 7.4.5. It's possible that you have a type mismatch in your
foreign keys which prevents use of the index on B.
First of all, be really sure it's THAT foreign key, ie. do your COPY with
only ONE foreign key at a time if you have several, and see which one is
the killer.
Then, supposing it's the column in A which REFERENCE's B(id) :
SELECT id FROM A LIMIT 1;
(check type)
SELECT id FROM B LIMIT 1;
(check type)
EXPLAIN ANALYZE the following :
SELECT * FROM B WHERE id = (SELECT id FROM A LIMIT 1);
It should use the index. Does it ?
PFC wrote:
> You're using 7.4.5. It's possible that you have a type mismatch in
> your foreign keys which prevents use of the index on B.
I read about this pothole and made damn sure the types match. (Actually,
I kinda hoped that was the problem, it would have been an easy fix.)
> First of all, be really sure it's THAT foreign key, ie. do your COPY
> with only ONE foreign key at a time if you have several, and see which
> one is the killer.
I took exactly this route, and the first FK I tried already hit the
jackpot. The real table had 4 FKs.
> EXPLAIN ANALYZE the following :
>
> SELECT * FROM B WHERE id = (SELECT id FROM A LIMIT 1);
>
> It should use the index. Does it ?
It sure looks like it:
Index Scan using ix_B on B (cost=0.04..3.06 rows=1 width=329) (actual
time=93.824..93.826 rows=1 loops=1)
Index Cond: (id = $0)
InitPlan
-> Limit (cost=0.00..0.04 rows=1 width=4) (actual
time=15.128..15.129 rows=1 loops=1)
-> Seq Scan on A (cost=0.00..47569.70 rows=1135570
width=4) (actual time=15.121..15.121 rows=1 loops=1)
Total runtime: 94.109 ms
The real problem seems to be what Chris and Stephen pointed out: even
though the FK check is deferred, it is done on a per-row bases. With 1M
rows, this just takes forever.
Thanks for the help.
--
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
-------------------------------------------
> Index Scan using ix_B on B (cost=0.04..3.06 rows=1 width=329) (actual
> time=93.824..93.826 rows=1 loops=1)
> Index Cond: (id = $0)
> InitPlan
> -> Limit (cost=0.00..0.04 rows=1 width=4) (actual
> time=15.128..15.129 rows=1 loops=1)
> -> Seq Scan on A (cost=0.00..47569.70 rows=1135570
> width=4) (actual time=15.121..15.121 rows=1 loops=1)
> Total runtime: 94.109 ms
94 ms for an index scan ?
this look really slow...
was the index in the RAM cache ? does it fit ? is it faster the second
time ? If it's still that slow, something somewhere is severely screwed.
B has 150K rows you say, so everything about B should fit in RAM, and you
should get 0.2 ms for an index scan, not 90 ms !
Try this :
Locate the files on disk which are involved in table B (table + indexes)
looking at the system catalogs
Look at the size of the files. Is the index severely bloated ? REINDEX ?
DROP/Recreate the index ?
Load them into the ram cache (just cat files | wc -b several times until
it's almost instantaneous)
Retry your query and your COPY
I know it's stupid... but it's a lot faster to load an index in the cache
by plainly reading the file rather than accessing it randomly.
(even though, with this number of rows, it should not be THAT slow !)
PFC wrote: > 94 ms for an index scan ? > this look really slow... That seems to be network latency. My psql client is connecting over ethernet to the database server. Retrying the command gives very different values, as low as 20ms. That 94ms was the highest I've seen. Running the same command locally (via a Unix socket) yields 2.5 ms every time. Am I correct is assuming that the timings are calculated locally by psql on my client, thus including network latency? -- 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 -------------------------------------------
> Am I correct is assuming that the timings are calculated locally by psql > on my client, thus including network latency? No explain analyze is done on the server... Chris
Christopher Kings-Lynne wrote: > No explain analyze is done on the server... Yes, but the psql \timing is calculated on the client, right? That is the value that PFC was refering to. -- 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 -------------------------------------------
Richard van den Berg <richard.vandenberg@trust-factory.com> writes:
> Christopher Kings-Lynne wrote:
>> No explain analyze is done on the server...
> Yes, but the psql \timing is calculated on the client, right? That is
> the value that PFC was refering to.
You didn't show us any \timing. The 94.109 ms figure is all server-side.
As an example:
regression=# \timing
Timing is on.
regression=# explain analyze select * from tenk1;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------
Seq Scan on tenk1 (cost=0.00..458.00 rows=10000 width=244) (actual time=0.050..149.615 rows=10000 loops=1)
Total runtime: 188.518 ms
(2 rows)
Time: 210.885 ms
regression=#
Here, 188.5 is at the server, 210.8 is at the client. The difference is
not all network delay, either --- parse/plan overhead is in there too.
regards, tom lane
Tom Lane wrote: > You didn't show us any \timing. The 94.109 ms figure is all server-side. Whoop, my mistake. I had been retesting without the explain, just the query. I re-run the explain analyze a few times, and it only reports 90ms the first time. After that it reports 2ms even over the network (the \timing on those are about 50ms which includes the network latency). 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 -------------------------------------------