From: Andy <frum@ar-sd.net>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Steinar H. Gunderson <sgunderson@bigfoot.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Massive delete performance
Date: Tue, 11 Oct 2005 17:27:40 +0300
Message-ID: <001101c5ce6f$f0f4e480$0b00a8c0@forge> (raw)
References: <00bd01c5ce37$f8a4cc00$0b00a8c0@forge>
<20051011121942.GA25543@uio.no>
<01e401c5ce67$93d65de0$0b00a8c0@forge>
<13905.1129040232@sss.pgh.pa.us>
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@sss.pgh.pa.us>
To: "Andy" <frum@ar-sd.net>
Cc: "Steinar H. Gunderson" <sgunderson@bigfoot.com>;
<pgsql-performance@postgresql.org>
Sent: Tuesday, October 11, 2005 5:17 PM
Subject: Re: [PERFORM] Massive delete performance
> "Andy" <frum@ar-sd.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
>
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: frum@ar-sd.net, tgl@sss.pgh.pa.us, sgunderson@bigfoot.com
Subject: Re: Massive delete performance
In-Reply-To: <001101c5ce6f$f0f4e480$0b00a8c0@forge>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox