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

Performance problem on 8.2.4, but not 8.2.3

From: Dave Pirotte <dpirotte(at)mediamatters(dot)org>
To: pgsql-performance(at)postgresql(dot)org
Subject: Performance problem on 8.2.4, but not 8.2.3
Date: 2007-05-25 18:08:52
Message-ID: 2C91A79B-6AFD-4734-B245-2ED81F4F15D4@mediamatters.org (view raw or flat)
Thread:
Lists: 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





Responses

pgsql-performance by date

Next:From: Richard HuxtonDate: 2007-05-25 18:13:14
Subject: Re: LIKE search and performance
Previous:From: Tom LaneDate: 2007-05-25 18:04:48
Subject: Re: How PostgreSQL handles multiple DDBB instances?

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