Re: slow query : very simple delete, 100% cpu, nearly no disk activity

On Monday 07 September 2009 03:25:23 Tom Lane wrote:
> Vincent de Phily <vincent(dot)dephily(at)mobile-devices(dot)fr> writes:
> > I've been running this simple delete since yesterday afternoon :
> >> db=# explain delete from message where datetime < '2009-03-03';
> >> Seq Scan on message (cost=0.00..34131.95 rows=133158 width=6)
> >> Filter: (datetime < '2009-03-03 00:00:00'::timestamp without time zone)
> >
> > There is no index on that column, so a seqscan is fine. But it really
> > shouldn't take > 15 hours to delete :
>
> 99% of the time, the reason a delete takes way longer than it seems like
> it should is trigger firing time. In particular, foreign key triggers
> where you don't have an index on the referencing column. Are there
> any foreign keys linking to this table?

Yes, but they look fine to me (?). Only one FK references the table; it's an
internal reference :

Table "public.message"
Column | Type | Modifiers
-----------+-----------------------------+------------------------------------------------------
id | integer | not null default
nextval('message_id_seq'::regclass)
unitid | integer | not null
userid | integer |
refid | integer |
(...)
Indexes:
"message_pkey" PRIMARY KEY, btree (id)
"message_unitid_fromto_status_idx" btree (unitid, fromto, status)
"message_userid_idx" btree (userid)
Foreign-key constraints:
"message_refid_fkey" FOREIGN KEY (refid) REFERENCES message(id) ON UPDATE
CASCADE ON DELETE CASCADE
"message_unitid_fkey" FOREIGN KEY (unitid) REFERENCES units(id) ON UPDATE
CASCADE ON DELETE CASCADE
"message_userid_fkey" FOREIGN KEY (userid) REFERENCES users(id) ON UPDATE
CASCADE ON DELETE CASCADE

Table "public.units"
Column | Type | Modifiers
-------------+-----------------------------+----------------------------------------------------
id | integer | not null default
nextval('units_id_seq'::regclass)
(...)
Indexes:
"units_pkey" PRIMARY KEY, btree (id)
"units_modid_ukey" UNIQUE, btree (modid)
"units_profileid_idx" btree (profileid)
Foreign-key constraints:
"units_profileid_fkey" FOREIGN KEY (profileid) REFERENCES profiles(id) ON
UPDATE CASCADE ON DELETE RESTRICT

Table "public.users"
Column | Type | Modifiers
----------+-----------------------+----------------------------------------------------
id | integer | not null default
nextval('users_id_seq'::regclass)
(...)
Indexes:
"users_pkey" PRIMARY KEY, btree (id)
"users_login_ukey" UNIQUE, btree (login)

Table users has a handdull of rows, table units has around 40000. 43% of
message.refid is NULL.

The delete finished during the weekend (DELETE 184368). Nothing in the logs
except the duration time (103113291.307 ms). I took a db dump before the
delete finished, in order to be able to reproduce the issue (a 30min test
shows me it is still slow).

--
Vincent de Phily
Mobile Devices
+33 (0) 666 301 306
+33 (0) 142 119 325

Warning
This message (and any associated files) is intended only for the use of its
intended recipient and may contain information that is confidential, subject
to copyright or constitutes a trade secret. If you are not the intended
recipient you are hereby notified that any dissemination, copying or
distribution of this message, or files associated with this message, is
strictly prohibited. If you have received this message in error, please
notify us immediately by replying to the message and deleting it from your
computer. Any views or opinions presented are solely those of the author
vincent(dot)dephily(at)mobile-devices(dot)fr and do not necessarily represent those of
the
company. Although the company has taken reasonable precautions to ensure no
viruses are present in this email, the company cannot accept responsibility
for any loss or damage arising from the use of this email or attachments.