Re: 50K record DELETE Begins, 100% CPU, Never Completes 1 hour later

Actually, I tried the NOT IN query as well, as well as writing and caching the primary keys I wanted to be sure to delete for deletion later.

Both of these options produce the same results, which lead me to believe there's a concurrancy issue somewhere, perhaps. I had combed our code for uncommitted writes, but found nothing outstanding.

I am testing now with autocommit=true.

Sort_mem is 32K.

cwl

> -----Original Message-----
> From: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
> Sent: Thursday, September 11, 2003 12:32 AM
> To: Clay Luther
> Cc: Pgsql-General (E-mail)
> Subject: Re: [GENERAL] 50K record DELETE Begins, 100% CPU, Never
> Completes 1 hour later
>
>
> "Clay Luther" <claycle(at)cisco(dot)com> writes:
> > ccm=# explain delete from numplan where pkid in (select
> numplan.pkid from numplan left outer join pilothuntgroup on
> numplan.pkid=pilothuntgroup.fknumplan left outer join
> devicenumplanmap on numplan.pkid = devicenumplanmap.fknumplan
> where numplan.tkpatternusage=2 and pilothuntgroup.fknumplan
> is null and devicenumplanmap.fknumplan is null);
>
> The left join/is null thingies look like a workaround for our pre-7.4
> lack of performance with NOT IN queries. Have you tried expressing
> this more straightforwardly with NOT IN?
>
> Also, what sort_mem setting are you using?
>
> regards, tom lane
>