[PERFORM] Foreign key slows down copy/insert

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

Re: [PERFORM] Foreign key slows down copy/insert

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 36

Re: [PERFORM] Foreign key slows down copy/insert

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 wi

Re: [PERFORM] Foreign key slows down copy/insert

ll 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 362

Re: [PERFORM] Foreign key slows down copy/insert

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 Hag

Re: [PERFORM] Foreign key slows down copy/insert

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-porta

Re: [PERFORM] Foreign key slows down copy/insert

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

Re: [PERFORM] Foreign key slows down copy/insert

r 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 362068

[PERFORM] Foreign key slows down copy/insert

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

[PERFORM] When are index scans used over seq scans?

nner made the right choice, but now I need to give it a hand. Could this have to do with the statistics of the tables? I make very sure (during the initial load and while testing) that I vacuum analyze all tables after I fill them. I'm runing postgres 7.4.7. Any help is appreciated. -- Richard

Re: [PERFORM] When are index scans used over seq scans?

re refering to. What is the priciple of multi-table selectivity? Your explanation sounds very plausible.. I don't mind changing the cpu_tuple_cost before running BETWEEN with timestamps, they are easy enough to spot. Thanks, -- Richard van den Berg, CISSP ---

Re: [PERFORM] When are index scans used over seq scans?

(actual time=0.004..0.011 rows=7 loops=924536) Index Cond: (("outer".starttimetrunc <= du.ts) AND ("outer".finishtimetrunc >= du.ts)) Total runtime: 44337.937 ms The explain analyze for cpu_tuple_cost = 0.01 is running now. If it takes hours, I'll send it to t

Re: [PERFORM] When are index scans used over seq scans?

ex Scan using ix_du_ts on duration du (cost=0.00..604.46 rows=1120 width=8) (actual time=0.004..0.011 rows=7 loops=924536) Index Cond: (("outer".starttimetrunc <= du.ts) AND ("outer".finishtimetrunc >= du.ts)) Total runtime: 41635.468 ms (5 rows) -- Richard van

Re: [PERFORM] When are index scans used over seq scans?

"2005-04-04 11:53:00.98","2005-04-04 22:35:00.38","2005-04-05 11:13:00.02","2005-04-05 21:31:00.98","2005-04-06 10:45:01","2005-04-07 02:08:08.25","2005-04-07 16:20:00.93","2005-04-08 10:25:00.40&

Re: [PERFORM] When are index scans used over seq scans?

an TO off; This doesn't help much. Instead of turning seqscans off this setting increases its cost with 100M. Since my query already has a cost of about 400M-800M this doesn't matter much. For now, the only reliable way of forcing the use of the index is to set cpu_tuple_cost = 1.

Re: [PERFORM] When are index scans used over seq scans?

to question what exactly is happening in those > comparisons. Your guess is as good as mine (actually, yours is much better). I can put together a reproducable test case if you like.. -- Richard van den Berg, CISSP --- Trust Factory B.V. | www.dna-

Re: [PERFORM] When are index scans used over seq scans?

n't help) to speed up this query. I am using the debian package. How can I tell if profiling is enabled? Thanks a lot, -- Richard van den Berg, CISSP --- Trust Factory B.V. | www.dna-portal.net Bazarstraat 44a| www.trust-factory.com 2518AK T

[PERFORM] Explain not accurate

ually runs quite fast. Even "explain analyze" shows these costs. This makes me wonder: can the estimates explain shows be dead wrong? I can explain in more detail (including the query and output of explain) if needed. I'm using 7.4 on Solaris 8. Sincerely, -- Richard van d