From: Stef <svb@ucs.co.za>
To: pgsql-performance@postgresql.org
Subject: Analyze makes queries slow...
Date: Mon, 11 Aug 2003 15:58:41 +0200
Message-ID: <20030811155841.483ffd93.svb@ucs.co.za> (raw)
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) downloadnumber_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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-performance@postgresql.org
Cc: svb@ucs.co.za
Subject: Re: Analyze makes queries slow...
In-Reply-To: <20030811155841.483ffd93.svb@ucs.co.za>
References: <20030811155841.483ffd93.svb@ucs.co.za>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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