pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedPerformance problem on 8.2.4, but not 8.2.3
7+ messages / 4 participants
[nested] [flat]
* Performance problem on 8.2.4, but not 8.2.3
@ 2007-05-25 18:08 Dave Pirotte <dpirotte@mediamatters.org>
2007-05-25 19:17 ` Re: Performance problem on 8.2.4, but not 8.2.3 Kristo Kaiv <kristo.kaiv@skype.net>
2007-05-25 19:56 ` Re: Performance problem on 8.2.4, but not 8.2.3 Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 7+ messages in thread
From: Dave Pirotte @ 2007-05-25 18:08 UTC (permalink / raw)
To: pgsql-performance
Greetings,
We have two servers running pgsql -- an older server running 8.2.3,
and a newer (far superior) one running 8.2.4. One of our reporting
queries is running painfully slowly on 8.2.4, but it executes in a
reasonable timeframe on 8.2.3. Below, I've included a contrived,
stripped down query which still exhibits the same unintuitively poor
performance, as well as its explain analyze output from both
servers. In particular, 8.2.4 opts for filters in a couple places
where we would expect index conds. Also, we've noticed that the
8.2.4 box (in other similar queries) consistently underestimates
costs, whereas the 8.2.3 box consistently overestimates.
All columns involved in this query are indexed (btrees), and there is
a functional index on mm_date_trunc('day', created_at)...where
mm_date_trunc is simply an immutable version of date_trunc (fine for
our purposes). The only configuration differences between the
servers are various memory settings... work_mem and temp_buffers are
8mb / 16mb, shared buffers 128mb / 512mb on the 8.2.3 and 8.2.4
servers, respectively. Stats targets are 10 on both, for
consistency... but it is worth mentioning that performance was still
abysmal under 8.2.4 with 250 as the target.
Any insight would be most appreciated, as we're a bit stumped. Thanks!
Cheers,
Dave Pirotte
Director of Technology
Media Matters for America
===============================================================
select h.day, h.c as total,
(select count(*) as c
from hits h2
join uri_qstrings uq on (h2.uri_qstring_id = uq.id)
join referrer_paths rp on (h2.referrer_path_id = rp.id)
join referrer_domains rd on (rp.referrer_domain_id = rd.id)
where mm_date_trunc('day', created_at) = h.day
and site_id = 3
and uq.qstring = '?f=h_top'
and rd.domain = 'mediamatters.org'
) as h_top
from (
select mm_date_trunc('day', h.created_at) as day,
count(*) as c
from hits h
where created_at > date_trunc('day', now() - interval '2 days')
group by mm_date_trunc('day', h.created_at)
) h
order by h.day asc;
QUERY PLAN (8.2.4)
------------------------------------------------------------------------
------------------------------------------------------------------------
----------------------------------------
Sort (cost=204012.65..204012.66 rows=3 width=16) (actual
time=83012.885..83012.885 rows=3 loops=1)
Sort Key: "day" -> Subquery Scan h (cost=149811.02..204012.62
rows=3 width=16) (actual time=28875.251..83012.868 rows=3 loops=1)
-> HashAggregate (cost=149811.02..149811.06 rows=3
width=8) (actual time=1602.787..1602.794 rows=3 loops=1)
-> Bitmap Heap Scan on hits h
(cost=6485.90..148079.18 rows=346368 width=8) (actual
time=48.222..1358.196 rows=391026 loops=1)
Recheck Cond: (created_at > date_trunc
('day'::text, (now() - '2 days'::interval)))
-> Bitmap Index Scan on hits_created_idx
(cost=0.00..6399.31 rows=346368 width=0) (actual time=47.293..47.293
rows=391027 loops=1)
Index Cond: (created_at > date_trunc
('day'::text, (now() - '2 days'::interval)))
SubPlan
-> Aggregate (cost=18067.17..18067.18 rows=1 width=0)
(actual time=27136.681..27136.681 rows=1 loops=3)
-> Nested Loop (cost=40.66..18067.16 rows=1
width=0) (actual time=1105.396..27135.496 rows=3394 loops=3)
-> Nested Loop (cost=40.66..18063.56 rows=9
width=8) (actual time=32.132..26837.394 rows=50537 loops=3)
-> Nested Loop (cost=40.66..5869.35
rows=47 width=8) (actual time=20.482..276.889 rows=121399 loops=3)
-> Index Scan using
referrer_domains_domains_idx on referrer_domains rd (cost=0.00..8.27
rows=1 width=8) (actual time=0.024..0.026 rows=1 loops=3)
Index Cond:
(("domain")::text = 'mediamatters.org'::text)
-> Bitmap Heap Scan on
referrer_paths rp (cost=40.66..5834.77 rows=2105 width=16) (actual
time=20.402..210.440 rows=121399 loops=3)
Recheck Cond:
(rp.referrer_domain_id = rd.id)
-> Bitmap Index Scan on
referrer_paths_domains_idx (cost=0.00..40.13 rows=2105 width=0)
(actual time=17.077..17.077 rows=121399 loops=3)
Index Cond:
(rp.referrer_domain_id = rd.id)
-> Index Scan using hits_refer_idx on
hits h2 (cost=0.00..257.59 rows=149 width=16) (actual
time=0.167..0.218 rows=0 loops=364197)
Index Cond: (h2.referrer_path_id
= rp.id)
Filter: ((mm_date_trunc
('day'::text, created_at) = $0) AND (site_id = 3))
-> Index Scan using uri_qstrings_pkey on
uri_qstrings uq (cost=0.00..0.39 rows=1 width=8) (actual
time=0.005..0.005 rows=0 loops=151611)
Index Cond: (h2.uri_qstring_id = uq.id)
Filter: ((qstring)::text = '?
f=h_top'::text)
Total runtime: 83013.098 ms
QUERY PLAN (8.2.3)
------------------------------------------------------------------------
------------------------------------------------------------------------
-------------------------------------
Sort (cost=270110.73..270110.74 rows=1 width=16) (actual
time=2116.106..2116.107 rows=3 loops=1)
Sort Key: "day" -> Subquery Scan h (cost=118726.46..270110.72
rows=1 width=16) (actual time=1763.504..2116.090 rows=3 loops=1)
-> HashAggregate (cost=118726.46..118726.47 rows=1
width=8) (actual time=1678.462..1678.467 rows=3 loops=1)
-> Bitmap Heap Scan on hits h
(cost=1827.68..118382.45 rows=68802 width=8) (actual
time=56.346..1496.264 rows=334231 loops=1)
Recheck Cond: (created_at > date_trunc
('day'::text, (now() - '2 days'::interval)))
-> Bitmap Index Scan on hits_created_idx
(cost=0.00..1810.48 rows=68802 width=0) (actual time=55.225..55.225
rows=334231 loops=1)
Index Cond: (created_at > date_trunc
('day'::text, (now() - '2 days'::interval)))
SubPlan
-> Aggregate (cost=151384.23..151384.24 rows=1 width=0)
(actual time=145.865..145.865 rows=1 loops=3)
-> Hash Join (cost=4026.42..151384.23 rows=1
width=0) (actual time=30.663..145.271 rows=2777 loops=3)
Hash Cond: (rp.referrer_domain_id = rd.id)
-> Nested Loop (cost=4018.13..151375.82
rows=30 width=8) (actual time=30.585..143.498 rows=3174 loops=3)
-> Hash Join (cost=4018.13..151149.21
rows=30 width=8) (actual time=30.550..93.357 rows=3174 loops=3)
Hash Cond: (h2.uri_qstring_id =
uq.id)
-> Bitmap Heap Scan on hits h2
(cost=3857.37..150325.60 rows=176677 width=16) (actual
time=19.710..60.881 rows=108568 loops=3)
Recheck Cond: (mm_date_trunc
('day'::text, created_at) = $0)
Filter: (site_id = 3)
-> Bitmap Index Scan on
hits_date_trunc_day_idx (cost=0.00..3813.20 rows=178042 width=0)
(actual time=19.398..19.398 rows=111410 loops=3)
Index Cond:
(mm_date_trunc('day'::text, created_at) = $0)
-> Hash (cost=160.24..160.24
rows=42 width=8) (actual time=32.417..32.417 rows=141 loops=1)
-> Bitmap Heap Scan on
uri_qstrings uq (cost=4.69..160.24 rows=42 width=8) (actual
time=31.502..32.352 rows=141 loops=1)
Recheck Cond:
((qstring)::text = '?f=h_top'::text)
-> Bitmap Index Scan
on uri_qstrings_qstring_idx (cost=0.00..4.68 rows=42 width=0)
(actual time=31.482..31.482 rows=141 loops=1)
Index Cond:
((qstring)::text = '?f=h_top'::text)
-> Index Scan using
referrer_paths_pkey on referrer_paths rp (cost=0.00..7.54 rows=1
width=16) (actual time=0.014..0.015 rows=1 loops=9521)
Index Cond: (h2.referrer_path_id
= rp.id)
-> Hash (cost=8.27..8.27 rows=1 width=8)
(actual time=0.062..0.062 rows=1 loops=1)
-> Index Scan using
referrer_domains_domains_idx on referrer_domains rd (cost=0.00..8.27
rows=1 width=8) (actual time=0.058..0.059 rows=1 loops=1)
Index Cond: (("domain")::text =
'mediamatters.org'::text)
Total runtime: 2116.266 ms
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Performance problem on 8.2.4, but not 8.2.3
2007-05-25 18:08 Performance problem on 8.2.4, but not 8.2.3 Dave Pirotte <dpirotte@mediamatters.org>
@ 2007-05-25 19:17 ` Kristo Kaiv <kristo.kaiv@skype.net>
1 sibling, 0 replies; 7+ messages in thread
From: Kristo Kaiv @ 2007-05-25 19:17 UTC (permalink / raw)
To: Dave Pirotte <dpirotte@mediamatters.org>; +Cc: pgsql-performance
Lo!
referrer_paths seems to have totally wrong stats. try full analyze on
it.
how many records in total do you have in referrer_paths on 8.2.4 server?
might be just a problem of usage pattern change from old system to
new (1 row vs. 121399 rows) ?
does not seem to be just a plan problem as the data itself seems to
be quite different.
Kristo
On 25.05.2007, at 21:08, Dave Pirotte wrote:
> Greetings,
>
> We have two servers running pgsql -- an older server running 8.2.3,
> and a newer (far superior) one running 8.2.4. One of our reporting
> queries is running painfully slowly on 8.2.4, but it executes in a
> reasonable timeframe on 8.2.3. Below, I've included a contrived,
> stripped down query which still exhibits the same unintuitively
> poor performance, as well as its explain analyze output from both
> servers. In particular, 8.2.4 opts for filters in a couple places
> where we would expect index conds. Also, we've noticed that the
> 8.2.4 box (in other similar queries) consistently underestimates
> costs, whereas the 8.2.3 box consistently overestimates.
>
> All columns involved in this query are indexed (btrees), and there
> is a functional index on mm_date_trunc('day', created_at)...where
> mm_date_trunc is simply an immutable version of date_trunc (fine
> for our purposes). The only configuration differences between the
> servers are various memory settings... work_mem and temp_buffers
> are 8mb / 16mb, shared buffers 128mb / 512mb on the 8.2.3 and 8.2.4
> servers, respectively. Stats targets are 10 on both, for
> consistency... but it is worth mentioning that performance was
> still abysmal under 8.2.4 with 250 as the target.
>
> Any insight would be most appreciated, as we're a bit stumped.
> Thanks!
>
> Cheers,
>
> Dave Pirotte
> Director of Technology
> Media Matters for America
>
> ===============================================================
>
> select h.day, h.c as total,
> (select count(*) as c
> from hits h2
> join uri_qstrings uq on (h2.uri_qstring_id = uq.id)
> join referrer_paths rp on (h2.referrer_path_id = rp.id)
> join referrer_domains rd on (rp.referrer_domain_id = rd.id)
> where mm_date_trunc('day', created_at) = h.day
> and site_id = 3
> and uq.qstring = '?f=h_top'
> and rd.domain = 'mediamatters.org'
> ) as h_top
> from (
> select mm_date_trunc('day', h.created_at) as day,
> count(*) as c
> from hits h
> where created_at > date_trunc('day', now() - interval '2 days')
> group by mm_date_trunc('day', h.created_at)
> ) h
> order by h.day asc;
>
>
> QUERY PLAN (8.2.4)
> ----------------------------------------------------------------------
> ----------------------------------------------------------------------
> --------------------------------------------
> Sort (cost=204012.65..204012.66 rows=3 width=16) (actual
> time=83012.885..83012.885 rows=3 loops=1)
> Sort Key: "day" -> Subquery Scan h
> (cost=149811.02..204012.62 rows=3 width=16) (actual
> time=28875.251..83012.868 rows=3 loops=1)
> -> HashAggregate (cost=149811.02..149811.06 rows=3
> width=8) (actual time=1602.787..1602.794 rows=3 loops=1)
> -> Bitmap Heap Scan on hits h
> (cost=6485.90..148079.18 rows=346368 width=8) (actual
> time=48.222..1358.196 rows=391026 loops=1)
> Recheck Cond: (created_at > date_trunc
> ('day'::text, (now() - '2 days'::interval)))
> -> Bitmap Index Scan on hits_created_idx
> (cost=0.00..6399.31 rows=346368 width=0) (actual
> time=47.293..47.293 rows=391027 loops=1)
> Index Cond: (created_at > date_trunc
> ('day'::text, (now() - '2 days'::interval)))
> SubPlan
> -> Aggregate (cost=18067.17..18067.18 rows=1 width=0)
> (actual time=27136.681..27136.681 rows=1 loops=3)
> -> Nested Loop (cost=40.66..18067.16 rows=1
> width=0) (actual time=1105.396..27135.496 rows=3394 loops=3)
> -> Nested Loop (cost=40.66..18063.56
> rows=9 width=8) (actual time=32.132..26837.394 rows=50537 loops=3)
> -> Nested Loop (cost=40.66..5869.35
> rows=47 width=8) (actual time=20.482..276.889 rows=121399 loops=3)
> -> Index Scan using
> referrer_domains_domains_idx on referrer_domains rd
> (cost=0.00..8.27 rows=1 width=8) (actual time=0.024..0.026 rows=1
> loops=3)
> Index Cond:
> (("domain")::text = 'mediamatters.org'::text)
> -> Bitmap Heap Scan on
> referrer_paths rp (cost=40.66..5834.77 rows=2105 width=16) (actual
> time=20.402..210.440 rows=121399 loops=3)
> Recheck Cond:
> (rp.referrer_domain_id = rd.id)
> -> Bitmap Index Scan on
> referrer_paths_domains_idx (cost=0.00..40.13 rows=2105 width=0)
> (actual time=17.077..17.077 rows=121399 loops=3)
> Index Cond:
> (rp.referrer_domain_id = rd.id)
> -> Index Scan using hits_refer_idx on
> hits h2 (cost=0.00..257.59 rows=149 width=16) (actual
> time=0.167..0.218 rows=0 loops=364197)
> Index Cond: (h2.referrer_path_id
> = rp.id)
> Filter: ((mm_date_trunc
> ('day'::text, created_at) = $0) AND (site_id = 3))
> -> Index Scan using uri_qstrings_pkey on
> uri_qstrings uq (cost=0.00..0.39 rows=1 width=8) (actual
> time=0.005..0.005 rows=0 loops=151611)
> Index Cond: (h2.uri_qstring_id = uq.id)
> Filter: ((qstring)::text = '?
> f=h_top'::text)
> Total runtime: 83013.098 ms
>
>
>
> QUERY PLAN (8.2.3)
> ----------------------------------------------------------------------
> ----------------------------------------------------------------------
> -----------------------------------------
> Sort (cost=270110.73..270110.74 rows=1 width=16) (actual
> time=2116.106..2116.107 rows=3 loops=1)
> Sort Key: "day" -> Subquery Scan h
> (cost=118726.46..270110.72 rows=1 width=16) (actual
> time=1763.504..2116.090 rows=3 loops=1)
> -> HashAggregate (cost=118726.46..118726.47 rows=1
> width=8) (actual time=1678.462..1678.467 rows=3 loops=1)
> -> Bitmap Heap Scan on hits h
> (cost=1827.68..118382.45 rows=68802 width=8) (actual
> time=56.346..1496.264 rows=334231 loops=1)
> Recheck Cond: (created_at > date_trunc
> ('day'::text, (now() - '2 days'::interval)))
> -> Bitmap Index Scan on hits_created_idx
> (cost=0.00..1810.48 rows=68802 width=0) (actual time=55.225..55.225
> rows=334231 loops=1)
> Index Cond: (created_at > date_trunc
> ('day'::text, (now() - '2 days'::interval)))
> SubPlan
> -> Aggregate (cost=151384.23..151384.24 rows=1
> width=0) (actual time=145.865..145.865 rows=1 loops=3)
> -> Hash Join (cost=4026.42..151384.23 rows=1
> width=0) (actual time=30.663..145.271 rows=2777 loops=3)
> Hash Cond: (rp.referrer_domain_id = rd.id)
> -> Nested Loop (cost=4018.13..151375.82
> rows=30 width=8) (actual time=30.585..143.498 rows=3174 loops=3)
> -> Hash Join
> (cost=4018.13..151149.21 rows=30 width=8) (actual
> time=30.550..93.357 rows=3174 loops=3)
> Hash Cond: (h2.uri_qstring_id =
> uq.id)
> -> Bitmap Heap Scan on hits h2
> (cost=3857.37..150325.60 rows=176677 width=16) (actual
> time=19.710..60.881 rows=108568 loops=3)
> Recheck Cond:
> (mm_date_trunc('day'::text, created_at) = $0)
> Filter: (site_id = 3)
> -> Bitmap Index Scan on
> hits_date_trunc_day_idx (cost=0.00..3813.20 rows=178042 width=0)
> (actual time=19.398..19.398 rows=111410 loops=3)
> Index Cond:
> (mm_date_trunc('day'::text, created_at) = $0)
> -> Hash (cost=160.24..160.24
> rows=42 width=8) (actual time=32.417..32.417 rows=141 loops=1)
> -> Bitmap Heap Scan on
> uri_qstrings uq (cost=4.69..160.24 rows=42 width=8) (actual
> time=31.502..32.352 rows=141 loops=1)
> Recheck Cond:
> ((qstring)::text = '?f=h_top'::text)
> -> Bitmap Index
> Scan on uri_qstrings_qstring_idx (cost=0.00..4.68 rows=42 width=0)
> (actual time=31.482..31.482 rows=141 loops=1)
> Index Cond:
> ((qstring)::text = '?f=h_top'::text)
> -> Index Scan using
> referrer_paths_pkey on referrer_paths rp (cost=0.00..7.54 rows=1
> width=16) (actual time=0.014..0.015 rows=1 loops=9521)
> Index Cond: (h2.referrer_path_id
> = rp.id)
> -> Hash (cost=8.27..8.27 rows=1 width=8)
> (actual time=0.062..0.062 rows=1 loops=1)
> -> Index Scan using
> referrer_domains_domains_idx on referrer_domains rd
> (cost=0.00..8.27 rows=1 width=8) (actual time=0.058..0.059 rows=1
> loops=1)
> Index Cond: (("domain")::text =
> 'mediamatters.org'::text)
> Total runtime: 2116.266 ms
>
>
>
>
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Performance problem on 8.2.4, but not 8.2.3
2007-05-25 18:08 Performance problem on 8.2.4, but not 8.2.3 Dave Pirotte <dpirotte@mediamatters.org>
@ 2007-05-25 19:56 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-05-25 20:05 ` Re: Performance problem on 8.2.4, but not 8.2.3 Steinar H. Gunderson <sgunderson@bigfoot.com>
1 sibling, 1 reply; 7+ messages in thread
From: Tom Lane @ 2007-05-25 19:56 UTC (permalink / raw)
To: Dave Pirotte <dpirotte@mediamatters.org>; +Cc: pgsql-performance
Dave Pirotte <dpirotte@mediamatters.org> writes:
> We have two servers running pgsql -- an older server running 8.2.3,
> and a newer (far superior) one running 8.2.4. One of our reporting
> queries is running painfully slowly on 8.2.4, but it executes in a
> reasonable timeframe on 8.2.3.
Are you sure you've analyzed all these tables in the 8.2.4 database?
Some of the rowcount estimates seem a bit far off.
I looked through the CVS logs and didn't find any planner changes
between 8.2.3 and 8.2.4 that seem likely to affect your query, so
I'm thinking it must be a statistical discrepancy.
regards, tom lane
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Performance problem on 8.2.4, but not 8.2.3
2007-05-25 18:08 Performance problem on 8.2.4, but not 8.2.3 Dave Pirotte <dpirotte@mediamatters.org>
2007-05-25 19:56 ` Re: Performance problem on 8.2.4, but not 8.2.3 Tom Lane <tgl@sss.pgh.pa.us>
@ 2007-05-25 20:05 ` Steinar H. Gunderson <sgunderson@bigfoot.com>
2007-05-25 20:33 ` Re: Performance problem on 8.2.4, but not 8.2.3 Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 7+ messages in thread
From: Steinar H. Gunderson @ 2007-05-25 20:05 UTC (permalink / raw)
To: pgsql-performance
On Fri, May 25, 2007 at 03:56:35PM -0400, Tom Lane wrote:
> I looked through the CVS logs and didn't find any planner changes
> between 8.2.3 and 8.2.4 that seem likely to affect your query, so
> I'm thinking it must be a statistical discrepancy.
It looks like the estimated cost is lower for 8.2.4 -- could it be that the
fact that he's giving it more memory lead to the planner picking a plan that
happens to be worse?
/* Steinar */
--
Homepage: http://www.sesse.net/
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Performance problem on 8.2.4, but not 8.2.3
2007-05-25 18:08 Performance problem on 8.2.4, but not 8.2.3 Dave Pirotte <dpirotte@mediamatters.org>
2007-05-25 19:56 ` Re: Performance problem on 8.2.4, but not 8.2.3 Tom Lane <tgl@sss.pgh.pa.us>
2007-05-25 20:05 ` Re: Performance problem on 8.2.4, but not 8.2.3 Steinar H. Gunderson <sgunderson@bigfoot.com>
@ 2007-05-25 20:33 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-05-25 21:37 ` Re: Performance problem on 8.2.4, but not 8.2.3 Dave Pirotte <dpirotte@mediamatters.org>
0 siblings, 1 reply; 7+ messages in thread
From: Tom Lane @ 2007-05-25 20:33 UTC (permalink / raw)
To: Steinar H. Gunderson <sgunderson@bigfoot.com>; +Cc: pgsql-performance
"Steinar H. Gunderson" <sgunderson@bigfoot.com> writes:
> It looks like the estimated cost is lower for 8.2.4 -- could it be that the
> fact that he's giving it more memory lead to the planner picking a plan that
> happens to be worse?
Offhand I don't think so. More work_mem might make a hash join look
cheaper (or a sort for a mergejoin), but the problem here seems to be
that it's switching away from a hash and to a nestloop. Which is a
loser because there are many more outer-relation rows than it's
expecting.
regards, tom lane
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Performance problem on 8.2.4, but not 8.2.3
2007-05-25 18:08 Performance problem on 8.2.4, but not 8.2.3 Dave Pirotte <dpirotte@mediamatters.org>
2007-05-25 19:56 ` Re: Performance problem on 8.2.4, but not 8.2.3 Tom Lane <tgl@sss.pgh.pa.us>
2007-05-25 20:05 ` Re: Performance problem on 8.2.4, but not 8.2.3 Steinar H. Gunderson <sgunderson@bigfoot.com>
2007-05-25 20:33 ` Re: Performance problem on 8.2.4, but not 8.2.3 Tom Lane <tgl@sss.pgh.pa.us>
@ 2007-05-25 21:37 ` Dave Pirotte <dpirotte@mediamatters.org>
2007-05-26 12:41 ` Re: Performance problem on 8.2.4, but not 8.2.3 Kristo Kaiv <kristo.kaiv@skype.net>
0 siblings, 1 reply; 7+ messages in thread
From: Dave Pirotte @ 2007-05-25 21:37 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Steinar H. Gunderson <sgunderson@bigfoot.com>; pgsql-performance
Thanks for the quick responses. :-) The data is almost identical,
between the two servers: 8.2.3 has 882198 records, 8.2.4 has 893121.
For background, I pg_dump'ed the data into the 8.2.4 server
yesterday, and analyzed with the stats target of 250, then reanalyzed
with target 10. So, the statistics should theoretically be ok.
Running a vacuum full analyze on referrer_paths, per Kristo's
suggestion, didn't affect the query plan.
We downgraded to 8.2.3 just to rule that out, upped stats target to
100, analyzed, and are still experiencing the same behavior -- it's
still coming up with the same bogus rowcount estimates. Over the
weekend I'll lower the memory and see if that does anything, just to
rule that out... Any other thoughts? Thanks so much for your time
and suggestions thus far.
Cheers,
Dave
On May 25, 2007, at 4:33 PM, Tom Lane wrote:
> "Steinar H. Gunderson" <sgunderson@bigfoot.com> writes:
>> It looks like the estimated cost is lower for 8.2.4 -- could it be
>> that the
>> fact that he's giving it more memory lead to the planner picking a
>> plan that
>> happens to be worse?
>
> Offhand I don't think so. More work_mem might make a hash join look
> cheaper (or a sort for a mergejoin), but the problem here seems to be
> that it's switching away from a hash and to a nestloop. Which is a
> loser because there are many more outer-relation rows than it's
> expecting.
>
> regards, tom lane
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 7: You can help support the PostgreSQL project by donating at
>
> http://www.postgresql.org/about/donate
>
Dave Pirotte
Director of Technology
Media Matters for America
dpirotte@mediamatters.org
phone: 202-756-4122
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Performance problem on 8.2.4, but not 8.2.3
2007-05-25 18:08 Performance problem on 8.2.4, but not 8.2.3 Dave Pirotte <dpirotte@mediamatters.org>
2007-05-25 19:56 ` Re: Performance problem on 8.2.4, but not 8.2.3 Tom Lane <tgl@sss.pgh.pa.us>
2007-05-25 20:05 ` Re: Performance problem on 8.2.4, but not 8.2.3 Steinar H. Gunderson <sgunderson@bigfoot.com>
2007-05-25 20:33 ` Re: Performance problem on 8.2.4, but not 8.2.3 Tom Lane <tgl@sss.pgh.pa.us>
2007-05-25 21:37 ` Re: Performance problem on 8.2.4, but not 8.2.3 Dave Pirotte <dpirotte@mediamatters.org>
@ 2007-05-26 12:41 ` Kristo Kaiv <kristo.kaiv@skype.net>
0 siblings, 0 replies; 7+ messages in thread
From: Kristo Kaiv @ 2007-05-26 12:41 UTC (permalink / raw)
To: Dave Pirotte <dpirotte@mediamatters.org>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Steinar H. Gunderson <sgunderson@bigfoot.com>; pgsql-performance
these bogus rowcount estimates are a bit strange. if you have 800K
rows and select 100K of them the rowcount estimate should most likely
come from the histogram for the column. can you check what the
histograms are for
referrer path tables refferrer_domain where id <= referrer_domain
'mediamatters.org' both in 8.2.3 and 8.2.4
I still think this happens because of skewed statistics. More memory
should encourage the planner to choose hash join over nested loop afaik.
Kristo
On 26.05.2007, at 0:37, Dave Pirotte wrote:
> Thanks for the quick responses. :-) The data is almost identical,
> between the two servers: 8.2.3 has 882198 records, 8.2.4 has
> 893121. For background, I pg_dump'ed the data into the 8.2.4
> server yesterday, and analyzed with the stats target of 250, then
> reanalyzed with target 10. So, the statistics should
> theoretically be ok. Running a vacuum full analyze on
> referrer_paths, per Kristo's suggestion, didn't affect the query plan.
>
> We downgraded to 8.2.3 just to rule that out, upped stats target to
> 100, analyzed, and are still experiencing the same behavior -- it's
> still coming up with the same bogus rowcount estimates. Over the
> weekend I'll lower the memory and see if that does anything, just
> to rule that out... Any other thoughts? Thanks so much for your
> time and suggestions thus far.
>
> Cheers,
> Dave
>
> On May 25, 2007, at 4:33 PM, Tom Lane wrote:
>
>> "Steinar H. Gunderson" <sgunderson@bigfoot.com> writes:
>>> It looks like the estimated cost is lower for 8.2.4 -- could it
>>> be that the
>>> fact that he's giving it more memory lead to the planner picking
>>> a plan that
>>> happens to be worse?
>>
>> Offhand I don't think so. More work_mem might make a hash join look
>> cheaper (or a sort for a mergejoin), but the problem here seems to be
>> that it's switching away from a hash and to a nestloop. Which is a
>> loser because there are many more outer-relation rows than it's
>> expecting.
>>
>> regards, tom lane
>>
>> ---------------------------(end of
>> broadcast)---------------------------
>> TIP 7: You can help support the PostgreSQL project by donating at
>>
>> http://www.postgresql.org/about/donate
>>
>
> Dave Pirotte
> Director of Technology
> Media Matters for America
> dpirotte@mediamatters.org
> phone: 202-756-4122
>
>
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2007-05-26 12:41 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-05-25 18:08 Performance problem on 8.2.4, but not 8.2.3 Dave Pirotte <dpirotte@mediamatters.org>
2007-05-25 19:17 ` Kristo Kaiv <kristo.kaiv@skype.net>
2007-05-25 19:56 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-05-25 20:05 ` Steinar H. Gunderson <sgunderson@bigfoot.com>
2007-05-25 20:33 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-05-25 21:37 ` Dave Pirotte <dpirotte@mediamatters.org>
2007-05-26 12:41 ` Kristo Kaiv <kristo.kaiv@skype.net>
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