Re: Slow deletes

Aha, I did have a dependency I missed. I did a pull from a dump:

CREATE CONSTRAINT TRIGGER "<unnamed>" AFTER DELETE ON "syslog_event"
FROM "syslog_event_message_event" NOT DEFERRABLE INITIALLY IMMEDIATE
FOR EACH ROW EXECUTE PROCEDURE
"RI_FKey_cascade_del" ('<unnamed>', 'syslog_event_message_event',
'syslog_event', 'UNSPECIFIED', 'event_id', 'event_id');

Both columns are the same type (int8).

=> \d syslog_event_message_event
Table "syslog_event_message_event"
Column | Type | Modifiers
------------+--------+-----------
message_id | bigint | not null
event_id | bigint | not null
Primary key: syslog_event_message_event_pkey
Triggers: RI_ConstraintTrigger_13220957

The table contains no rows (previously deleted, vacuumed, and analyzed).

By the by, it seems from the various timings of this (and knowing how long
it takes to dump/rebuild the database in general), I could have solved my
specific problem by dumping the database, deleting the required rows using
sed/grep/vi, and rebuilt the database. While this may be a bit of
an exaggeration, it is not far off! Somehow, this just seems wrong. I
must be doing something strange to have such slow deletes, and I can't
seem to find it.

Regards,
Ed

On Mon, 12 Aug 2002, Tom Lane wrote:

> Edmund Dengler <edmundd(at)eSentire(dot)com> writes:
> > Anyway to conveniently dump the triggers (3 are constraint check, my own
> > personal one is suppose to be on inserts only)?
>
> The RI on-delete trigger must be the issue then. Lack of indexes, or
> linking to a foreign key of a different column type are the trouble
> causes I've seen there ...
>
> regards, tom lane
>