Postgres Pro
Downloads
Home   >   mailing lists

Re: 4s query want to run faster - Mailing list pgsql-performance

From Adonias Malosso
Subject Re: 4s query want to run faster
Date
Msg-id 8a5d3c890802211305v46c44e2awc5811f496caf0191@mail.gmail.com
Whole thread Raw
In response to Re: 4s query want to run faster  ("Scott Marlowe" <scott.marlowe@gmail.com>)
Responses Re: 4s query want to run faster  ("Scott Marlowe" <scott.marlowe@gmail.com>)
List pgsql-performance
Tree view
HashAggregate  (cost=47818.40..47853.12 rows=1984 width=4) (actual time=5738.879..5743.390 rows=1715 loops=1)
 Filter: (count(*) > 0)
 ->  Hash Join  (cost=16255.99..46439.06 rows=183912 width=4) (actual time=1887.974..5154.207 rows=241693 loops=1)
       Hash Cond: (si.aciid = ai.aciid)
       ->  Seq Scan on subacaoindicador si  (cost=0.00..22811.98 rows=368798 width=4) (actual time=0.108..1551.816 rows=368798 loops=1)
       ->  Hash  (cost=16160.64..16160.64 rows=38141 width=8) (actual time=1887.790..1887.790 rows=52236 loops=1)
             ->  Hash Join  (cost=9015.31..16160.64 rows=38141 width=8) (actual time=980.058..1773.530 rows=52236 loops=1)
                   Hash Cond: (p.inuid = i.inuid)
                   ->  Hash Join  (cost=8905.89..15376.11 rows=39160 width=8) (actual time=967.116..1568.028 rows=54225 loops=1)
                         Hash Cond: (ai.ptoid = p.ptoid)
                         ->  Seq Scan on acaoindicador ai  (cost=0.00..4200.84 rows=76484 width=8) (actual time=0.080..259.412 rows=76484 loops=1)
                         ->  Hash  (cost=8678.33..8678.33 rows=91026 width=8) (actual time=966.841..966.841 rows=92405 loops=1)
                               ->  Seq Scan on pontuacao p  (cost=0.00..8678.33 rows=91026 width=8) (actual time=0.087..746.528 rows=92405 loops=1)
                                     Filter: (ptostatus = 'A'::bpchar)
                   ->  Hash  (cost=104.46..104.46 rows=1984 width=4) (actual time=12.913..12.913 rows=1983 loops=1)
                         ->  Seq Scan on instrumentounidade i  (cost=0.00..104.46 rows=1984 width=4) (actual time=0.091..8.879 rows=1983 loops=1)
                               Filter: (itrid = 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 ideas
> why it doesn´t 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 = i.inuid
> inner join cte.acaoindicador ai on ai.ptoid = p.ptoid
> inner join cte.subacaoindicador si on si.aciid = ai.aciid
> where i.itrid = 2 and p.ptostatus = 'A'
> group by i.inuid
>  having count(*) > 0

What does explain analyze say about that query?

pgsql-performance by date:

Previous
From: "Scott Marlowe"
Date:
Subject: Re: 4s query want to run faster
Next
From: "Claus Guttesen"
Date:
Subject: Re: 4s query want to run faster