On Mon, Sep 7, 2009 at 5:05 AM, Vincent de Phily
<vincent.dephily@mobile-devices.fr> wrote:
> On Monday 07 September 2009 03:25:23 Tom Lane wrote:
>> Vincent de Phily <vincent.dephily@mobile-devices.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
where is the index on refid?
merlin
