To: pgsql-sql@postgresql.org
From: Thomas Mueller <news-exp-jul05@tmueller.com>
Subject: Re: Simple delete takes hours
Date: Sat, 05 Mar 2005 15:00:46 +0100
Lines: 30
Message-ID: <d0cdoh$tap$1@sea.gmane.org>
References: <d0807h$vuu$1@sea.gmane.org> <opsm4zaaizth1vuj@musicbox>
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-15
Content-Transfer-Encoding: 7bit
User-Agent: Mozilla Thunderbird 1.0 (X11/20050116)
In-Reply-To: <opsm4zaaizth1vuj@musicbox>

On 05.03.2005 00:24 PFC wrote:

>     Every time a row is removed from pwd_name, the ON DELETE CASCADE
> trigger  will look in pwd_name_rev if there is a row to delete... Does
> it have an  index on pwd_name_rev( rev_of ) ? If not you'll get a full
> table scan for  every row deleted in pwd_name...

Yes that's it, thanks a lot!


pwdcheck=# explain analyze delete from pwd_name where description=1;
                                                       QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------
 Seq Scan on pwd_name  (cost=0.00..116571.15 rows=1774250 width=6)
(actual time=9526.671..21957.920 rows=543348 loops=1)
   Filter: (description = 1)
 Total runtime: 35555.749 ms
(3 rows)

Is it possible to get fired triggers/called stored procedures and things
like that in an 'explain' ?

To find out why the delete is that slow I did:
pwdcheck=# explain analyze delete from pwd_name where id in
pwdcheck-# (select id from pwd_name where description=1 limit 10);

There was no hint that every deleted row leads to a full table scan.


Thomas