Hi Tom,
Thanks for responding.
I got as much info as I could :
On Mon, 11 Aug 2003 11:43:45 -0400
Tom Lane <[EMAIL PROTECTED]> wrote:
=> Could we see the results of "EXPLAIN ANALYZE", rather than just EXPLAIN,
=> for the un-analyzed case?
Attached the output of this.
=> Also, what do you see in pg_stats (after analyzing) for each of the
=> tables used in the query?
I attached a file in csv format of pg_stats after analyzing.
(With the columns selected on the top line)
It looks like cached values for (quite a lot of?) the table columns.
I would assume it stores the most commonly selected
values for every column with an index. Don't know if I'm correct.
=> And what PG version is this, exactly?
PostgreSQL 7.3.1
Kind regards
Stefan
Aggregate (cost=187.80..187.84 rows=1 width=699) (actual time=142704.64..148066.77
rows=125769 loops=1)
-> Group (cost=187.80..187.82 rows=1 width=699) (actual time=142704.48..144239.11
rows=125769 loops=1)
-> Sort (cost=187.80..187.81 rows=1 width=699) (actual
time=142704.45..142947.14 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
-> Nested Loop (cost=140.07..187.79 rows=1 width=699) (actual
time=49796.26..135679.87 rows=125769 loops=1)
Join Filter: (("outer".sku = "inner".sku) AND ("outer".group_code
= "inner".group_code))
-> Nested Loop (cost=140.07..181.76 rows=1 width=635) (actual
time=49742.50..118086.42 rows=125769 loops=1)
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) AND ("inner".supplier_cde =
"outer".supplier_code))
-> Nested Loop (cost=140.07..176.91 rows=1 width=485)
(actual time=49741.95..90991.39 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))
-> Hash Join (cost=140.07..172.07 rows=1 width=367)
(actual time=49741.16..52345.71 rows=162115 loops=1)
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) (actual time=0.09..9.50 rows=202 loops=1)
-> Hash (cost=140.00..140.00 rows=27
width=267) (actual time=49740.97..49740.97 rows=0 loops=1)
-> Nested Loop (cost=0.00..140.00
rows=27 width=267) (actual time=432.55..49360.23 rows=162115 loops=1)
-> Nested Loop (cost=0.00..30.79
rows=1 width=115) (actual time=154.19..184.03 rows=198 loops=1)
-> Seq Scan on forex f
(cost=0.00..25.00 rows=1 width=51) (actual time=50.86..51.12 rows=4 loops=1)
Filter: ((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)
(actual time=25.90..32.81 rows=50 loops=4)
Index Cond:
(b.country_code = "outer".from_ctry)
-> Index Scan using
master_fpp_values_uidx1 on master_fpp_values m (cost=0.00..108.88 rows=27 width=152)
(actual time=3.41..243.55 rows=819 loops=198)
Index Cond: ((m.fpp_code =
'200307'::text) AND (m.brn_code = "outer".brn_code))
-> Index Scan using idx_sku_price on sku_price sk
(cost=0.00..4.83 rows=1 width=118) (actual time=0.09..0.21 rows=4 loops=162115)
Index Cond: ((sk.group_cde =
"outer".group_code) AND (sk.sku_mst_cde = "outer".sku))
-> Index Scan using idx_supplier_price on supplier_price
ss (cost=0.00..4.83 rows=1 width=150) (actual time=0.11..0.16 rows=5 loops=125769)
Index Cond: (("outer".group_cde = ss.group_cde) AND
("outer".sku_mst_cde = ss.sku_mst_cde))
-> Index Scan using master_sku_descr_idx1 on master_sku_descr s
(cost=0.00..6.02 rows=1 width=64) (actual time=0.12..0.12 rows=1 loops=125769)
Index Cond: (("outer".group_cde = s.group_code) AND
("outer".sku_mst_cde = s.sku) AND (s.control_code = '0'::text))
Total runtime: 148710.78 msec
(30 rows)
schemaname,tablename,attname,null_frac,avg_width,n_distinct,most_common_freqs,correlation
public,master_fpp_values,fpp_code,0,10,7,{0.197667\,0.151\,0.143333\,0.136333\,0.131\,0.123333\,0.117333},0.881058
public,master_fpp_values,brn_code,0,8,199,{0.0306667\,0.025\,0.02\,0.017\,0.015\,0.0146667\,0.0143333\,0.0143333\,0.014\,0.0133333},-0.305321
public,master_fpp_values,sku,0,12,2961,{0.00366667\,0.00333333\,0.003\,0.003\,0.003\,0.003\,0.00266667\,0.00266667\,0.00266667\,0.00266667},-0.1541
public,master_fpp_values,group_code,0,9,1,{1},1
public,master_fpp_values,stktype_code,0,6,10,{0.405\,0.376667\,0.103333\,0.046\,0.0286667\,0.0226667\,0.00633333\,0.006\,0.00466667\,0.000666667},0.299429
public,master_fpp_values,supplier_code,0,9,145,{0.0496667\,0.0496667\,0.049\,0.0433333\,0.0406667\,0.032\,0.03\,0.0283333\,0.0253333\,0.025},-0.00397595
public,master_fpp_values,mth_sls_qty_01,0,10,12,{0.923667},0.887183
public,master_fpp_values,mth_sls_qty_02,0,10,14,{0.921333},0.869282
public,master_fpp_values,mth_sls_qty_03,0,10,12,{0.930333},0.873398
public,master_fpp_values,mth_sls_qty_04,0,10,12,{0.939333},0.899521
public,master_fpp_values,mth_sls_qty_05,0,10,16,{0.932333},0.884406
public,master_fpp_values,mth_sls_qty_06,0,10,14,{0.928333},0.86523
public,master_fpp_values,mth_sls_qty_07,0,10,16,{0.931667},0.870748
public,master_fpp_values,mth_sls_qty_08,0,10,17,{0.939333},0.898058
public,master_fpp_values,mth_sls_qty_09,0,10,16,{0.940667},0.904561
public,master_fpp_values,mth_sls_qty_10,0,10,11,{0.943},0.877744
public,master_fpp_values,mth_sls_qty_11,0,10,7,{0.940667\,0.046\,0.00666667\,0.00266667\,0.00233333\,0.001\,0.000666667},0.889018
public,master_fpp_values,mth_sls_qty_12,0,10,10,{0.945},0.890438
public,master_fpp_values,qty_on_hand_01,0,10,27,{0.748333\,0.162\,0.0496667},0.665886
public,master_fpp_values,qty_on_hand_02,0,10,23,{0.753667\,0.159667},0.657389
public,master_fpp_values,qty_on_hand_03,0,10,21,{0.765\,0.152},0.629485
public,master_fpp_values,qty_on_hand_04,0,10,25,{0.759333\,0.153},0.594277
public,master_fpp_values,qty_on_hand_05,0,10,27,{0.765\,0.149333},0.619
public,master_fpp_values,qty_on_hand_06,0,10,27,{0.764\,0.151333},0.61393
public,master_fpp_values,qty_on_hand_07,0,10,25,{0.761\,0.153333},0.620102
public,master_fpp_values,qty_on_hand_08,0,10,25,{0.766\,0.153667},0.624114
public,master_fpp_values,qty_on_hand_09,0,10,27,{0.772\,0.148333},0.629625
public,master_fpp_values,qty_on_hand_10,0,10,29,{0.783333\,0.141333},0.625353
public,master_fpp_values,qty_on_hand_11,0,10,22,{0.785\,0.146667},0.590573
public,master_fpp_values,qty_on_hand_12,0,10,20,{0.778333\,0.154667},0.565559
public,master_fpp_values,mth_sls_cst,0,10,181,{0.925667},0.889698
public,master_fpp_values,mth_sls_disc,0,10,78,{0.956},0.891678
public,master_fpp_values,mth_sls_nett_excl,0,10,147,{0.925667},0.888023
public,master_fpp_values,mth_qty_on_hand_cst,0,10,512,{0.755\,0.00266667},0.641361
public,master_fpp_values,mth_sls_qty_und,0,10,5,{0.986333},0.979298
public,master_fpp_values,qty_in_goods,0,10,16,{0.947667},0.905277
public,master_fpp_values,mat_sls_qty,0,10,48,{0.648667\,0.177333\,0.05\,0.0316667},0.44792
public,master_fpp_values,ytd_sls_qty,0,10,21,{0.858667\,0.0796667},0.866826
public,master_fpp_values,mat_sls_cst,0,10,1100,{0.651333\,0.00266667\,0.00233333\,0.002\,0.002\,0.002\,0.002\,0.002\,0.00166667\,0.00166667},0.419433
public,master_fpp_values,ytd_sls_cst,0,10,355,{0.86},0.861035
public,master_fpp_values,mat_sls_disc,0,10,452,{0.807333\,0.007\,0.00666667\,0.00433333\,0.004\,0.004\,0.004\,0.00366667},0.646366
public,master_fpp_values,ytd_sls_disc,0,10,167,{0.913667},0.756582
public,master_fpp_values,mat_sls_nett_excl,0,11,838,{0.659333\,0.00633333\,0.00566667\,0.00533333\,0.004\,0.00366667\,0.00333333\,0.003\,0.003\,0.003},0.428129
public,master_fpp_values,ytd_sls_nett_excl,0,10,300,{0.863333\,0.005},0.860931
public,master_fpp_values,ly_mth_sls_nett_excl,0,10,129,{0.946333},0.883056
public,master_fpp_values,ly_ytd_sls_nett_excl,0,10,345,{0.856333\,0.00466667},0.792166
public,master_fpp_values,stk_turn_qty_on_hand_cst,0,11,1906,{0.546667\,0.00233333\,0.00133333\,0.00133333\,0.00133333\,0.00133333\,0.00133333\,0.00133333\,0.00133333\,0.001},0.277605
public,master_fpp_values,current_orders,0.964333,4,1,{0.0356667},1
public,master_fpp_values,overdue_orders,0.964333,4,1,{0.0356667},1
public,master_fpp_values,no_mths_history,0,4,12,{0.382\,0.136\,0.100667},0.165119
public,master_sku_descr,group_code,0,9,1,{1},1
public,master_sku_descr,sku,0,18,-1,\N,-0.17559
public,master_sku_descr,sku_descr,0,27,2532,{0.0176667\,0.015\,0.0136667\,0.009\,0.008\,0.00766667\,0.00733333\,0.007\,0.007\,0.00666667},0.0381189
public,master_sku_descr,dep_code,0,7,19,{0.182333\,0.140667\,0.130667\,0.094\,0.0873333\,0.0696667},0.218586
public,master_sku_descr,dep_descr,0,13,19,{0.182333\,0.140667\,0.130667\,0.094\,0.0873333\,0.0696667},0.100323
public,master_sku_descr,cat_code,0,9,48,{0.181\,0.117\,0.0733333\,0.065\,0.0616667\,0.049\,0.043\,0.0423333\,0.038\,0.0286667},0.182654
public,master_sku_descr,cat_descr,0,14,45,{0.181\,0.117\,0.0733333\,0.065\,0.0616667\,0.049\,0.043\,0.0423333\,0.038\,0.0286667},0.0820663
public,master_sku_descr,subcat_code,0,13,67,{0.175\,0.117\,0.0733333\,0.065\,0.0556667\,0.0486667\,0.043\,0.0353333\,0.0286667\,0.026},0.17828
public,master_sku_descr,subcat_descr,0,16,67,{0.175\,0.117\,0.0733333\,0.065\,0.0556667\,0.0486667\,0.043\,0.0353333\,0.0286667\,0.026},0.0220006
public,master_sku_descr,control_code,0,5,2,{0.812667\,0.187333},1
public,master_sku_descr,control_descr,0,20,2,{0.812667\,0.187333},0.0865626
public,master_sku_descr,supplier_code,0,9,167,{0.182667\,0.0503333\,0.0473333\,0.0373333\,0.0336667\,0.0323333\,0.0323333\,0.03\,0.0296667\,0.026},-0.0817026
public,master_sku_descr,supplier_descr,0,27,163,{0.182667\,0.0503333\,0.0473333\,0.0373333\,0.0336667\,0.0323333\,0.0323333\,0.03\,0.0296667\,0.027},0.184701
public,master_branch_descr,brn_code,0,8,-1,\N,0.0902227
public,master_branch_descr,brn_descr,0,23,-0.99495,{0.010101},-0.0104298
public,master_branch_descr,country_code,0,7,4,{0.883838\,0.0757576\,0.020202\,0.020202},0.713787
public,master_branch_descr,country_descr,0,15,4,{0.883838\,0.0757576\,0.020202\,0.020202},0.713787
public,master_branch_descr,cluster_code,0,8,15,{0.136364\,0.126263\,0.106061\,0.10101\,0.0959596},0.0364343
public,master_branch_descr,cluster_descr,0,23,15,{0.136364\,0.126263\,0.106061\,0.10101\,0.0959596},-0.220806
public,master_branch_descr,brand_code,0,11,2,{0.873737\,0.126263},0.715874
public,master_branch_descr,brand_descr,0,18,2,{0.873737\,0.126263},0.828365
public,master_branch_descr,chain_code,0,9,1,{1},1
public,master_branch_descr,chain_descr,0,17,1,{1},1
public,master_branch_descr,sbu_code,0,7,1,{1},1
public,master_branch_descr,sbu_descr,0,15,1,{1},1
public,master_branch_descr,group_code,0,5,1,{1},1
public,master_branch_descr,group_descr,0,11,1,{1},1
public,master_branch_descr,region_code,0,15,-0.222222,{0.0757576\,0.050505\,0.040404\,0.0353535\,0.0353535\,0.0353535\,0.0353535\,0.0353535\,0.0353535\,0.0353535},-0.270643
public,master_branch_descr,region_descr,0,22,-0.217172,{0.0757576\,0.050505\,0.040404\,0.0353535\,0.0353535\,0.0353535\,0.0353535\,0.0353535\,0.0353535\,0.0353535},-0.155933
public,master_branch_descr,division_code,0,9,7,{0.257576\,0.247475\,0.222222\,0.156566\,0.10101},-0.0911781
public,master_branch_descr,division_descr,0,21,7,{0.257576\,0.247475\,0.222222\,0.156566\,0.10101},0.159081
public,supplier_price,group_cde,0,9,1,{1},1
public,supplier_price,sku_mst_cde,0,11,-0.189704,{0.001\,0.001\,0.001\,0.001\,0.001\,0.001\,0.001\,0.001\,0.001\,0.001},0.425292
public,supplier_price,supplier_cde,0,10,203,{0.073\,0.0446667\,0.0416667\,0.0416667\,0.0306667\,0.026\,0.024\,0.023\,0.023\,0.019},0.151589
public,supplier_price,price_tmpl_hdr_cde,0,4,1,{1},1
public,supplier_price,price_tmpl_hdr_reg,0,8,5,{0.231\,0.207333\,0.191667\,0.185\,0.185},0.211032
public,supplier_price,supplier_sku_cde,0,4,19,{0.989},0.990141
public,supplier_price,supplier_sku_barcde,0,4,1,{1},1
public,supplier_price,supplier_price_leadtm_days,0,4,14,{0.884333},0.815372
public,supplier_price,supplier_price_curr_cost,0,12,2285,{0.00866667\,0.00866667\,0.008\,0.00766667\,0.00533333\,0.00533333\,0.005\,0.005\,0.00466667\,0.00466667},-0.123537
public,supplier_price,supplier_price_cost_dte_efctv,0,8,1407,{0.103\,0.0113333\,0.00766667\,0.00733333\,0.00733333\,0.007\,0.00633333\,0.006\,0.006\,0.00566667},0.200601
public,supplier_price,supplier_price_dte_created,0,8,1718,{0.103\,0.0313333\,0.0163333\,0.0136667\,0.013\,0.009\,0.009\,0.00733333\,0.00633333\,0.00633333},0.236249
public,supplier_price,audit_id,0,4,448,{0.734\,0.011\,0.00766667\,0.00733333\,0.00666667\,0.00666667\,0.00566667\,0.00533333\,0.005\,0.00466667},0.624155
public,forex,from_ctry,0,7,4,{0.25\,0.25\,0.25\,0.25},0.22902
public,forex,to_ctry,0,7,4,{0.25\,0.25\,0.25\,0.25},0.24478
public,forex,fpp_code,0,10,36,\N,0.555554
public,forex,descr,0,4,1,{1},1
public,forex,is_active,0,1,1,{1},1
public,forex,seq,0,4,36,\N,0.555554
public,forex,rate,0,11,3,{0.625\,0.1875\,0.1875},0.470405
public,price_tmpl_det,group_cde,0,9,1,{1},1
public,price_tmpl_det,price_tmpl_hdr_cde,0,4,1,{1},1
public,price_tmpl_det,price_tmpl_hdr_reg,0,8,5,{0.762376\,0.123762\,0.0742574\,0.019802\,0.019802},0.386344
public,price_tmpl_det,branch_cde,0,8,-1,\N,0.951216
public,price_tmpl_det,audit_id,0,4,1,{1},1
public,sku_price,group_cde,0,9,1,{1},1
public,sku_price,sku_mst_cde,0,11,-0.203542,{0.001\,0.001\,0.001\,0.001\,0.001\,0.001\,0.001\,0.001\,0.001\,0.001},0.321261
public,sku_price,price_tmpl_hdr_cde,0,4,1,{1},1
public,sku_price,price_tmpl_hdr_reg,0,8,5,{0.211\,0.201667\,0.2\,0.2\,0.187333},-0.0265109
public,sku_price,sku_price_curr_cost,0,12,3507,{0.007\,0.006\,0.00466667\,0.00433333\,0.004\,0.004\,0.004\,0.00366667\,0.00366667\,0.00333333},-0.10889
public,sku_price,sku_price_stdcst_attr,0,24,2,{0.716667\,0.283333},0.484316
public,sku_price,sku_price_misc_attr,0,24,10,{0.742333\,0.208333},0.593248
public,sku_price,sku_price_cost_dte_efctv,0,8,468,{0.340333\,0.171667\,0.036\,0.0213333\,0.0153333\,0.0143333\,0.0126667\,0.012\,0.009\,0.00833333},0.0142753
public,sku_price,sku_price_curr_rsp_ca,0,12,521,{0.0266667\,0.0236667\,0.0233333\,0.0223333\,0.0216667\,0.0193333\,0.018\,0.0176667\,0.0173333\,0.016},-0.0683204
public,sku_price,sku_price_curr_rsp_cr,0,12,521,{0.0266667\,0.0236667\,0.0233333\,0.0223333\,0.0216667\,0.0193333\,0.018\,0.0176667\,0.0173333\,0.016},-0.0683643
public,sku_price,sku_price_rsp_dte_efctv,0,8,468,{0.340333\,0.171667\,0.036\,0.0213333\,0.0153333\,0.0143333\,0.0126667\,0.012\,0.009\,0.00833333},0.0142753
public,sku_price,sku_price_curr_tax_cde,0,5,3,{0.580667\,0.232\,0.187333},0.556641
public,sku_price,sku_price_tax_dte_efctv,0,8,416,{0.253667\,0.171667\,0.151333\,0.0273333\,0.016\,0.0153333\,0.0143333\,0.0126667\,0.012\,0.009},0.0858765
public,sku_price,sku_price_curr_stk_typ,0,6,7,{0.519333\,0.230667\,0.142},0.383167
public,sku_price,sku_price_stk_dte_efctv,0,8,468,{0.340333\,0.171667\,0.036\,0.0213333\,0.0153333\,0.0143333\,0.0126667\,0.012\,0.009\,0.00833333},0.0142753
public,sku_price,audit_id,0,4,392,{0.55\,0.171333\,0.0153333\,0.0136667\,0.0126667\,0.012\,0.00833333\,0.008\,0.00766667\,0.006},0.175806
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
