Thread: query does not return after increasing range in 'between' clause
Hi, I am having bad luck with a query that should return zero rows but actually never returns (completes execution.)
When I broaden the ‘between’ clause range from " period.orderno between 1447 and 1450" to " period.orderno between 1446 and 1450” the query plan changes and the query never completes execution at least in the 30 to 40 minutes I have been willing to wait. I do see the query cost going up in the new plan but it seems odd that the execution time seems to increase to infinity.
Query plan that returns in milli-seconds, with "period.orderno between 1447 and 1450”:
QUERY PLAN
---------------------------------------------------------------------------------------------------------------
Nested Loop (cost=1.43..37834893.51 rows=560 width=221)
-> Nested Loop (cost=1.15..37834721.59 rows=509 width=186)
-> Nested Loop (cost=0.72..37672374.44 rows=343460 width=57)
-> Index Scan using pk_nb_periods on nb_periods period (cost=0.15..21.37 rows=3 width=8)
Filter: ((orderno >= 1447) AND (orderno <= 1450))
-> Index Scan using wk_f_p_idx on t_week_f fact (cost=0.57..12555979.74 rows=147129 width=49)
Index Cond: (period = period.period)
Filter: (store_tag = ANY ('{100,4480}'::integer[]))
-> Index Scan using pk_nb_products on nb_products product (cost=0.42..0.46 rows=1 width=144)
Index Cond: ((product_key)::text = (fact.upc)::text)
Filter: (((lvl)::text = 'UPC'::text) AND (category_tag = 'GRBYFD'::bpchar))
-> Index Scan using pk_nb_markets on nb_markets market (cost=0.28..0.30 rows=1 width=35)
Index Cond: (mkt_tag = fact.store_tag)
(13 rows)
Query plan that never completes with "period.orderno between 1446 and 1450”:
QUERY PLAN
----------------------------------------------------------------------------------------------------------------
Nested Loop (cost=1.43..40275248.26 rows=746 width=221)
-> Nested Loop (cost=1.15..40275019.26 rows=678 width=186)
Join Filter: (fact.period = period.period)
-> Nested Loop (cost=1.00..40271833.48 rows=52740 width=178)
-> Index Scan using prd_cat_idx on nb_products product (cost=0.42..4447.78 rows=900 width=144)
Index Cond: (category_tag = 'GRBYFD'::bpchar)
Filter: ((lvl)::text = 'UPC'::text)
-> Index Scan using wk_f_u_idx on t_week_f fact (cost=0.57..44736.52 rows=502 width=49)
Index Cond: ((upc)::text = (product.product_key)::text)
Filter: (store_tag = ANY ('{100,4480}'::integer[]))
-> Materialize (cost=0.15..21.39 rows=4 width=8)
-> Index Scan using pk_nb_periods on nb_periods period (cost=0.15..21.37 rows=4 width=8)
Filter: ((orderno >= 1446) AND (orderno <= 1450))
-> Index Scan using pk_nb_markets on nb_markets market (cost=0.28..0.30 rows=1 width=35)
Index Cond: (mkt_tag = fact.store_tag)
(15 rows)
On 08/27/2014 11:02 AM, Dennis wrote: > Hi, I am having bad luck with a query that should return zero rows but > actually never returns (completes execution.) > And the query is :) ? -- Adrian Klaver adrian.klaver@aklaver.com
Oh ya…
select fact.store_tag,fact.period,fact.upc upcid,
case when mkt_tag = 4480 then market.description else '' end retailmarket,
case when mkt_tag = 100 then market.description else '' end compmarket,
case when mkt_tag = 4480 then dollars else 0 end base_dollars,
case when mkt_tag = 100 then dollars else 0 end ref_dollars,
case when mkt_tag = 4480 then units else 0 end base_units,
case when mkt_tag = 100 then units else 0 end ref_units,
case when mkt_tag = 100 then acv else null end avg_acv,
case when mkt_tag = 4480 then b_dollars else 0 end base_base_dollars,
case when mkt_tag = 100 then b_dollars else 0 end ref_base_dollars,
case when mkt_tag = 4480 then b_units else 0 end base_base_units,
case when mkt_tag = 100 then b_units else 0 end ref_base_units,
product.lvl,product.upc,
product.description as description,
product.category,product.category_tag,
product.subcategory,
product.company,product.brand,
market.mkt_tag as mkt_tag,
period.period,
period.orderno
from
sg_dm_b.t_week_f fact,
sg_dm_b.nb_products product,
sg_dm_b.nb_markets market,
sg_dm_b.nb_periods period
where (product.product_key = fact.upc)
and (market.mkt_tag = fact.store_tag)
and (period.period = fact.period)
and fact.store_tag in(100,4480)
and product.lvl = 'UPC'
and product.category_tag = 'GRBYFD'
and period.orderno between 1446 and 1450;
— change to period.orderno between 1447 and 1450 gives the none material version of the plan.
On Aug 27, 2014, at 1:18 PM, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
On 08/27/2014 11:02 AM, Dennis wrote:Hi, I am having bad luck with a query that should return zero rows but
actually never returns (completes execution.)
And the query is :) ?
--
Adrian Klaver
adrian.klaver@aklaver.com
Dennis <dennisr@visi.com> writes:
> Hi, I am having bad luck with a query that should return zero rows but actually never returns (completes execution.)
Seems like you need a multicolumn index on t_week_f, and maybe also on
nb_products, so that the conditions shown as "filters" here could be
applied as index conditions instead.
I wonder also if your statistics are up to date --- are the rowcount
estimates shown in EXPLAIN reasonably sane?
regards, tom lane