Skip site navigation (1) Skip section navigation (2)

Re: 4s query want to run faster

From: "Adonias Malosso" <malosso(at)gmail(dot)com>
To: "Scott Marlowe" <scott(dot)marlowe(at)gmail(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: 4s query want to run faster
Date: 2008-02-21 21:05:52
Message-ID: 8a5d3c890802211305v46c44e2awc5811f496caf0191@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
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?
>

In response to

Responses

pgsql-performance by date

Next:From: Claus GuttesenDate: 2008-02-21 21:10:24
Subject: Re: 4s query want to run faster
Previous:From: Scott MarloweDate: 2008-02-21 20:58:04
Subject: Re: 4s query want to run faster

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group