Hi all, I posted this problem on the sql list, and was referred to this list in stead. I have attached an sql statement that normally runs under 3 minutes. That is, until I vacuum analyze the database (or just the tables in the query), then the same query runs longer than 12 hours, and I have to kill it.
However 90% of queries are faster after analyzing on this database, there are two or three, including this one that takes for ever. I have tried to reverse engineer the explain plan from before analyzing, to come up with an sql statement, using proper joins, to force the planner to do the original join, but although I came close, I never got the same result as the original query. I suspect that this might be caused by some of the crazy indexes that were built on some of these tables, but I can't really do much about that, unless I can come up with a very good reason to nuke them. I also attached the "create table" statements for all the tables, as well as a row count of each. Can somebody help me with guidelines or something similar, to understand exactly what is happening in the explain plan. TIA Stefan
Aggregate (cost=52.00..61.64 rows=32 width=241)
-> Group (cost=52.00..57.62 rows=321 width=241)
-> Sort (cost=52.00..52.80 rows=321 width=241)
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=36.38..38.62 rows=321 width=241)
Merge Cond: (("outer".group_cde = "inner".group_cde) AND
("outer".brn_code = "inner".brn_code))
Join Filter: (("outer".price_tmpl_hdr_cde =
"inner".price_tmpl_hdr_cde) AND ("outer".price_tmpl_hdr_reg =
"inner".price_tmpl_hdr_reg))
-> Nested Loop (cost=0.00..1407212.08 rows=63 width=179)
Join Filter: (("inner".sku_mst_cde = "outer".sku) AND
("inner".group_cde = "outer".group_code))
-> Merge Join (cost=0.00..1405644.89 rows=315 width=135)
Merge Cond: ("outer".group_code = "inner".group_code)
Join Filter: ("outer".sku = "inner".sku)
-> Nested Loop (cost=0.00..4826563.70 rows=8694
width=108)
-> Index Scan using master_fpp_values_idx2 on
master_fpp_values m (cost=0.00..3766902.34 rows=215650 width=54)
Filter: (fpp_code = '200307'::text)
-> Index Scan using pk_supplier_price on
supplier_price ss (cost=0.00..4.90 rows=1 width=54)
Index Cond: ((ss.group_cde =
"outer".group_code) AND (ss.sku_mst_cde = "outer".sku) AND (ss.supplier_cde =
"outer".supplier_code))
-> Index Scan using master_sku_descr_idx3 on
master_sku_descr s (cost=0.00..2535.04 rows=10758 width=27)
Filter: (control_code = '0'::text)
-> Index Scan using idx_sku_price on sku_price sk
(cost=0.00..4.96 rows=1 width=44)
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=36.38..36.87 rows=198 width=62)
Sort Key: p.group_cde, p.branch_cde
-> Hash Join (cost=18.46..28.82 rows=198 width=62)
Hash Cond: ("outer".brn_code = "inner".branch_cde)
-> Merge Join (cost=13.94..20.34 rows=198 width=33)
Merge Cond: ("outer".country_code =
"inner".from_ctry)
-> Index Scan using master_branch_descr_idx4
on master_branch_descr b (cost=0.00..33.12 rows=198 width=15)
-> Sort (cost=13.94..13.95 rows=4 width=18)
Sort Key: f.from_ctry
-> Index Scan using forex_idx1 on forex
f (cost=0.00..13.90 rows=4 width=18)
Index Cond: ((to_ctry =
'ZAF'::text) AND (fpp_code = '200307'::text))
-> Hash (cost=4.02..4.02 rows=202 width=29)
-> Seq Scan on price_tmpl_det p
(cost=0.00..4.02 rows=202 width=29)
(34 rows)
Aggregate (cost=163.58..163.61 rows=1 width=699)
-> Group (cost=163.58..163.60 rows=1 width=699)
-> Sort (cost=163.58..163.58 rows=1 width=699)
Sort Key: m.group_code, m.sku, m.brn_code, m.stktype_code,
ss.supplier_price_curr_cost, sk.sku_price_curr_cost
-> Nested Loop (cost=115.56..163.57 rows=1 width=699)
Join Filter: (("outer".sku = "inner".sku) AND ("outer".group_code
= "inner".group_code))
-> Nested Loop (cost=115.56..157.53 rows=1 width=635)
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))
-> Nested Loop (cost=115.56..152.49 rows=1 width=517)
Join Filter: (("inner".group_cde = "outer".group_cde)
AND ("inner".price_tmpl_hdr_cde = "outer".price_tmpl_hdr_cde) AND
("inner".price_tmpl_hdr_reg = "outer".price_tmpl_hdr_reg))
-> Hash Join (cost=115.56..147.45 rows=1 width=367)
Hash Cond: ("outer".branch_cde =
"inner".brn_code)
Join Filter: ("inner".group_code =
"outer".group_cde)
-> Seq Scan on price_tmpl_det p
(cost=0.00..20.00 rows=1000 width=100)
-> Hash (cost=115.49..115.49 rows=25
width=267)
-> Nested Loop (cost=0.00..115.49
rows=25 width=267)
-> Nested Loop (cost=0.00..11.50
rows=1 width=115)
-> Index Scan using
forex_idx1 on forex f (cost=0.00..5.71 rows=1 width=51)
Index Cond: ((to_ctry =
'ZAF'::text) AND (fpp_code = '200307'::text))
-> Index Scan using
master_branch_descr_idx4 on master_branch_descr b (cost=0.00..5.78 rows=1 width=64)
Index Cond:
(b.country_code = "outer".from_ctry)
-> Index Scan using
master_fpp_values_uidx1 on master_fpp_values m (cost=0.00..103.68 rows=25 width=152)
Index Cond: ((m.fpp_code =
'200307'::text) AND (m.brn_code = "outer".brn_code))
-> Index Scan using pk_supplier_price on
supplier_price ss (cost=0.00..5.02 rows=1 width=150)
Index Cond: ((ss.group_cde =
"outer".group_code) AND (ss.sku_mst_cde = "outer".sku) AND (ss.supplier_cde =
"outer".supplier_code))
-> Index Scan using idx_sku_price on sku_price sk
(cost=0.00..5.02 rows=1 width=118)
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))
-> Index Scan using master_sku_descr_idx1 on master_sku_descr s
(cost=0.00..6.02 rows=1 width=64)
Index Cond: (("outer".group_cde = s.group_code) AND
("outer".sku_mst_cde = s.sku) AND (s.control_code = '0'::text))
(29 rows)
forex.sql
Description: Binary data
master_branch_descr.sql
Description: Binary data
master_fpp_values.sql
Description: Binary data
master_sku_descr.sql
Description: Binary data
Number of rows in master_fpp_values : count --------- 1012252 (1 row) Number of rows in master_sku_descr : count ------- 53219 (1 row) Number of rows in master_branch_descr : count ------- 198 (1 row) Number of rows in supplier_price : count ------- 54670 (1 row) Number of rows in price_tmpl_det : count ------- 202 (1 row) Number of rows in sku_price : count ------- 54670 (1 row) Number of rows in forex : count ------- 576 (1 row)
price_tmpl_det.sql
Description: Binary data
sku_price.sql
Description: Binary data
sql_statement.sql
Description: Binary data
supplier_price.sql
Description: Binary data
---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings
