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

Performance issues migrating from 743 to 826

From: Matthew Lunnon <mlunnon(at)rwa-net(dot)co(dot)uk>
To: pgsql-performance(at)postgresql(dot)org
Subject: Performance issues migrating from 743 to 826
Date: 2008-01-28 11:41:41
Message-ID: 479DBF75.6080402@rwa-net.co.uk (view raw or flat)
Thread:
Lists: pgsql-performance
Hi

I am investigating migrating from postgres 743 to postgres 826 but 
although the performance in postgres 826 seems to be generally better 
there are some instances where it seems to be markedly worse, a factor 
of up to 10.  The problem seems to occur when I join to more than 4 
tables. Has anyone else experienced anything similar or got any 
suggestions as to what I might do? I am running on an intel box with two 
hyper threaded cores and 4GB of RAM. I have tweaked the postgres.conf 
files with these values and the query and explain output are below. In 
this case the query takes 6.037 ms to run on 862 and 2.332 to run on 743.

Thanks in advance for any help.

Regards
Matthew

8.2.6
shared_buffers = 500MB
work_mem = 10MB
maintenance_work_mem = 100MB
effective_cache_size = 2048MB
default_statistics_target = 1000

7.4.3
shared_buffers = 51200
sort_mem = 10240
vacuum_mem = 81920
effective_cache_size = 102400

explain analyze
 SELECT *
   FROM market mrkt
   JOIN market_group_relation mgr USING (market_id)
   JOIN market_group mg USING (market_group_id)
   JOIN market_group_price_relation mgpr USING (market_group_id)
   JOIN accommodation_price_panel app ON 
app.accommodation_price_panel_id = mgpr.price_panel_id
  JOIN daily_rates dr USING (accommodation_price_panel_id)
WHERE mrkt.live <> 'X'::bpchar AND mg.live <> 'X'::bpchar AND app.live 
<> 'X'::bpchar
AND dr.min_group_size = 0
AND MARKET_ID = 10039 
AND CODE = 'LONHRL'
AND CODE_TYPE = 'IS'
AND ROOM_TYPE = 'Zk'
AND BOARD_TYPE = 'BB'
AND CONTRACT_ID = '16077'
AND ( START_DATE BETWEEN '2008-05-22' AND '2008-05-31' OR '2008-05-22' 
BETWEEN START_DATE AND END_DATE )

