Received: from localhost (maia-5.hub.org [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id B2A0A9FB410 for ; Tue, 30 Jan 2007 18:37:28 -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 93909-07 for ; Tue, 30 Jan 2007 18:37:17 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4 Received: from nf-out-0910.google.com (nf-out-0910.google.com [64.233.182.189]) by postgresql.org (Postfix) with ESMTP id 350959FA0E7 for ; Tue, 30 Jan 2007 18:37:20 -0400 (AST) Received: by nf-out-0910.google.com with SMTP id x4so358743nfb for ; Tue, 30 Jan 2007 14:37:18 -0800 (PST) DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=NH/gbDlckv/fvlvul0rHbD+x+S6U3x/HbhfGLXbR1DVfrxDE8WztBOJUCZSbqjoOanrvLKvD5FSo1JppwDcetW4uLNYQWcZXkFwJgNpFyvnNQi4jbXa4kpn+1AZK8NzaEpJ50y0kJeeh6VSY2CaJt+lXw1bMMylXyBJ7i6iO+1w= Received: by 10.49.34.17 with SMTP id m17mr1535951nfj.1170196637989; Tue, 30 Jan 2007 14:37:17 -0800 (PST) Received: by 10.48.210.4 with HTTP; Tue, 30 Jan 2007 14:37:17 -0800 (PST) Message-ID: <81961ff50701301437t60c5370bvdf165215cbeaeb25@mail.gmail.com> Date: Tue, 30 Jan 2007 17:37:17 -0500 From: "Chad Wagner" To: "=?ISO-8859-1?Q?Sidar_L=F3pez_Cruz?=" Subject: Re: Very slow queries Cc: pgsql-performance@postgresql.org In-Reply-To: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_168511_31511906.1170196637537" References: X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200701/438 X-Sequence-Number: 23039 ------=_Part_168511_31511906.1170196637537 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: quoted-printable Content-Disposition: inline On 1/30/07, Sidar L=F3pez Cruz wrote: > > query: Delete From ceroriesgo.salarios Where numero_patrono Not In (Selec= t > numero_patrono From ceroriesgo.patronos) > > Seq Scan on salarios (cost=3D51021.78..298803854359.95 rows=3D14240077 > width=3D6) > Filter: (NOT (subplan)) > SubPlan > -> Materialize (cost=3D51021.78..69422.58 rows=3D1032980 width=3D25= ) > -> Seq Scan on patronos (cost=3D0.00..41917.80 rows=3D1032980 > width=3D25) > How many rows exist in salarios, but not in patronos? How many rows are there in salarios? What does the explain look like for: delete from ceroriesgo.salarios s where not exists (select 1 from ceroriesgo.patronos where numero_patrono =3D s.numero_patrono); Also, is this not a case for a foreign key with a cascade delete? http://www.postgresql.org/docs/8.2/static/ddl-constraints.html --=20 Chad http://www.postgresqlforums.com/ ------=_Part_168511_31511906.1170196637537 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline On 1/30/07, Sidar L=F3pez Cruz <sidarlopez@hotmail.com> wrote:
query: Delete From ceroriesgo.salarios Where numero_patrono Not In (Select<= br>numero_patrono From ceroriesgo.patronos)

Seq Scan on salarios&nbs= p; (cost=3D51021.78..298803854359.95 rows=3D14240077 width=3D6)
&nb= sp; Filter: (NOT (subplan))
  SubPlan
    ->  Materia= lize  (cost=3D51021.78..69422.58 rows=3D1032980 width=3D25)
&n= bsp;         ->  = Seq Scan on patronos  (cost=3D0.00..41917.80 rows=3D1032980
wi= dth=3D25)

How many rows exist in salarios, but no= t in patronos?  How many rows are there in salarios?

What does the explain look like for:

delete
 from ce= roriesgo.salarios s
where not exists (select 1
   &nbs= p;            &= nbsp;           from cero= riesgo.patronos
         &n= bsp;            = ;    where numero_patrono =3D=20 s.numero_patrono);

Also, is this not a case for a foreign key with a= cascade delete?

http://www.postgresql.org/docs/8.2/static/ddl-const= raints.html


--
Chad
http://www.postgresqlforums.com/ ------=_Part_168511_31511906.1170196637537--