X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id DEB9BD6E3B for ; Wed, 7 Sep 2005 14:09:47 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 89434-05 for ; Wed, 7 Sep 2005 17:09:42 +0000 (GMT) Received: from wproxy.gmail.com (wproxy.gmail.com [64.233.184.195]) by svr1.postgresql.org (Postfix) with ESMTP id 59F70D7F82 for ; Wed, 7 Sep 2005 14:09:41 -0300 (ADT) Received: by wproxy.gmail.com with SMTP id 36so1435804wra for ; Wed, 07 Sep 2005 10:09:40 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:reply-to:to:subject:cc:in-reply-to:mime-version:content-type:references; b=ldED98D7CIRe4S+crJI2Y3HrVJEr+RDhY/dhgVD+hidCGBdMmCfAHlnpzP7lBYcLLj66ZQ4RA0AtrGMh34oggd4V6HJ2imHDlNHJRlTLpqTkUDL+yom9VQQmLfZ0aqdyZNn+dcdLBRdlEuE2uWE0wOWCy2UDsPwIpqzxceNqX3o= Received: by 10.54.104.11 with SMTP id b11mr4732489wrc; Wed, 07 Sep 2005 10:09:40 -0700 (PDT) Received: by 10.54.42.25 with HTTP; Wed, 7 Sep 2005 10:09:39 -0700 (PDT) Message-ID: Date: Wed, 7 Sep 2005 19:09:39 +0200 From: Meetesh Karia Reply-To: meetesh.karia@alumni.duke.edu To: Christian Compagnon Subject: Re: Query take 101 minutes, help, please Cc: pgsql-performance@postgresql.org In-Reply-To: Mime-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_9481_15360451.1126112979508" References: X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.17 required=5 tests=[AWL=0.059, HTML_40_50=0.086, HTML_MESSAGE=0.001, RCVD_BY_IP=0.024] X-Spam-Level: X-Archive-Number: 200509/111 X-Sequence-Number: 14419 ------=_Part_9481_15360451.1126112979508 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline PG is creating the union of January, February and March tables first and=20 that doesn't have an index on it. If you're going to do many queries using= =20 the union of those three tables, you might want to place their contents int= o=20 one table and create an index on it. Otherwise, try something like this: SELECT "Rut Cliente" FROM "Internet_Abril" WHERE "Rut Cliente" NOT IN (SELECT "Rut Cliente" FROM "Internet_Enero") AND "Rut Cliente" NOT IN (SELECT "Rut Cliente" FROM "Internet_Febrero") AND "Rut Cliente" NOT IN (SELECT "Rut Cliente" FROM "Internet_Marzo"); You could also compare the performance of that to this and choose the one= =20 that works the best: SELECT "Rut Cliente" FROM "Internet_Abril" a LEFT JOIN "Internet_Enero" e ON a."Rut Cliente" =3D e."Rut Cliente" LEFT JOIN "Internet_Febrero" f ON a."Rut Cliente" =3D f."Rut Cliente" LEFT JOIN "Internet_Marzo" m ON a."Rut Cliente" =3D m."Rut Cliente" WHERE e."Rut Cliente" IS NULL AND f."Rut Cliente" IS NULL and m."Rut=20 Cliente" IS NULL; Meetesh On 9/7/05, Christian Compagnon wrote: >=20 > Hello, >=20 > I'm a newbie in postgresql, I've installed it on a Windows XP machine > ( I can't use linux, it's a company machine ), I'm courious why this > query takes so long >=20 > SELECT "Rut Cliente" > FROM "Internet_Abril" > WHERE "Rut Cliente" NOT IN ((SELECT "Rut Cliente" FROM > "Internet_Enero") UNION (SELECT "Rut Cliente" FROM > "Internet_Febrero") UNION (SELECT "Rut Cliente" FROM > "Internet_Marzo")); >=20 > it takes about 100 minutes to complete the query. > All tables has index created ( Rut Cliente is a VarChar ), and tables > has 50.000 records each. >=20 > The explain for the query tells the following >=20 > "QUERY PLAN > Seq Scan on "Internet_Abril" (cost=3D19406.67..62126112.70 rows=3D24731= =20 > width=3D13) > Filter: (NOT (subplan)) > SubPlan > -> Materialize (cost=3D19406.67..21576.07 rows=3D136740 width=3D13) > -> Unique (cost=3D17784.23..18467.93 rows=3D136740 width=3D13) > -> Sort (cost=3D17784.23..18126.08 rows=3D136740 width=3D13) > Sort > Key: "Rut Cliente" > -> Append (cost=3D0.00..3741.80 rows=3D136740 width=3D13) > -> Subquery Scan "*SELECT* 1" (cost=3D0.00..1233.38 > rows=3D45069 width=3D13) > -> Seq Scan on "Internet_Enero" (cost=3D0.00..782.69 > rows=3D45069 width=3D13) > -> Subquery Scan "*SELECT* 2" (cost=3D0.00..1104.06 > rows=3D40353 width=3D13) > -> Seq Scan on "Internet_Febrero" (cost=3D0.00..700.53 > rows=3D40353 width=3D13) > -> Subquery Scan "*SELECT* 3" (cost=3D0.00..1404.36 > rows=3D51318 width=3D13) > -> Seq Scan on "Internet_Marzo" (cost=3D0.00..891.18 > rows=3D51318 width=3D13) >=20 > Any help will be apreciated, It's for my thesis >=20 >=20 > saludos > Christian >=20 > ---------------------------(end of broadcast)--------------------------- > TIP 1: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly > ------=_Part_9481_15360451.1126112979508 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline PG is creating the union of January, February and March tables first and that doesn't have an index on it.  If you're going to do many queries using the union of those three tables, you might want to place their contents into one table and create an index on it.

