pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Analyze makes queries slow...
7+ messages / 3 participants
[nested] [flat]

* Analyze makes queries slow...
@ 2003-08-11 13:58  Stef <svb@ucs.co.za>
  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 15:43  Tom Lane <tgl@sss.pgh.pa.us>
  parent: 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 16:47  Stef <svb@ucs.co.za>
  parent: 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 18:25  Tom Lane <tgl@sss.pgh.pa.us>
  parent: 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 18:50  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Stef <svb@ucs.co.za>
  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-12 09:57  Jacek Rembisz <jr178783@zodiac.mimuw.edu.pl>
  parent: Stef <svb@ucs.co.za>
  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

* Re: Analyze makes queries slow...
@ 2003-08-12 17:30  Stef <svb@ucs.co.za>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  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


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