Re: Slow deletes

On Mon, Aug 12, 2002 at 10:34:23PM -0400, Tom Lane wrote:
> 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.

Actually, an easier way to do it is by saying:

delete from syslog_event where event_id = '1001'

which works fine, since the quoted value is listed as 'unknown' rather than
'int4'

I considered a patch to make the parser treat all numbers as unknown, but
that would break any code that relies on numbers-are-int4 (think function
and operator type resolution).

Sometimes you can't win.
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> There are 10 kinds of people in the world, those that can do binary
> arithmetic and those that can't.