Otherwise, try something like this:

SELECT "Rut Cliente"
FROM "Internet_Abril"
WHERE "Rut Cliente"  NOT IN (SELECT "Rut Cliente" =  FROM
"Internet_Enero")
AND "Rut Cliente" NOT IN (SELECT "Rut Cliente"  FR= OM
"Internet_Febrero")
AND "Rut Cliente" NOT IN (SELECT "Rut Cliente"  FR= OM
"Internet_Marzo");

You could also compare the performance of that to this and choose the one t= hat works the best:

SELECT "Rut Cliente"
FROM "Internet_Abril" a
LEFT JOIN "Internet_Enero" e ON a."Rut Cliente" =3D e.&= quot;Rut Cliente"
LEFT JOIN "Internet_Febrero" f ON a."Rut Cliente" =3D f= ."Rut Cliente"
LEFT JOIN "Internet_Marzo" m ON a."Rut Cliente" =3D m.&= quot;Rut Cliente"
WHERE e."Rut Cliente" IS NULL AND f."Rut Cliente" IS NU= LL and m."Rut Cliente" IS NULL;

Meetesh

On 9/7/05, Christian Compagnon <ccompagnon@gmail.com> wrote:
Hello,

I'm a newbie in postgresql, I've installed it on a Windows XP= machine
( I can't use linux, it's a company machine ), I'm courious why= this
query takes so long

SELECT "Rut Cliente"
FROM = "Internet_Abril"
WHERE "Rut Cliente"  NOT IN ((SELECT "Rut Clie= nte"  FROM
"Internet_Enero") UNION (SELECT &quo= t;Rut Cliente"  FROM
"Internet_Febrero") UNION = (SELECT "Rut Cliente"  FROM
"Internet_Marzo"));

it takes about 100 minutes to comp= lete the query.
All tables has index created ( Rut Cliente is a VarChar = ), and tables
has 50.000 records each.

The explain for the query = tells the following

"QUERY PLAN
Seq Scan on "Internet_Abril" &n= bsp;(cost=3D19406.67..62126112.70 rows=3D24731 width=3D13)
  F= ilter: (NOT (subplan))
   SubPlan
    &= nbsp;  ->  Materialize  (cost=3D19406.67..21576= .07 rows=3D136740 width=3D13)
        ->  Unique=   (cost=3D17784.23..18467.93 rows=3D136740 width=3D13)
       &nbs= p;->  Sort  (cost=3D17784.23..18126.08 rows=3D136740= width=3D13)
          = ;            &n= bsp;            = ;            &n= bsp;            = ;   Sort
Key: "Rut Cliente"
      = ;    ->  Append  (cost=3D0.00..3= 741.80 rows=3D136740 width=3D13)
       &nbs= p;  ->  Subquery Scan "*SELECT* 1"  (cost=3D0.00..1233.38
rows=3D4506= 9 width=3D13)
         &nbs= p; ->  Seq Scan on "Internet_Enero"  (cost=3D0.00..782.69
rows=3D45069 = width=3D13)
           ->  Subquery Scan "*SELECT* 2"  (cost=3D0.00..1104.06
rows=3D40353 width=3D13)
&nb= sp;            ->  Seq Scan on "Internet_Febrero"  (cost=3D0.00..700.53
rows=3D4035= 3 width=3D13)
         &nbs= p; ->  Subquery Scan "*SELECT* 3"  (cost=3D0.00..1404.36
rows=3D51318 width=3D13)
&nb= sp;           ->&= nbsp; Seq Scan on "Internet_Marzo"  (cost=3D0.00..891.18
rows= =3D51318 width=3D13)

Any help will be apreciated, It's for my thesis=


saludos
Christian

---------------------------(end of = broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate       subscribe-nomail command to majordomo@postgresql.org so that y= our
       message can get through to the = mailing list cleanly

------=_Part_9481_15360451.1126112979508--