pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feed From: Adonias Malosso <malosso@gmail.com>
To: Scott Marlowe <scott.marlowe@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: 4s query want to run faster
Date: Thu, 21 Feb 2008 18:05:52 -0300
Message-ID: <8a5d3c890802211305v46c44e2awc5811f496caf0191@mail.gmail.com> (raw )
In-Reply-To: <dcc563d10802211258w65285c0bl9f9da18b9ed0fa92@mail.gmail.com >
References: <8a5d3c890802211248k7d9dfbb5ndd645358a84130a7@mail.gmail.com >
<dcc563d10802211258w65285c0bl9f9da18b9ed0fa92@mail.gmail.com >
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?
>
view thread (12+ messages) latest in thread
Message-ID: <8a5d3c890802211305v46c44e2awc5811f496caf0191@mail.gmail.com>
Permalink: ../8a5d3c890802211305v46c44e2awc5811f496caf0191@mail.gmail.com/
Also on: postgresql.org/message-id/8a5d3c890802211305v46c44e2awc5811f496caf0191@mail.gmail.com
copy link · copy postgr.es
reply Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-performance@postgresql.org
Cc: malosso@gmail.com, scott.marlowe@gmail.com
Subject: Re: 4s query want to run faster
In-Reply-To: <8a5d3c890802211305v46c44e2awc5811f496caf0191@mail.gmail.com>
References: <8a5d3c890802211248k7d9dfbb5ndd645358a84130a7@mail.gmail.com> <dcc563d10802211258w65285c0bl9f9da18b9ed0fa92@mail.gmail.com> <8a5d3c890802211305v46c44e2awc5811f496caf0191@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox