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

On Mon, Sep 21, 2009 at 10:50 AM, Vincent de Phily
<vincent(dot)dephily(at)mobile-devices(dot)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(dot)dephily(at)mobile-devices(dot)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.

merlin