Received: from localhost (maia-2.hub.org [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id 76AC69FA4A0 for ; Wed, 31 Jan 2007 10:32:44 -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 80412-04 for ; Wed, 31 Jan 2007 10:32:19 -0400 (AST) X-Greylist: delayed 17:18:00.801441 by SQLgrey-1.7.4 Received: from smtp.blackducksoftware.com (nat.blackducksoftware.com [216.195.218.91]) by postgresql.org (Postfix) with ESMTP id 6AFF59FA3AC for ; Wed, 31 Jan 2007 10:32:36 -0400 (AST) Received: from localhost (gyro.blackducksoftware.com [127.0.0.1]) by smtp.blackducksoftware.com (Postfix) with ESMTP id 542395C4243; Wed, 31 Jan 2007 09:33:14 -0500 (EST) Received: from smtp.blackducksoftware.com ([127.0.0.1]) by localhost (gyro.blackducksoftware.com [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 17247-06; Wed, 31 Jan 2007 09:33:13 -0500 (EST) Received: from [10.12.14.31] (tallen.blackducksoftware.com [10.12.14.31]) by smtp.blackducksoftware.com (Postfix) with ESMTP id 704795C4089; Wed, 31 Jan 2007 09:33:13 -0500 (EST) Message-ID: <45C0A88B.2060409@blackducksoftware.com> Date: Wed, 31 Jan 2007 09:32:43 -0500 From: Ted Allen User-Agent: Thunderbird 1.5.0.9 (X11/20070104) MIME-Version: 1.0 To: =?ISO-8859-1?Q?Sidar_L=F3pez_Cruz?= CC: pgsql-performance@postgresql.org, chad.wagner@gmail.com Subject: Re: Very slow queries References: In-Reply-To: Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 8bit X-Virus-Scanned: Debian amavisd-new at blackducksoftware.com X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200701/446 X-Sequence-Number: 23047 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) 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