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

Reply via email to