| 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) |
