Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 5C89D2E00C3 for ; Thu, 21 Feb 2008 16:48:27 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 55565-01 for ; Thu, 21 Feb 2008 16:48:21 -0400 (AST) Received: from wx-out-0506.google.com (wx-out-0506.google.com [66.249.82.224]) by postgresql.org (Postfix) with ESMTP id 81E8D2E00B8 for ; Thu, 21 Feb 2008 16:48:21 -0400 (AST) Received: by wx-out-0506.google.com with SMTP id r21so149695wxc.28 for ; Thu, 21 Feb 2008 12:48:20 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from:to:subject:mime-version:content-type; bh=UlzYfxncm69AtnqV0816qM45uEh/+sAvE4kosMpmSnM=; b=iJLsP8hy7lTZVOjTGglpLYmUlO19DqdCJFu38wBrigif08FFaxW5wjlipbhAeyZ/Xm7JPUAsytxI3wWKrUQkezljcDxUrXEpYB2d7+BwEBhH9tJCJXB++Clf0AbG7/rArF9tPPpCm5M8MPVV/ql+vM5jMWau5jo6aql6T57ijAE= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:to:subject:mime-version:content-type; b=t11WsiufXvpGkxbYst3V5W3uXQU9FsDNLMjUXtJNbKJJYR23hPe2ViwtYRkpKCCAAj44RqIrcuYS6Udgaew/mVoeQZgXpi+rqiRAbAG9qWFVI1HMAcksDc+e8U7tiJyoTLIknrXZAZdwd0fdpzy+mcgOyMzujsIsyO4Udik9ofo= Received: by 10.140.169.4 with SMTP id r4mr7056245rve.131.1203626898988; Thu, 21 Feb 2008 12:48:18 -0800 (PST) Received: by 10.140.171.13 with HTTP; Thu, 21 Feb 2008 12:48:18 -0800 (PST) Message-ID: <8a5d3c890802211248k7d9dfbb5ndd645358a84130a7@mail.gmail.com> Date: Thu, 21 Feb 2008 17:48:18 -0300 From: "Adonias Malosso" To: pgsql-performance@postgresql.org Subject: 4s query want to run faster MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_6609_3603709.1203626898975" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.001 tagged_above=0 required=5 tests=HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 200802/270 X-Sequence-Number: 28812 ------=_Part_6609_3603709.1203626898975 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline Hi all, The following query takes about 4s to run in a 16GB ram server. Any ideas why it doesn=B4t use index for the primary keys in the join conditions? select i.inuid, count(*) as total from cte.instrumentounidade i inner join cte.pontuacao p on p.inuid =3D i.inuid inner join cte.acaoindicador ai on ai.ptoid =3D p.ptoid inner join cte.subacaoindicador si on si.aciid =3D ai.aciid where i.itrid =3D 2 and p.ptostatus =3D 'A' group by i.inuid having count(*) > 0 HashAggregate (cost=3D47905.87..47941.01 rows=3D2008 width=3D4) Filter: (count(*) > 0) -> Hash Join (cost=3D16307.79..46511.45 rows=3D185923 width=3D4) Hash Cond: (si.aciid =3D ai.aciid) -> Seq Scan on subacaoindicador si (cost=3D0.00..22812.17 rows=3D3= 68817 width=3D4) -> Hash (cost=3D16211.40..16211.40 rows=3D38556 width=3D8) -> Hash Join (cost=3D9018.20..16211.40 rows=3D38556 width=3D= 8) Hash Cond: (p.inuid =3D i.inuid) -> Hash Join (cost=3D8908.41..15419.10 rows=3D39593 width=3D8) Hash Cond: (ai.ptoid =3D p.ptoid) -> Seq Scan on acaoindicador ai (cost=3D 0.00..4200.84 rows=3D76484 width=3D8) -> Hash (cost=3D8678.33..8678.33 rows=3D92034 width=3D8) -> Seq Scan on pontuacao p (cost=3D 0.00..8678.33 rows=3D92034 width=3D8) Filter: (ptostatus =3D 'A'::bpchar) -> Hash (cost=3D104.78..104.78 rows=3D2008 width=3D4) -> Seq Scan on instrumentounidade i (cost=3D 0.00..104.78 rows=3D2008 width=3D4) Filter: (itrid =3D 2) ------=_Part_6609_3603709.1203626898975 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline Hi all,

The following query takes about 4s to run in a 16GB ram ser= ver. Any ideas why it doesn=B4t use index for the primary keys in the join = conditions?

select i.inuid, count(*) as total
from cte.instrument= ounidade i
inner join cte.pontuacao p on p.inuid =3D i.inuid
inner join cte.acaoind= icador ai on ai.ptoid =3D p.ptoid
inner join cte.subacaoindicador si on = si.aciid =3D ai.aciid
where i.itrid =3D 2 and p.ptostatus =3D 'A'= ;
group by i.inuid
having count(*) > 0

HashAggregate  (cost=3D47905.87..47941.0= 1 rows=3D2008 width=3D4)
 Filter: (count(*) > 0)
 ->&= nbsp; Hash Join  (cost=3D16307.79..46511.45 rows=3D185923 width=3D4)       Hash Cond: (si.aciid =3D ai.aciid)        ->  Seq Scan on subacaoindicad= or si  (cost=3D0.00..22812.17 rows=3D368817 width=3D4)
  =      ->  Hash  (cost=3D16211.40..16211.40 = rows=3D38556 width=3D8)
        =      ->  Hash Join  (cost=3D9018.20..16211= .40 rows=3D38556 width=3D8)
            &nb= sp;      Hash Cond: (p.inuid =3D i.inuid)
 = ;            &n= bsp;     ->  Hash Join  (cost=3D8908.41..1= 5419.10 rows=3D39593 width=3D8)
      &nbs= p;            &= nbsp;     Hash Cond: (ai.ptoid =3D p.ptoid)
 &n= bsp;            = ;           ->  S= eq Scan on acaoindicador ai  (cost=3D0.00..4200.84 rows=3D76484 width= =3D8)
            &nb= sp;            ->=   Hash  (cost=3D8678.33..8678.33 rows=3D92034 width=3D8)
 = ;            &n= bsp;            = ;     ->  Seq Scan on pontuacao p  (cost= =3D0.00..8678.33 rows=3D92034 width=3D8)
     &= nbsp;           &nbs= p;            &= nbsp;      Filter: (ptostatus =3D 'A'::bpc= har)
            &nb= sp;      ->  Hash  (cost=3D104.78..10= 4.78 rows=3D2008 width=3D4)
       &n= bsp;            = ;     ->  Seq Scan on instrumentounidade i = ; (cost=3D0.00..104.78 rows=3D2008 width=3D4)
    &n= bsp;            = ;            &n= bsp; Filter: (itrid =3D 2)
 
------=_Part_6609_3603709.1203626898975--