On Apr 14, 2005, at 7:59 AM, Richard van den Berg wrote:
How do I explain why test cases 2 and 3 do not come close to case 1?
Am I missing something obvious?
there's cost involved with enforcing the FK: if you're indexes can't be
used then you're doing a boatload of sequence scans to find and loc
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.
You may want to hunt the archives. IIRCC I saw a couple of posts in the
recent months about an update you can do to one of the system tables to disable
the key checks and t
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
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
Richard van den Berg <[EMAIL PROTECTED]> 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
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.n
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
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL P
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 comma
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 row
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 al
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 pr
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 h
On Thu, 14 Apr 2005, Tom Lane wrote:
> Stephan Szabo <[EMAIL PROTECTED]> 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
Christopher Kings-Lynne <[EMAIL PROTECTED]> 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.
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 post
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 care
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_NA
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
Stephan Szabo <[EMAIL PROTECTED]> 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
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
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...
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
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 cann
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 cann
24 matches
Mail list logo
