Edmund Dengler <edmundd@eSentire.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