"Nested Loop  (cost=37.27..48.34 rows=1 width=458) (actual 
time=1.474..2.138 rows=14 loops=1)"
"  ->  Nested Loop  (cost=37.27..42.34 rows=1 width=282) (actual 
time=1.428..1.640 rows=2 loops=1)"
"        ->  Hash Join  (cost=37.27..40.68 rows=1 width=199) (actual 
time=1.367..1.516 rows=2 loops=1)"
"              Hash Cond: ("outer".market_group_id = 
"inner".market_group_id)"
"              ->  Seq Scan on market_group mg  (cost=0.00..3.01 rows=78 
width=81) (actual time=0.004..0.105 rows=80 loops=1)"
"                    Filter: (live <> 'X'::bpchar)"
"              ->  Hash  (cost=37.27..37.27 rows=1 width=126) (actual 
time=1.325..1.325 rows=0 loops=1)"
"                    ->  Hash Join  (cost=12.66..37.27 rows=1 width=126) 
(actual time=1.051..1.321 rows=2 loops=1)"
"                          Hash Cond: ("outer".market_group_id = 
"inner".market_group_id)"
"                          ->  Seq Scan on market_group_relation mgr  
(cost=0.00..24.46 rows=27 width=31) (actual time=0.165..0.641 rows=30 
loops=1)"
"                                Filter: (10039 = market_id)"
"                          ->  Hash  (cost=12.66..12.66 rows=2 width=95) 
(actual time=0.641..0.641 rows=0 loops=1)"
"                                ->  Nested Loop  (cost=0.00..12.66 
rows=2 width=95) (actual time=0.056..0.593 rows=27 loops=1)"
"                                      ->  Index Scan using 
accommodation_price_panel_idx1 on accommodation_price_panel app  
(cost=0.00..6.02 rows=1 width=60) (actual time=0.037..0.200 rows=27 
loops=1)"
"                                            Index Cond: ((contract_id = 
16077) AND ((code)::text = 'LONHRL'::text) AND (code_type = 'IS'::bpchar))"
"                                            Filter: (live <> 'X'::bpchar)"
"                                      ->  Index Scan using 
market_group_price_relation_pkey on market_group_price_relation mgpr  
(cost=0.00..6.62 rows=1 width=35) (actual time=0.007..0.008 rows=1 
loops=27)"
"                                            Index Cond: 
("outer".accommodation_price_panel_id = mgpr.price_panel_id)"
"        ->  Seq Scan on market mrkt  (cost=0.00..1.65 rows=1 width=87) 
(actual time=0.045..0.046 rows=1 loops=2)"
"              Filter: ((live <> 'X'::bpchar) AND (market_id = 10039))"
"  ->  Index Scan using daily_rates_pkey on daily_rates dr  
(cost=0.00..5.99 rows=1 width=180) (actual time=0.022..0.113 rows=7 
loops=2)"
"        Index Cond: ((dr.accommodation_price_panel_id = 
"outer".price_panel_id) AND (dr.room_type = 'Zk'::bpchar))"
"        Filter: ((min_group_size = 0) AND (board_type = 'BB'::bpchar) 
AND (('2008-05-22'::date >= start_date) OR (start_date >= 
'2008-05-22'::date)) AND (('2008-05-22'::date <= end_date) OR 
(start_date >= '2008-05-22'::date)) AND (('2008-05-22'::date >= st (..)"
"Total runtime: 2.332 ms"


"Nested Loop  (cost=0.00..30.39 rows=1 width=458) (actual 
time=0.123..5.841 rows=14 loops=1)"
"  ->  Nested Loop  (cost=0.00..29.70 rows=1 width=439) (actual 
time=0.099..4.590 rows=189 loops=1)"
"        ->  Nested Loop  (cost=0.00..29.40 rows=1 width=358) (actual 
time=0.091..3.243 rows=189 loops=1)"
"              ->  Nested Loop  (cost=0.00..21.07 rows=1 width=327) 
(actual time=0.081..1.571 rows=189 loops=1)"
"                    ->  Nested Loop  (cost=0.00..10.40 rows=1 
width=147) (actual time=0.053..0.134 rows=27 loops=1)"
"                          ->  Seq Scan on market mrkt  (cost=0.00..2.08 
rows=1 width=87) (actual time=0.022..0.023 rows=1 loops=1)"
"                                Filter: ((live <> 'X'::bpchar) AND 
(market_id = 10039))"
"                          ->  Index Scan using 
accommodation_price_panel_idx1 on accommodation_price_panel app  
(cost=0.00..8.31 rows=1 width=60) (actual time=0.027..0.071 rows=27 
loops=1)"
"                                Index Cond: ((contract_id = 16077) AND 
((code)::text = 'LONHRL'::text) AND (code_type = 'IS'::bpchar))"
"                                Filter: (live <> 'X'::bpchar)"
"                    ->  Index Scan using daily_rates_pkey on 
daily_rates dr  (cost=0.00..10.64 rows=1 width=180) (actual 
time=0.019..0.038 rows=7 loops=27)"
"                          Index Cond: 
((app.accommodation_price_panel_id = dr.accommodation_price_panel_id) 
AND (dr.room_type = 'Zk'::bpchar) AND (dr.board_type = 'BB'::bpchar) AND 
(dr.min_group_size = 0))"
"                          Filter: (((start_date >= '2008-05-22'::date) 
AND (start_date <= '2008-05-31'::date)) OR (('2008-05-22'::date >= 
start_date) AND ('2008-05-22'::date <= end_date)))"
"              ->  Index Scan using market_group_price_relation_pkey on 
market_group_price_relation mgpr  (cost=0.00..8.31 rows=1 width=35) 
(actual time=0.005..0.006 rows=1 loops=189)"
"                    Index Cond: (app.accommodation_price_panel_id = 
mgpr.price_panel_id)"
"        ->  Index Scan using market_group_pkey on market_group mg  
(cost=0.00..0.28 rows=1 width=81) (actual time=0.003..0.004 rows=1 
loops=189)"
"              Index Cond: (mgpr.market_group_id = mg.market_group_id)"
"              Filter: (live <> 'X'::bpchar)"
"  ->  Index Scan using market_group_relation_idx2 on 
market_group_relation mgr  (cost=0.00..0.67 rows=1 width=31) (actual 
time=0.005..0.005 rows=0 loops=189)"
"        Index Cond: (mgr.market_group_id = mg.market_group_id)"
"        Filter: (10039 = market_id)"
"Total runtime: 6.037 ms"


Responses

pgsql-performance by date

Next:From: Matthew LunnonDate: 2008-01-28 12:02:26
Subject: Performance problems inside a stored procedure.
Previous:From: Guillaume SmetDate: 2008-01-28 08:18:08
Subject: Re: 8.3rc1 Out of memory when performing update

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