pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedAnalyze makes queries slow...
7+ messages / 3 participants
[nested] [flat]
* Analyze makes queries slow...
@ 2003-08-11 13:58 Stef <svb@ucs.co.za>
2003-08-11 15:43 ` Re: Analyze makes queries slow... Tom Lane <tgl@sss.pgh.pa.us>
2003-08-12 09:57 ` Re: Analyze makes queries slow... Jacek Rembisz <jr178783@zodiac.mimuw.edu.pl>
0 siblings, 2 replies; 7+ messages in thread
From: Stef @ 2003-08-11 13:58 UTC (permalink / raw)
To: pgsql-performance
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
Attachments:
explain_after.txt (text/plain, 3.4K) download
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)
explain_before.txt (text/plain, 3.4K) download
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 (application/octet-stream, 1.7K) download
master_branch_descr.sql (application/octet-stream, 5.4K) download
master_fpp_values.sql (application/octet-stream, 4.1K) download
master_sku_descr.sql (application/octet-stream, 3.7K) download
number_of_rows.txt (text/plain, 476B) download
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 (application/octet-stream, 583B) download
sku_price.sql (application/octet-stream, 2.8K) download
sql_statement.sql (application/octet-stream, 2.0K) download
supplier_price.sql (application/octet-stream, 1.3K) download
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Analyze makes queries slow...
2003-08-11 13:58 Analyze makes queries slow... Stef <svb@ucs.co.za>
@ 2003-08-11 15:43 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-08-11 16:47 ` Re: Analyze makes queries slow... Stef <svb@ucs.co.za>
1 sibling, 1 reply; 7+ messages in thread
From: Tom Lane @ 2003-08-11 15:43 UTC (permalink / raw)
To: Stef <svb@ucs.co.za>; +Cc: pgsql-performance
Stef <svb@ucs.co.za> writes:
> 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.
Could we see the results of "EXPLAIN ANALYZE", rather than just EXPLAIN,
for the un-analyzed case? I won't make you do it for the analyzed case ;-)
but when dealing with a plan-selection problem the planner's estimates
are obviously not to be trusted.
Also, what do you see in pg_stats (after analyzing) for each of the
tables used in the query?
And what PG version is this, exactly?
regards, tom lane
PS: in case you don't know this already, an easy way to get back to the
un-analyzed state is "DELETE FROM pg_statistics".
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Analyze makes queries slow...
2003-08-11 13:58 Analyze makes queries slow... Stef <svb@ucs.co.za>
2003-08-11 15:43 ` Re: Analyze makes queries slow... Tom Lane <tgl@sss.pgh.pa.us>
@ 2003-08-11 16:47 ` Stef <svb@ucs.co.za>
2003-08-11 18:25 ` Re: Analyze makes queries slow... Tom Lane <tgl@sss.pgh.pa.us>
2003-08-11 18:50 ` Re: Analyze makes queries slow... Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 7+ messages in thread
From: Stef @ 2003-08-11 16:47 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance
Hi Tom,
Thanks for responding.
I got as much info as I could :
On Mon, 11 Aug 2003 11:43:45 -0400
Tom Lane <tgl@sss.pgh.pa.us> 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
Attachments:
explain_analyze.txt (text/plain, 4.1K) download
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)
pg_stats.csv (text/comma-separated-values, 11.0K) download
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Analyze makes queries slow...
2003-08-11 13:58 Analyze makes queries slow... Stef <svb@ucs.co.za>
2003-08-11 15:43 ` Re: Analyze makes queries slow... Tom Lane <tgl@sss.pgh.pa.us>
2003-08-11 16:47 ` Re: Analyze makes queries slow... Stef <svb@ucs.co.za>
@ 2003-08-11 18:25 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-08-12 17:30 ` Re: Analyze makes queries slow... Stef <svb@ucs.co.za>
1 sibling, 1 reply; 7+ messages in thread
From: Tom Lane @ 2003-08-11 18:25 UTC (permalink / raw)
To: Stef <svb@ucs.co.za>; +Cc: pgsql-performance
Stef <svb@ucs.co.za> writes:
> => Could we see the results of "EXPLAIN ANALYZE", rather than just EXPLAIN,
> => for the un-analyzed case?
> Attached the output of this.
Hmm... not immediately obvious where it's going wrong. Could you try
this (after ANALYZE):
set enable_mergejoin to off;
explain analyze ... query ...
If it finishes in a reasonable amount of time, send the explain output.
regards, tom lane
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Analyze makes queries slow...
2003-08-11 13:58 Analyze makes queries slow... Stef <svb@ucs.co.za>
2003-08-11 15:43 ` Re: Analyze makes queries slow... Tom Lane <tgl@sss.pgh.pa.us>
2003-08-11 16:47 ` Re: Analyze makes queries slow... Stef <svb@ucs.co.za>
2003-08-11 18:25 ` Re: Analyze makes queries slow... Tom Lane <tgl@sss.pgh.pa.us>
@ 2003-08-12 17:30 ` Stef <svb@ucs.co.za>
0 siblings, 0 replies; 7+ messages in thread
From: Stef @ 2003-08-12 17:30 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance
On Mon, 11 Aug 2003 14:25:03 -0400
Tom Lane <tgl@sss.pgh.pa.us> 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.
Attachments:
expl_anlyze_nomergejoin_7.3.1.txt (text/plain, 4.8K) download
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)
expl_anlyze_nomergejoin_7.3.4.txt (text/plain, 5.2K) download
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)
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Analyze makes queries slow...
2003-08-11 13:58 Analyze makes queries slow... Stef <svb@ucs.co.za>
2003-08-11 15:43 ` Re: Analyze makes queries slow... Tom Lane <tgl@sss.pgh.pa.us>
2003-08-11 16:47 ` Re: Analyze makes queries slow... Stef <svb@ucs.co.za>
@ 2003-08-11 18:50 ` Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 7+ messages in thread
From: Tom Lane @ 2003-08-11 18:50 UTC (permalink / raw)
To: Stef <svb@ucs.co.za>; +Cc: pgsql-performance
Stef <svb@ucs.co.za> writes:
> => And what PG version is this, exactly?
> PostgreSQL 7.3.1
Ah, I think I see it: you are getting burnt by a mergejoin estimation
bug that was fixed in 7.3.2. Please update (you might as well go to
7.3.4 while you're at it) and see if the results improve.
regards, tom lane
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Analyze makes queries slow...
2003-08-11 13:58 Analyze makes queries slow... Stef <svb@ucs.co.za>
@ 2003-08-12 09:57 ` Jacek Rembisz <jr178783@zodiac.mimuw.edu.pl>
1 sibling, 0 replies; 7+ messages in thread
From: Jacek Rembisz @ 2003-08-12 09:57 UTC (permalink / raw)
To: pgsql-performance
On Mon, Aug 11, 2003 at 03:58:41PM +0200, Stef wrote:
> 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.
Hmm, I have noticed similar problem with a query with order by ... limit clause.Although it runs only 10 times slower after analyze :)
The query joins one big table (20 000 rows) with several small tables
(200-4000 rows) than order by "primary key of big table" limit 20
Without this order by ... limit clause the query is 5 times faster after
analyze.
Looking into explain analyze outputs:
1. Before vacuum analyze a planer chooses nested loop, the deepest is:
-> Nested Loop (cost=0.00..116866.54 rows=19286 width=96) (actual time=0.14..1.39 rows=21 loops=1)
-> Index Scan Backward using big_table_pkey on big_table k (cost=0.00..1461.15 rows=19286 width=52) (actual time=0.07..0.47 rows=21 loops=1)
-> Index Scan using 4000rows_table_pkey on 4000rows_table zs (cost=0.00..5.97 rows=1 width=44) (actual time=0.02..0.02 rows=0 loops=21)
2. After analyze uses hashjoins
When I remove this order by limit clause the query after analyze takes
the same time and the query before analyze is much more slower.
I won't blame the planer. How he could learn that he should first
take those 20 rows and than perform joins? There is a where clause
with complex exists(subquery) condition regarding one of big_table fields,
but removing this condition does not change the query plan.
Pure joining without any additional conditions and only primary key of big
table in select clause runs 4 times slower then whole query before
vacuuum analyze :)
Does in all the planer take in the consideration the limit clause?
Probably I'm missing something. I don't know much about the planer.
Finaly I have redesigned the query.
Regards,
Jacek
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2003-08-12 17:30 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-08-11 13:58 Analyze makes queries slow... Stef <svb@ucs.co.za>
2003-08-11 15:43 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-08-11 16:47 ` Stef <svb@ucs.co.za>
2003-08-11 18:25 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-08-12 17:30 ` Stef <svb@ucs.co.za>
2003-08-11 18:50 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-08-12 09:57 ` Jacek Rembisz <jr178783@zodiac.mimuw.edu.pl>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox