pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feed4s query want to run faster
12+ messages / 5 participants
[nested] [flat]
* 4s query want to run faster
@ 2008-02-21 20:48 Adonias Malosso <malosso@gmail.com>
2008-02-21 20:58 ` Re: 4s query want to run faster Scott Marlowe <scott.marlowe@gmail.com>
2008-02-21 21:10 ` Re: 4s query want to run faster Claus Guttesen <kometen@gmail.com>
0 siblings, 2 replies; 12+ messages in thread
From: Adonias Malosso @ 2008-02-21 20:48 UTC (permalink / raw)
To: pgsql-performance
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
HashAggregate (cost=47905.87..47941.01 rows=2008 width=4)
Filter: (count(*) > 0)
-> Hash Join (cost=16307.79..46511.45 rows=185923 width=4)
Hash Cond: (si.aciid = ai.aciid)
-> Seq Scan on subacaoindicador si (cost=0.00..22812.17 rows=368817
width=4)
-> Hash (cost=16211.40..16211.40 rows=38556 width=8)
-> Hash Join (cost=9018.20..16211.40 rows=38556 width=8)
Hash Cond: (p.inuid = i.inuid)
-> Hash Join (cost=8908.41..15419.10 rows=39593
width=8)
Hash Cond: (ai.ptoid = p.ptoid)
-> Seq Scan on acaoindicador ai (cost=
0.00..4200.84 rows=76484 width=8)
-> Hash (cost=8678.33..8678.33 rows=92034
width=8)
-> Seq Scan on pontuacao p (cost=
0.00..8678.33 rows=92034 width=8)
Filter: (ptostatus = 'A'::bpchar)
-> Hash (cost=104.78..104.78 rows=2008 width=4)
-> Seq Scan on instrumentounidade i (cost=
0.00..104.78 rows=2008 width=4)
Filter: (itrid = 2)
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: 4s query want to run faster
2008-02-21 20:48 4s query want to run faster Adonias Malosso <malosso@gmail.com>
@ 2008-02-21 20:58 ` Scott Marlowe <scott.marlowe@gmail.com>
2008-02-21 21:05 ` Re: 4s query want to run faster Adonias Malosso <malosso@gmail.com>
1 sibling, 1 reply; 12+ messages in thread
From: Scott Marlowe @ 2008-02-21 20:58 UTC (permalink / raw)
To: Adonias Malosso <malosso@gmail.com>; +Cc: pgsql-performance
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?
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: 4s query want to run faster
2008-02-21 20:48 4s query want to run faster Adonias Malosso <malosso@gmail.com>
2008-02-21 20:58 ` Re: 4s query want to run faster Scott Marlowe <scott.marlowe@gmail.com>
@ 2008-02-21 21:05 ` Adonias Malosso <malosso@gmail.com>
2008-02-21 21:25 ` Re: 4s query want to run faster Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 1 reply; 12+ messages in thread
From: Adonias Malosso @ 2008-02-21 21:05 UTC (permalink / raw)
To: Scott Marlowe <scott.marlowe@gmail.com>; +Cc: 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.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?
>
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: 4s query want to run faster
2008-02-21 20:48 4s query want to run faster Adonias Malosso <malosso@gmail.com>
2008-02-21 20:58 ` Re: 4s query want to run faster Scott Marlowe <scott.marlowe@gmail.com>
2008-02-21 21:05 ` Re: 4s query want to run faster Adonias Malosso <malosso@gmail.com>
@ 2008-02-21 21:25 ` Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 0 replies; 12+ messages in thread
From: Scott Marlowe @ 2008-02-21 21:25 UTC (permalink / raw)
To: Adonias Malosso <malosso@gmail.com>; +Cc: pgsql-performance
Well, all the row counts in expected and actual are pretty close. I'm
guessing it's as optimized as it's likely to get. you could try
mucking about with random_page_cost to force index usage, but indexes
are not always a win in pgsql, hence the seq scans etc... If the
number of rows returned represents a large percentage of the total
number of rows in the table, then a seq scan is generally a win. Note
that most all the time being spent in this query is on the Hash Join,
not on the seq scans.
Also, you should really update to 8.2.6 the latest 8.2 version. Check
the release notes for the bugs that were fixed between 8.2.1 and 8.2.6
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: 4s query want to run faster
2008-02-21 20:48 4s query want to run faster Adonias Malosso <malosso@gmail.com>
@ 2008-02-21 21:10 ` Claus Guttesen <kometen@gmail.com>
2008-02-21 21:11 ` Re: 4s query want to run faster Adonias Malosso <malosso@gmail.com>
1 sibling, 1 reply; 12+ messages in thread
From: Claus Guttesen @ 2008-02-21 21:10 UTC (permalink / raw)
To: Adonias Malosso <malosso@gmail.com>; +Cc: pgsql-performance
> 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?
Maby random_page_cost is set too high? What version are you using?
--
regards
Claus
When lenity and cruelty play for a kingdom,
the gentlest gamester is the soonest winner.
Shakespeare
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: 4s query want to run faster
2008-02-21 20:48 4s query want to run faster Adonias Malosso <malosso@gmail.com>
2008-02-21 21:10 ` Re: 4s query want to run faster Claus Guttesen <kometen@gmail.com>
@ 2008-02-21 21:11 ` Adonias Malosso <malosso@gmail.com>
2008-02-21 21:16 ` Re: 4s query want to run faster Claus Guttesen <kometen@gmail.com>
0 siblings, 1 reply; 12+ messages in thread
From: Adonias Malosso @ 2008-02-21 21:11 UTC (permalink / raw)
To: Claus Guttesen <kometen@gmail.com>; +Cc: pgsql-performance
On Thu, Feb 21, 2008 at 6:10 PM, Claus Guttesen <kometen@gmail.com> wrote:
> > 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?
>
> Maby random_page_cost is set too high? What version are you using?
Postgresql v. 8.2.1
>
> --
> regards
> Claus
>
> When lenity and cruelty play for a kingdom,
> the gentlest gamester is the soonest winner.
>
> Shakespeare
>
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: 4s query want to run faster
2008-02-21 20:48 4s query want to run faster Adonias Malosso <malosso@gmail.com>
2008-02-21 21:10 ` Re: 4s query want to run faster Claus Guttesen <kometen@gmail.com>
2008-02-21 21:11 ` Re: 4s query want to run faster Adonias Malosso <malosso@gmail.com>
@ 2008-02-21 21:16 ` Claus Guttesen <kometen@gmail.com>
2008-02-21 21:23 ` Re: 4s query want to run faster Adonias Malosso <malosso@gmail.com>
0 siblings, 1 reply; 12+ messages in thread
From: Claus Guttesen @ 2008-02-21 21:16 UTC (permalink / raw)
To: Adonias Malosso <malosso@gmail.com>; +Cc: pgsql-performance
> > > why it doesn´t use index for the primary keys in the join conditions?
> >
> > Maby random_page_cost is set too high? What version are you using?
>
> Postgresql v. 8.2.1
You can try to lower this value. The default (in 8.3) is 4.
--
regards
Claus
When lenity and cruelty play for a kingdom,
the gentlest gamester is the soonest winner.
Shakespeare
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: 4s query want to run faster
2008-02-21 20:48 4s query want to run faster Adonias Malosso <malosso@gmail.com>
2008-02-21 21:10 ` Re: 4s query want to run faster Claus Guttesen <kometen@gmail.com>
2008-02-21 21:11 ` Re: 4s query want to run faster Adonias Malosso <malosso@gmail.com>
2008-02-21 21:16 ` Re: 4s query want to run faster Claus Guttesen <kometen@gmail.com>
@ 2008-02-21 21:23 ` Adonias Malosso <malosso@gmail.com>
2008-02-21 22:59 ` Re: 4s query want to run faster Mark Kirkwood <markir@paradise.net.nz>
0 siblings, 1 reply; 12+ messages in thread
From: Adonias Malosso @ 2008-02-21 21:23 UTC (permalink / raw)
To: Claus Guttesen <kometen@gmail.com>; +Cc: pgsql-performance
Set random_page_cost = 2 solved the problem. thanks
On Thu, Feb 21, 2008 at 6:16 PM, Claus Guttesen <kometen@gmail.com> wrote:
> > > > why it doesn´t use index for the primary keys in the join
> conditions?
> > >
> > > Maby random_page_cost is set too high? What version are you using?
> >
> > Postgresql v. 8.2.1
>
> You can try to lower this value. The default (in 8.3) is 4.
>
> --
> regards
> Claus
>
> When lenity and cruelty play for a kingdom,
> the gentlest gamester is the soonest winner.
>
> Shakespeare
>
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: 4s query want to run faster
2008-02-21 20:48 4s query want to run faster Adonias Malosso <malosso@gmail.com>
2008-02-21 21:10 ` Re: 4s query want to run faster Claus Guttesen <kometen@gmail.com>
2008-02-21 21:11 ` Re: 4s query want to run faster Adonias Malosso <malosso@gmail.com>
2008-02-21 21:16 ` Re: 4s query want to run faster Claus Guttesen <kometen@gmail.com>
2008-02-21 21:23 ` Re: 4s query want to run faster Adonias Malosso <malosso@gmail.com>
@ 2008-02-21 22:59 ` Mark Kirkwood <markir@paradise.net.nz>
2008-02-21 23:16 ` Re: 4s query want to run faster Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 1 reply; 12+ messages in thread
From: Mark Kirkwood @ 2008-02-21 22:59 UTC (permalink / raw)
To: Adonias Malosso <malosso@gmail.com>; +Cc: Claus Guttesen <kometen@gmail.com>; pgsql-performance
The other parameter you might want to look at is effective_cache_size -
increasing it will encourage index use. On a machine with 16GB the
default is probably too small (there are various recommendations about
how to set this ISTR either Scott M or Greg Smith had a page somewhere
that covered this quite well - guys?).
Obviously, decreasing random_page_cost fixed this query for you, but if
find yourself needing to tweak it again for other queries, then look at
changing effective_cache_size.
Cheers
Mark
Adonias Malosso wrote:
> Set random_page_cost = 2 solved the problem. thanks
>
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: 4s query want to run faster
2008-02-21 20:48 4s query want to run faster Adonias Malosso <malosso@gmail.com>
2008-02-21 21:10 ` Re: 4s query want to run faster Claus Guttesen <kometen@gmail.com>
2008-02-21 21:11 ` Re: 4s query want to run faster Adonias Malosso <malosso@gmail.com>
2008-02-21 21:16 ` Re: 4s query want to run faster Claus Guttesen <kometen@gmail.com>
2008-02-21 21:23 ` Re: 4s query want to run faster Adonias Malosso <malosso@gmail.com>
2008-02-21 22:59 ` Re: 4s query want to run faster Mark Kirkwood <markir@paradise.net.nz>
@ 2008-02-21 23:16 ` Scott Marlowe <scott.marlowe@gmail.com>
2008-02-21 23:40 ` Re: 4s query want to run faster Dave Cramer <pg@fastcrypt.com>
2008-02-22 05:10 ` Re: 4s query want to run faster Mark Kirkwood <markir@paradise.net.nz>
0 siblings, 2 replies; 12+ messages in thread
From: Scott Marlowe @ 2008-02-21 23:16 UTC (permalink / raw)
To: Mark Kirkwood <markir@paradise.net.nz>; +Cc: Adonias Malosso <malosso@gmail.com>; Claus Guttesen <kometen@gmail.com>; pgsql-performance
On Thu, Feb 21, 2008 at 4:59 PM, Mark Kirkwood <markir@paradise.net.nz> wrote:
> The other parameter you might want to look at is effective_cache_size -
> increasing it will encourage index use. On a machine with 16GB the
> default is probably too small (there are various recommendations about
> how to set this ISTR either Scott M or Greg Smith had a page somewhere
> that covered this quite well - guys?).
>
> Obviously, decreasing random_page_cost fixed this query for you, but if
> find yourself needing to tweak it again for other queries, then look at
> changing effective_cache_size.
effective_cache_size is pretty easy to set, and it's not real
sensitive to small changes, so guesstimation is fine where it's
concerned. Basically, let your machine run for a while, then add the
cache and buffer your unix kernel has altogether (top and free will
tell you these things). If you're running other apps on the server,
make a SWAG (scientific wild assed guess) how much the other apps are
pounding on the kernel cache / buffer and set effective_cache_size to
how much you think postgresql is using of the total and set it to
that.
If your data set fits into memory, then setting random page cost
closer to 1 makes a lot of sense, and the larger effective cache size.
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: 4s query want to run faster
2008-02-21 20:48 4s query want to run faster Adonias Malosso <malosso@gmail.com>
2008-02-21 21:10 ` Re: 4s query want to run faster Claus Guttesen <kometen@gmail.com>
2008-02-21 21:11 ` Re: 4s query want to run faster Adonias Malosso <malosso@gmail.com>
2008-02-21 21:16 ` Re: 4s query want to run faster Claus Guttesen <kometen@gmail.com>
2008-02-21 21:23 ` Re: 4s query want to run faster Adonias Malosso <malosso@gmail.com>
2008-02-21 22:59 ` Re: 4s query want to run faster Mark Kirkwood <markir@paradise.net.nz>
2008-02-21 23:16 ` Re: 4s query want to run faster Scott Marlowe <scott.marlowe@gmail.com>
@ 2008-02-21 23:40 ` Dave Cramer <pg@fastcrypt.com>
1 sibling, 0 replies; 12+ messages in thread
From: Dave Cramer @ 2008-02-21 23:40 UTC (permalink / raw)
To: Scott Marlowe <scott.marlowe@gmail.com>; +Cc: Mark Kirkwood <markir@paradise.net.nz>; Adonias Malosso <malosso@gmail.com>; Claus Guttesen <kometen@gmail.com>; pgsql-performance
On 21-Feb-08, at 6:16 PM, Scott Marlowe wrote:
> On Thu, Feb 21, 2008 at 4:59 PM, Mark Kirkwood
> <markir@paradise.net.nz> wrote:
>> The other parameter you might want to look at is
>> effective_cache_size -
>> increasing it will encourage index use. On a machine with 16GB the
>> default is probably too small (there are various recommendations
>> about
>> how to set this ISTR either Scott M or Greg Smith had a page
>> somewhere
>> that covered this quite well - guys?).
>>
The default is always too small in my experience.
What are the rest of the configuration values ?
Dave
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: 4s query want to run faster
2008-02-21 20:48 4s query want to run faster Adonias Malosso <malosso@gmail.com>
2008-02-21 21:10 ` Re: 4s query want to run faster Claus Guttesen <kometen@gmail.com>
2008-02-21 21:11 ` Re: 4s query want to run faster Adonias Malosso <malosso@gmail.com>
2008-02-21 21:16 ` Re: 4s query want to run faster Claus Guttesen <kometen@gmail.com>
2008-02-21 21:23 ` Re: 4s query want to run faster Adonias Malosso <malosso@gmail.com>
2008-02-21 22:59 ` Re: 4s query want to run faster Mark Kirkwood <markir@paradise.net.nz>
2008-02-21 23:16 ` Re: 4s query want to run faster Scott Marlowe <scott.marlowe@gmail.com>
@ 2008-02-22 05:10 ` Mark Kirkwood <markir@paradise.net.nz>
1 sibling, 0 replies; 12+ messages in thread
From: Mark Kirkwood @ 2008-02-22 05:10 UTC (permalink / raw)
To: Scott Marlowe <scott.marlowe@gmail.com>; +Cc: Adonias Malosso <malosso@gmail.com>; Claus Guttesen <kometen@gmail.com>; pgsql-performance
Scott Marlowe wrote:
>
> effective_cache_size is pretty easy to set, and it's not real
> sensitive to small changes, so guesstimation is fine where it's
> concerned. Basically, let your machine run for a while, then add the
> cache and buffer your unix kernel has altogether (top and free will
> tell you these things). If you're running other apps on the server,
> make a SWAG (scientific wild assed guess) how much the other apps are
> pounding on the kernel cache / buffer and set effective_cache_size to
> how much you think postgresql is using of the total and set it to
> that.
>
FWIW - The buffered|cached may well be called something different if you
are not on Linux (I didn't see any platform mentioned - sorry if I
missed it) - e.g for Freebsd it is "Inactive" that shows what the os is
caching and "Cached" actually means something slightly different... (yep
that's caused a lot of confusion in the past...)
Cheers
Mark
^ permalink raw reply [nested|flat] 12+ messages in thread
end of thread, other threads:[~2008-02-22 05:10 UTC | newest]
Thread overview: 12+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-02-21 20:48 4s query want to run faster Adonias Malosso <malosso@gmail.com>
2008-02-21 20:58 ` Scott Marlowe <scott.marlowe@gmail.com>
2008-02-21 21:05 ` Adonias Malosso <malosso@gmail.com>
2008-02-21 21:25 ` Scott Marlowe <scott.marlowe@gmail.com>
2008-02-21 21:10 ` Claus Guttesen <kometen@gmail.com>
2008-02-21 21:11 ` Adonias Malosso <malosso@gmail.com>
2008-02-21 21:16 ` Claus Guttesen <kometen@gmail.com>
2008-02-21 21:23 ` Adonias Malosso <malosso@gmail.com>
2008-02-21 22:59 ` Mark Kirkwood <markir@paradise.net.nz>
2008-02-21 23:16 ` Scott Marlowe <scott.marlowe@gmail.com>
2008-02-21 23:40 ` Dave Cramer <pg@fastcrypt.com>
2008-02-22 05:10 ` Mark Kirkwood <markir@paradise.net.nz>
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