Received: from localhost (unknown [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 74B0B2E005B for ; Thu, 21 Feb 2008 17:06:00 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 93076-07 for ; Thu, 21 Feb 2008 17:05:52 -0400 (AST) Received: from rv-out-0910.google.com (rv-out-0910.google.com [209.85.198.188]) by postgresql.org (Postfix) with ESMTP id 0FB8B2E00C1 for ; Thu, 21 Feb 2008 17:05:54 -0400 (AST) Received: by rv-out-0910.google.com with SMTP id g13so121292rvb.43 for ; Thu, 21 Feb 2008 13:05:52 -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:cc:in-reply-to:mime-version:content-type:references; bh=hmis8VKHvaZO3QU+QkCDUixd9vcdMqievvI/g72Yb6I=; b=L6iFRPaUJKEVjtCshge81rwUtupLQKDGp1/jaw2xS4T8FhDe+A0nZd704SfY6JGQccWmkccaIOWkZFuJPBenUxrOUO6RfpTU5CmnSzcirPJBu9Puqz8VrIm/92RRbvYDKPBt0BD+rXtDi4d2yaWUsdqnlb1F8pmwyLf9otvCsB8= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=ZIp8MQWtFF/+Y/laDYAfrbHInS2W4WeJAE6QLNsnwAQA961FkphuD37EnxOagf2lIu51sOht42oAzCpjB2PcrEWMSdsEG44ViF9x94sTBEmojdoTl2GHkLtz98NduNf8FuLPjtSnZ7CC/tgjYouADCHDmpND6yvbOBXIxeSZsmI= Received: by 10.141.198.9 with SMTP id a9mr3808834rvq.219.1203627952314; Thu, 21 Feb 2008 13:05:52 -0800 (PST) Received: by 10.140.171.13 with HTTP; Thu, 21 Feb 2008 13:05:52 -0800 (PST) Message-ID: <8a5d3c890802211305v46c44e2awc5811f496caf0191@mail.gmail.com> Date: Thu, 21 Feb 2008 18:05:52 -0300 From: "Adonias Malosso" To: "Scott Marlowe" Subject: Re: 4s query want to run faster Cc: pgsql-performance@postgresql.org In-Reply-To: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_6696_31911717.1203627952304" References: <8a5d3c890802211248k7d9dfbb5ndd645358a84130a7@mail.gmail.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200802/272 X-Sequence-Number: 28814 ------=_Part_6696_31911717.1203627952304 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline HashAggregate (cost=3D47818.40..47853.12 rows=3D1984 width=3D4) (actual ti= me=3D 5738.879..5743.390 rows=3D1715 loops=3D1) Filter: (count(*) > 0) -> Hash Join (cost=3D16255.99..46439.06 rows=3D183912 width=3D4) (actual= time=3D 1887.974..5154.207 rows=3D241693 loops=3D1) Hash Cond: (si.aciid =3D ai.aciid) -> Seq Scan on subacaoindicador si (cost=3D0.00..22811.98 rows=3D3= 68798 width=3D4) (actual time=3D0.108..1551.816 rows=3D368798 loops=3D1) -> Hash (cost=3D16160.64..16160.64 rows=3D38141 width=3D8) (actual= time=3D 1887.790..1887.790 rows=3D52236 loops=3D1) -> Hash Join (cost=3D9015.31..16160.64 rows=3D38141 width=3D= 8) (actual time=3D980.058..1773.530 rows=3D52236 loops=3D1) Hash Cond: (p.inuid =3D i.inuid) -> Hash Join (cost=3D8905.89..15376.11 rows=3D39160 width=3D8) (actual time=3D967.116..1568.028 rows=3D54225 loops=3D1) Hash Cond: (ai.ptoid =3D p.ptoid) -> Seq Scan on acaoindicador ai (cost=3D 0.00..4200.84 rows=3D76484 width=3D8) (actual time=3D0.080..259.412 rows=3D= 76484 loops=3D1) -> Hash (cost=3D8678.33..8678.33 rows=3D91026 width=3D8) (actual time=3D966.841..966.841 rows=3D92405 loops=3D1) -> Seq Scan on pontuacao p (cost=3D 0.00..8678.33 rows=3D91026 width=3D8) (actual time=3D0.087..746.528 rows=3D= 92405 loops=3D1) Filter: (ptostatus =3D 'A'::bpchar) -> Hash (cost=3D104.46..104.46 rows=3D1984 width=3D4) = (actual time=3D12.913..12.913 rows=3D1983 loops=3D1) -> Seq Scan on instrumentounidade i (cost=3D 0.00..104.46 rows=3D1984 width=3D4) (actual time=3D0.091..8.879 rows=3D1983= loops=3D1) Filter: (itrid =3D 2) Total runtime: 5746.415 ms On Thu, Feb 21, 2008 at 5:58 PM, Scott Marlowe wrote: > On Thu, Feb 21, 2008 at 2:48 PM, Adonias Malosso > wrote: > > 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 > > What does explain analyze say about that query? > ------=_Part_6696_31911717.1203627952304 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline HashAggregate  (cost=3D47818.40..47853.12 rows=3D198= 4 width=3D4) (actual time=3D5738.879..5743.390 rows=3D1715 loops=3D1)
&= nbsp;Filter: (count(*) > 0)
 ->  Hash Join  (cost= =3D16255.99..46439.06 rows=3D183912 width=3D4) (actual time=3D1887.974..515= 4.207 rows=3D241693 loops=3D1)
       Hash Cond: (si.aciid =3D ai.aciid)
  &= nbsp;    ->  Seq Scan on subacaoindicador si  (cost= =3D0.00..22811.98 rows=3D368798 width=3D4) (actual time=3D0.108..1551.816 r= ows=3D368798 loops=3D1)
       ->  Hash &nb= sp;(cost=3D16160.64..16160.64 rows=3D38141 width=3D8) (actual time=3D1887.7= 90..1887.790 rows=3D52236 loops=3D1)
             ->  Hash Join &nbs= p;(cost=3D9015.31..16160.64 rows=3D38141 width=3D8) (actual time=3D980.058.= .1773.530 rows=3D52236 loops=3D1)
          &n= bsp;        Hash Cond: (p.inuid =3D i.inuid)
 =                  ->  = Hash Join  (cost=3D8905.89..15376.11 rows=3D39160 width=3D8) (actual t= ime=3D967.116..1568.028 rows=3D54225 loops=3D1)
                    &nbs= p;    Hash Cond: (ai.ptoid =3D p.ptoid)
      =                    -> =  Seq Scan on acaoindicador ai  (cost=3D0.00..4200.84 rows=3D76484= width=3D8) (actual time=3D0.080..259.412 rows=3D76484 loops=3D1)
 = ;                     &nb= sp;  ->  Hash  (cost=3D8678.33..8678.33 rows=3D91026 widt= h=3D8) (actual time=3D966.841..966.841 rows=3D92405 loops=3D1)
                    &nbs= p;          ->  Seq Scan on pontuacao p &n= bsp;(cost=3D0.00..8678.33 rows=3D91026 width=3D8) (actual time=3D0.087..746= .528 rows=3D92405 loops=3D1)
            =                      = ;    Filter: (ptostatus =3D 'A'::bpchar)
                   ->=  Hash  (cost=3D104.46..104.46 rows=3D1984 width=3D4) (actual tim= e=3D12.913..12.913 rows=3D1983 loops=3D1)
        &= nbsp;                ->  Se= q Scan on instrumentounidade i  (cost=3D0.00..104.46 rows=3D1984 width= =3D4) (actual time=3D0.091..8.879 rows=3D1983 loops=3D1)
                    &nbs= p;          Filter: (itrid =3D 2)
Total runtime= : 5746.415 ms


On Thu, Feb 21, 2008 at 5:58 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
On Thu, Feb 21, 2008 at 2:48 PM, Adonias Malosso <malosso@gmail.com> wrote:
> Hi all,
>
> The following query takes about 4s to run in a 16GB ram server. Any id= eas
> 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

What does explain analyze say about that query?

------=_Part_6696_31911717.1203627952304--