Re: Slow deletes

Edmund Dengler <edmundd(at)eSentire(dot)com> writes:
> Can anyone explain why these deletes are extremely slow?

> => explain delete from syslog_event where event_id = 1001;
> NOTICE: QUERY PLAN:

> Seq Scan on syslog_event (cost=0.00..342277.67 rows=1 width=6)

> There are over 5,000,000 rows in the table.

Seqscan on a 5M-row table will take a little while...

Your problem is that it's not using an indexscan, and the reason
for that is that '1001' is taken as an integer not a bigint. The
system is not smart about optimizing cross-datatype comparisons
into indexscans. You could write

delete from syslog_event where event_id = 1001::int8;

(or use CAST if you want to be pedantically standards-compliant).
Alternatively, consider whether event_id really needs to be bigint.
There's a clear notational advantage in plain integer.

Yes, it'd be nice if "bigintcol = 1001" acted more reasonably,
and someday we'll make it happen ... but doing so without breaking
the system's type-extensibility features is not trivial.

regards, tom lane