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@xxxxxxxxx> wrote:
On Thu, Feb 21, 2008 at 2:48 PM, Adonias Malosso <malosso@xxxxxxxxx> wrote:What does explain analyze say about that query?
> 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
