Ups folks,
Indeed there were 2 important indexes missing. Now it runs about 10 times
faster. Sorry for the caused trouble :) and thanx for help.
Hash IN Join (cost=3307.49..7689.47 rows=30250 width=6) (actual
time=227.666..813.786 rows=56374 loops=1)
Hash Cond: ("outer".id_order = "inner".id)
-> Seq Scan on report (cost=0.00..2458.99 rows=60499 width=10) (actual
time=0.035..269.422 rows=60499 loops=1)
-> Hash (cost=3109.24..3109.24 rows=30901 width=4) (actual
time=227.459..227.459 rows=0 loops=1)
-> Seq Scan on orders o (cost=9.73..3109.24 rows=30901 width=4)
(actual time=0.429..154.219 rows=57543 loops=1)
Filter: (NOT (hashed subplan))
SubPlan
-> Sort (cost=9.71..9.72 rows=3 width=4) (actual
time=0.329..0.330 rows=1 loops=1)
Sort Key: cp.id_ag
-> Nested Loop (cost=0.00..9.69 rows=3 width=4)
(actual time=0.218..0.224 rows=1 loops=1)
-> Index Scan using users_name_idx on users u
(cost=0.00..5.61 rows=1 width=4) (actual time=0.082..0.084 rows=1 loops=1)
Index Cond: ((name)::text = 'dc'::text)
-> Index Scan using contactpartner_id_user_idx
on contactpartner cp (cost=0.00..4.03 rows=3 width=8) (actual
time=0.125..0.127 rows=1 loops=1)
Index Cond: (cp.id_user = "outer".id)
Total runtime: 31952.811 ms
----- Original Message -----
From: "Tom Lane" <[EMAIL PROTECTED]>
To: "Andy" <[EMAIL PROTECTED]>
Cc: "Steinar H. Gunderson" <[EMAIL PROTECTED]>;
<pgsql-performance@postgresql.org>
Sent: Tuesday, October 11, 2005 5:17 PM
Subject: Re: [PERFORM] Massive delete performance
"Andy" <[EMAIL PROTECTED]> writes:
EXPLAIN ANALYZE
DELETE FROM report WHERE id_order IN
...
Hash IN Join (cost=3532.83..8182.33 rows=32042 width=6) (actual
time=923.456..2457.323 rows=59557 loops=1)
...
Total runtime: 456718.658 ms
So the runtime is all in the delete triggers. The usual conclusion from
this is that there is a foreign key column pointing at this table that
does not have an index, or is not the same datatype as the column it
references. Either condition will force a fairly inefficient way of
handling the FK deletion check.
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
