Search Postgresql Archives

query does not return after increasing range in 'between' clause

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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)


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux