Re: Massive delete performance

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" <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Andy" <frum(at)ar-sd(dot)net>
Cc: "Steinar H. Gunderson" <sgunderson(at)bigfoot(dot)com>;
<pgsql-performance(at)postgresql(dot)org>
Sent: Tuesday, October 11, 2005 5:17 PM
Subject: Re: [PERFORM] Massive delete performance

> "Andy" <frum(at)ar-sd(dot)net> 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
>
>