From: | Thomas Mueller <news-exp-jul05(at)tmueller(dot)com> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: Simple delete takes hours |
Date: | 2005-03-05 14:00:46 |
Message-ID: | d0cdoh$tap$1@sea.gmane.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Mauro Bertoli | 2005-03-05 16:31:02 | Re: Postgres performance |
Previous Message | Tom Lane | 2005-03-05 05:02:19 | Re: truncating table permissions |