Received: from localhost (maia-5.hub.org [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id 785B79FA48D for ; Wed, 31 Jan 2007 11:28:15 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.182]) (amavisd-new, port 10024) with ESMTP id 12178-10 for ; Wed, 31 Jan 2007 11:28:02 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4 Received: from bay0-omc1-s1.bay0.hotmail.com (bay0-omc1-s1.bay0.hotmail.com [65.54.246.73]) by postgresql.org (Postfix) with ESMTP id 1620B9FA306 for ; Wed, 31 Jan 2007 11:28:07 -0400 (AST) Received: from hotmail.com ([65.55.137.85]) by bay0-omc1-s1.bay0.hotmail.com with Microsoft SMTPSVC(6.0.3790.2668); Wed, 31 Jan 2007 07:28:06 -0800 Received: from mail pickup service by hotmail.com with Microsoft SMTPSVC; Wed, 31 Jan 2007 07:28:05 -0800 Message-ID: Received: from 65.55.137.123 by by132fd.bay132.hotmail.msn.com with HTTP; Wed, 31 Jan 2007 15:28:05 GMT X-Originating-IP: [200.91.71.251] X-Originating-Email: [sidarlopez@hotmail.com] X-Sender: sidarlopez@hotmail.com In-Reply-To: <45C0A88B.2060409@blackducksoftware.com> From: =?iso-8859-1?B?U2lkYXIgTPNwZXogQ3J1eg==?= To: pgsql-performance@postgresql.org Cc: tallen@blackducksoftware.com Subject: Re: Very slow queries Date: Wed, 31 Jan 2007 09:28:05 -0600 Mime-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1; format=flowed X-OriginalArrivalTime: 31 Jan 2007 15:28:05.0908 (UTC) FILETIME=[6777BD40:01C7454C] X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200701/450 X-Sequence-Number: 23051 >From: Ted Allen >To: Sidar López Cruz >CC: pgsql-performance@postgresql.org, chad.wagner@gmail.com >Subject: Re: [PERFORM] Very slow queries >Date: Wed, 31 Jan 2007 09:32:43 -0500 > >How many rows were delete last time you ran the query? > >Chad's query looks good but here is another variation that may help. > >Delete From ceroriesgo.salarios Where numero_patrono In (Select >ceroriesgo.salarios.numero_patrono From ceroriesgo.salarios Left Join >ceroriesgo.patronos Using (numero_patrono) Where >ceroriesgo.patronos.numero_patrono Is Null) > Executing these query take: Query returned successfully: 290 rows affected, 2542387 ms execution time. I think that's too many time >Hope that Helps, >Ted > >Sidar López Cruz wrote: >> >> >> >>>From: "Chad Wagner" >>>To: "Sidar López Cruz" >>>CC: pgsql-performance@postgresql.org >>>Subject: Re: [PERFORM] Very slow queries >>>Date: Tue, 30 Jan 2007 17:37:17 -0500 >>> >>>On 1/30/07, Sidar López Cruz wrote: >>>> >>>>query: Delete From ceroriesgo.salarios Where numero_patrono Not In >>>>(Select >>>>numero_patrono From ceroriesgo.patronos) >>>> >>>>Seq Scan on salarios (cost=51021.78..298803854359.95 rows=14240077 >>>>width=6) >>>> Filter: (NOT (subplan)) >>>> SubPlan >>>> -> Materialize (cost=51021.78..69422.58 rows=1032980 width=25) >>>> -> Seq Scan on patronos (cost=0.00..41917.80 rows=1032980 >>>>width=25) >>>> >>> >>>How many rows exist in salarios, but not in patronos? How many rows are >>>there in salarios? >> >>Rows: >>Patronos: 1032980 >>Salarios: 28480200 >> >>> >>>What does the explain look like for: >>> >>>delete >>>from ceroriesgo.salarios s >>>where not exists (select 1 >>> from ceroriesgo.patronos >>> where numero_patrono = s.numero_patrono); >>> >>>Also, is this not a case for a foreign key with a cascade delete? >> >>No, this is not cascade delete case because I need to delete from salarios >>not from patronos. >> >> >>>http://www.postgresql.org/docs/8.2/static/ddl-constraints.html >>> >>> >>>-- >>>Chad >>>http://www.postgresqlforums.com/ >> >>_________________________________________________________________ >>Charla con tus amigos en línea mediante MSN Messenger: >>http://messenger.latam.msn.com/ >> >> >>---------------------------(end of broadcast)--------------------------- >>TIP 6: explain analyze is your friend >> > > >-- > >*Edward Allen* >Software Engineer >Black Duck Software, Inc. > >tallen@blackducksoftware.com >T +1.781.891.5100 x133 >F +1.781.891.5145 >http://www.blackducksoftware.com > > >---------------------------(end of broadcast)--------------------------- >TIP 3: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faq _________________________________________________________________ MSN Amor: busca tu ˝ naranja http://latam.msn.com/amor/