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"
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
