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

On Monday 21 September 2009 17:00:36 Merlin Moncure wrote:
> On Mon, Sep 21, 2009 at 10:50 AM, Vincent de Phily
>
> <vincent.dephily@mobile-devices.fr> wrote:
> > On Friday 11 September 2009 23:55:09 Merlin Moncure wrote:
> >> On Mon, Sep 7, 2009 at 5:05 AM, Vincent de Phily
> >>
> >> <vincent.dephily@mobile-devices.fr> wrote:
> >> >                                     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
> >>
> >> where is the index on refid?
> >
> > It's
> > "message_pkey" PRIMARY KEY, btree (id)
> > because
> > (refid) REFERENCES message(id)
>
> You are thinking about this backwards.  Every time you delete a
> message, the table has to be scanned for any messages that reference
> the message being deleted because of the refid constraint (in order to
> see if any deletions must be cascaded).   PostgreSQL creates a backing
> index for primary keys automatically but not foreign keys...so you
> likely need to create an index on refid.

D'Oh ! Sounds obvious now that you mention it, and it's a very good
explanation of the delete's slowness.

I'll test this tonight or tomorrow.


--
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.dephily@mobile-devices.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.