query does not return after increasing range in 'between' clause - Mailing list pgsql-general
| From | Dennis |
|---|---|
| Subject | query does not return after increasing range in 'between' clause |
| Date | |
| Msg-id | E283F669-5717-4CD5-A716-9F6809D285CA@visi.com Whole thread Raw |
| Responses |
Re: query does not return after increasing range in 'between'
clause
(Adrian Klaver <adrian.klaver@aklaver.com>)
Re: query does not return after increasing range in 'between' clause (Tom Lane <tgl@sss.pgh.pa.us>) |
| List | pgsql-general |
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)
pgsql-general by date: