Received: from localhost (maia-2.hub.org [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id 7D2C59FB1D8 for ; Wed, 31 Jan 2007 10:58:11 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-new, port 10024) with ESMTP id 90169-04 for ; Wed, 31 Jan 2007 10:57:48 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4 Received: from bay0-omc2-s5.bay0.hotmail.com (bay0-omc2-s5.bay0.hotmail.com [65.54.246.141]) by postgresql.org (Postfix) with ESMTP id 849CD9FA3AC for ; Wed, 31 Jan 2007 10:58:06 -0400 (AST) Received: from hotmail.com ([65.55.137.112]) by bay0-omc2-s5.bay0.hotmail.com with Microsoft SMTPSVC(6.0.3790.2668); Wed, 31 Jan 2007 06:58:04 -0800 Received: from mail pickup service by hotmail.com with Microsoft SMTPSVC; Wed, 31 Jan 2007 06:58:04 -0800 Message-ID: Received: from 65.55.137.123 by by132fd.bay132.hotmail.msn.com with HTTP; Wed, 31 Jan 2007 14:58:02 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 08:58:02 -0600 Mime-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1; format=flowed X-OriginalArrivalTime: 31 Jan 2007 14:58:04.0418 (UTC) FILETIME=[35B22E20:01C74548] X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200701/448 X-Sequence-Number: 23049 >How many rows were delete last time you ran the query? I never delete any rows, the tables was inserted with copy command, then I create index and I need to delete these records on ceroriesgo.salarios to create the foreign key restriction on it. > >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) > >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 _________________________________________________________________ Las mejores tiendas, los precios mas bajos, entregas en todo el mundo, YupiMSN Compras: http://latam.msn.com/compras/