Обсуждение: 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
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
>

"Clay Luther" <claycle@cisco.com> writes:
> Sort_mem is 32K.

Try more (like 32M).  Particularly in 7.4, you can really hobble a query
by starving it for sort memory (since that also determines whether
hashtable techniques will be tried).

            regards, tom lane