Re: 4s query want to run faster

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(dot)marlowe(at)gmail(dot)com>
wrote:

> On Thu, Feb 21, 2008 at 2:48 PM, Adonias Malosso <malosso(at)gmail(dot)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?
>