Actually, I tried the NOT IN query as well, as well as writing and caching the primary keys I wanted to be sure to
deletefor 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@sss.pgh.pa.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@cisco.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
>