On Mon, 11 Aug 2003 14:25:03 -0400
Tom Lane <[EMAIL PROTECTED]> wrote:
=> set enable_mergejoin to off;
=> explain analyze ... query ...
=>
=> If it finishes in a reasonable amount of time, send the explain output.
Hi again,
I did this on the 7.3.1 database, and attached the output.
It actually ran faster after ANALYZE and 'set enable_mergejoin to off'
Thanks!
I also reloaded this database onto 7.3.4, tried the same query after
the ANALYZE, and the query executed a lot faster.
Thanks again!
I also attached the output of the EXPLAIN ANALYZE on 7.3.4
For now I'll maybe just disable mergejoin. But definitely a postgres
upgrade is what I will do.
I went through the different outputs of EXPLAIN ANALYZE a bit, and
I think I can now see where the difference is.
Thanks a lot for the help.
Regards
Stefan.
Aggregate (cost=103991.51..103999.75 rows=27 width=241) (actual
time=77907.78..83292.51 rows=125803 loops=1)
-> Group (cost=103991.51..103996.32 rows=274 width=241) (actual
time=77907.61..79449.70 rows=125803 loops=1)
-> Sort (cost=103991.51..103992.20 rows=274 width=241) (actual
time=77907.58..78149.54 rows=125803 loops=1)
Sort Key: m.group_code, m.sku, m.brn_code, m.stktype_code,
ss.supplier_price_curr_cost, sk.sku_price_curr_cost
-> Hash Join (cost=2599.45..103980.40 rows=274 width=241) (actual
time=2527.34..73353.16 rows=125803 loops=1)
Hash Cond: ("outer".country_code = "inner".from_ctry)
-> Nested Loop (cost=2585.54..103961.83 rows=12 width=223)
(actual time=2504.90..71966.16 rows=125803 loops=1)
Join Filter: (("inner".price_tmpl_hdr_cde =
"outer".price_tmpl_hdr_cde) AND ("inner".price_tmpl_hdr_reg =
"outer".price_tmpl_hdr_reg) AND ("inner".group_cde = "outer".group_cde) AND
("inner".sku_mst_cde = "outer".sku))
-> Hash Join (cost=2585.54..103611.37 rows=60 width=179)
(actual time=2411.76..46771.60 rows=125803 loops=1)
Hash Cond: ("outer".brn_code = "inner".brn_code)
-> Hash Join (cost=2575.07..103599.70 rows=60
width=164) (actual time=2410.16..44730.60 rows=125803 loops=1)
Hash Cond: ("outer".brn_code =
"inner".branch_cde)
Join Filter: (("inner".group_cde =
"outer".group_code) AND ("outer".price_tmpl_hdr_cde = "inner".price_tmpl_hdr_cde) AND
("outer".price_tmpl_hdr_reg = "inner".price_tmpl_hdr_reg))
-> Hash Join (cost=2570.54..103586.96
rows=299 width=135) (actual time=2402.43..39292.85 rows=629015 loops=1)
Hash Cond: ("outer".sku = "inner".sku)
Join Filter: (("outer".group_code =
"inner".group_code) AND ("inner".supplier_cde = "outer".supplier_code))
-> Seq Scan on master_fpp_values m
(cost=0.00..98545.54 rows=220358 width=54) (actual time=1013.70..28087.16 rows=162226
loops=1)
Filter: (fpp_code = '200307'::text)
-> Hash (cost=2542.25..2542.25
rows=11318 width=81) (actual time=1388.58..1388.58 rows=0 loops=1)
-> Hash Join
(cost=543.67..2542.25 rows=11318 width=81) (actual time=188.63..1277.34 rows=54675
loops=1)
Hash Cond:
("outer".sku_mst_cde = "inner".sku)
Join Filter:
("outer".group_cde = "inner".group_code)
-> Seq Scan on
supplier_price ss (cost=0.00..1418.75 rows=54675 width=54) (actual time=5.94..553.10
rows=54675 loops=1)
-> Hash
(cost=516.06..516.06 rows=11042 width=27) (actual time=182.53..182.53 rows=0 loops=1)
-> Index Scan using
master_sku_descr_idx11 on master_sku_descr s (cost=0.00..516.06 rows=11042 width=27)
(actual time=19.15..160.75 rows=10936 loops=1)
Index Cond:
(control_code = '0'::text)
-> Hash (cost=4.02..4.02 rows=202 width=29)
(actual time=7.51..7.51 rows=0 loops=1)
-> Seq Scan on price_tmpl_det p
(cost=0.00..4.02 rows=202 width=29) (actual time=6.46..7.16 rows=202 loops=1)
-> Hash (cost=9.98..9.98 rows=198 width=15) (actual
time=1.41..1.41 rows=0 loops=1)
-> Seq Scan on master_branch_descr b
(cost=0.00..9.98 rows=198 width=15) (actual time=0.09..1.08 rows=198 loops=1)
-> Index Scan using idx_sku_price on sku_price sk
(cost=0.00..5.83 rows=1 width=44) (actual time=0.17..0.17 rows=1 loops=125803)
Index Cond: ((sk.group_cde = "outer".group_cde) AND
(sk.sku_mst_cde = "outer".sku_mst_cde) AND (sk.price_tmpl_hdr_cde =
"outer".price_tmpl_hdr_cde) AND (sk.price_tmpl_hdr_reg = "outer".price_tmpl_hdr_reg))
-> Hash (cost=13.90..13.90 rows=4 width=18) (actual
time=22.28..22.28 rows=0 loops=1)
-> Index Scan using forex_idx1 on forex f
(cost=0.00..13.90 rows=4 width=18) (actual time=22.22..22.26 rows=4 loops=1)
Index Cond: ((to_ctry = 'ZAF'::text) AND (fpp_code =
'200307'::text))
Total runtime: 83938.26 msec
(36 rows)
Aggregate (cost=100832.75..100839.08 rows=21 width=241) (actual
time=124406.04..130113.77 rows=125769 loops=1)
-> Group (cost=100832.75..100836.44 rows=211 width=241) (actual
time=124405.89..126257.32 rows=125769 loops=1)
-> Sort (cost=100832.75..100833.28 rows=211 width=241) (actual
time=124405.86..124640.33 rows=125769 loops=1)
Sort Key: m.group_code, m.sku, m.brn_code, m.stktype_code,
ss.supplier_price_curr_cost, sk.sku_price_curr_cost
-> Merge Join (cost=100824.44..100824.62 rows=211 width=241) (actual
time=105588.22..107495.57 rows=125769 loops=1)
Merge Cond: ("outer".country_code = "inner".from_ctry)
-> Sort (cost=100810.50..100810.52 rows=9 width=223) (actual
time=105562.94..105882.26 rows=125769 loops=1)
Sort Key: b.country_code
-> Nested Loop (cost=3149.50..100810.35 rows=9 width=223)
(actual time=3438.71..101078.92 rows=125769 loops=1)
Join Filter: (("inner".price_tmpl_hdr_cde =
"outer".price_tmpl_hdr_cde) AND ("inner".price_tmpl_hdr_reg =
"outer".price_tmpl_hdr_reg) AND ("inner".group_cde = "outer".group_cde) AND
("inner".sku_mst_cde = "outer".sku))
-> Hash Join (cost=3149.50..100551.84 rows=44
width=179) (actual time=3405.53..63392.62 rows=125769 loops=1)
Hash Cond: ("outer".brn_code = "inner".brn_code)
-> Hash Join (cost=3139.03..100540.48 rows=44
width=164) (actual time=3403.98..60948.65 rows=125769 loops=1)
Hash Cond: ("outer".brn_code =
"inner".branch_cde)
Join Filter: (("inner".group_cde =
"outer".group_code) AND ("outer".price_tmpl_hdr_cde = "inner".price_tmpl_hdr_cde) AND
("outer".price_tmpl_hdr_reg = "inner".price_tmpl_hdr_reg))
-> Hash Join (cost=3134.50..100529.68
rows=228 width=135) (actual time=3392.93..51324.39 rows=628845 loops=1)
Hash Cond: ("outer".sku =
"inner".sku)
Join Filter: (("outer".group_code =
"inner".group_code) AND ("inner".supplier_cde = "outer".supplier_code))
-> Seq Scan on master_fpp_values m
(cost=0.00..90519.23 rows=176470 width=54) (actual time=978.39..24186.16 rows=162115
loops=1)
Filter: (fpp_code =
'200307'::text)
-> Hash (cost=2957.40..2957.40
rows=11240 width=81) (actual time=2414.25..2414.25 rows=0 loops=1)
-> Hash Join
(cost=543.88..2957.40 rows=11240 width=81) (actual time=502.21..2278.41 rows=54670
loops=1)
Hash Cond:
("outer".sku_mst_cde = "inner".sku)
Join Filter:
("outer".group_cde = "inner".group_code)
-> Seq Scan on
supplier_price ss (cost=0.00..1421.17 rows=54917 width=54) (actual time=0.10..343.54
rows=54670 loops=1)
-> Hash
(cost=515.77..515.77 rows=11243 width=27) (actual time=501.90..501.90 rows=0 loops=1)
-> Index Scan
using master_sku_descr_idx11 on master_sku_descr s (cost=0.00..515.77 rows=11243
width=27) (actual time=37.37..477.86 rows=10935 loops=1)
Index Cond:
(control_code = '0'::text)
-> Hash (cost=4.02..4.02 rows=202
width=29) (actual time=10.81..10.81 rows=0 loops=1)
-> Seq Scan on price_tmpl_det p
(cost=0.00..4.02 rows=202 width=29) (actual time=9.67..10.45 rows=202 loops=1)
-> Hash (cost=9.98..9.98 rows=198 width=15)
(actual time=1.43..1.43 rows=0 loops=1)
-> Seq Scan on master_branch_descr b
(cost=0.00..9.98 rows=198 width=15) (actual time=0.11..1.08 rows=198 loops=1)
-> Index Scan using pk_sku_price on sku_price sk
(cost=0.00..5.83 rows=1 width=44) (actual time=0.25..0.26 rows=1 loops=125769)
Index Cond: ((sk.group_cde = "outer".group_cde)
AND (sk.sku_mst_cde = "outer".sku_mst_cde) AND (sk.price_tmpl_hdr_cde =
"outer".price_tmpl_hdr_cde) AND (sk.price_tmpl_hdr_reg = "outer".price_tmpl_hdr_reg))
-> Sort (cost=13.94..13.95 rows=4 width=18) (actual
time=25.22..113.25 rows=9906 loops=1)
Sort Key: f.from_ctry
-> Index Scan using forex_idx1 on forex f
(cost=0.00..13.90 rows=4 width=18) (actual time=24.94..25.00 rows=4 loops=1)
Index Cond: ((to_ctry = 'ZAF'::text) AND (fpp_code =
'200307'::text))
Total runtime: 130815.00 msec
(39 rows)
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
