在’2012-01-01’和’2012-01-04’之间
它的速度较慢,即使它的记录设置较小,如果我这样做的话
‘2012-01-01’与’2012-04-17’之间
可以告诉我为什么会这样吗？
这是较慢的查询：
"HashAggregate (cost=9388.82..9388.88 rows=1 wIDth=311) (actual time=187787.183..187788.001 rows=153 loops=1)"" -> nested Loop (cost=9266.68..9388.77 rows=1 wIDth=311) (actual time=8432.315..187785.091 rows=153 loops=1)"" Join Filter: ((oh.ord_ID = ord_line.ord_ID) AND (IIS.item_ID = ord_line.item_ID))"" -> nested Loop left Join (cost=9266.68..9373.81 rows=1 wIDth=333) (actual time=92.238..147161.052 rows=5847933 loops=1)"" Join Filter: ((oh.division)::text = (item_color_cd.division)::text)"" -> nested Loop left Join (cost=9266.68..9373.53 rows=1 wIDth=322) (actual time=92.214..113058.202 rows=5847933 loops=1)"" Join Filter: ((oh.division)::text = (item_size_cd.division)::text)"" -> nested Loop left Join (cost=9266.68..9373.24 rows=1 wIDth=312) (actual time=92.189..77932.977 rows=5847933 loops=1)"" Join Filter: ((oh.division)::text = (item_class_cd.division)::text)"" -> nested Loop (cost=9266.68..9372.92 rows=1 wIDth=294) (actual time=92.183..61183.081 rows=5847933 loops=1)"" -> nested Loop (cost=67.45..91.07 rows=1 wIDth=75) (actual time=0.819..58.193 rows=8463 loops=1)"" -> nested Loop (cost=67.45..75.24 rows=1 wIDth=51) (actual time=0.784..3.894 rows=360 loops=1)"" -> Index Scan using item_dept_cd_division_key on item_dept_cd (cost=0.00..5.27 rows=1 wIDth=27) (actual time=0.045..0.047 rows=1 loops=1)"" Index Cond: (((division)::text = 'BSM'::text) AND ((dept_cd)::text = 'S'::text))"" Filter: ((dept_cd)::text ~~ 'S'::text)"" -> Bitmap Heap Scan on item_item_num iin (cost=67.45..69.96 rows=1 wIDth=32) (actual time=0.734..3.315 rows=360 loops=1)"" Recheck Cond: ((iin.dept_cd_ID = item_dept_cd.dept_cd_ID) AND ((iin.division)::text = 'BSM'::text))"" -> BitmapAnd (cost=67.45..67.45 rows=1 wIDth=0) (actual time=0.687..0.687 rows=0 loops=1)"" -> Bitmap Index Scan on item_item_num_dept_ID (cost=0.00..6.92 rows=213 wIDth=0) (actual time=0.109..0.109 rows=364 loops=1)"" Index Cond: (iin.dept_cd_ID = item_dept_cd.dept_cd_ID)"" -> Bitmap Index Scan on item_item_num_division_key (cost=0.00..59.64 rows=2573 wIDth=0) (actual time=0.544..0.544 rows=2753 loops=1)"" Index Cond: ((iin.division)::text = 'BSM'::text)"" -> Index Scan using item_sku_item_num_ID_key on item_sku IIS (cost=0.00..15.82 rows=1 wIDth=32) (actual time=0.021..0.104 rows=24 loops=360)"" Index Cond: (IIS.item_num_ID = iin.item_num_ID)"" Filter: ((IIS.division)::text = 'BSM'::text)"" -> HashAggregate (cost=9199.23..9281.69 rows=8 wIDth=66) (actual time=0.020..6.298 rows=691 loops=8463)"" -> nested Loop (cost=0.01..9198.91 rows=8 wIDth=66) (actual time=1.111..89.484 rows=691 loops=1)"" -> Index Scan using ledger_demand_ix4 on ledger_demand ls (cost=0.01..4512.71 rows=710 wIDth=35) (actual time=1.078..79.178 rows=691 loops=1)"" Index Cond: ((date(trans_date) >= '2012-01-01'::date) AND (date(trans_date) <= '2012-01-04'::date))"" Filter: (((division)::text = 'BSM'::text) AND (((qty_ordered > 0) AND ((ledger_code)::text ~~ 'D%'::text)) OR ((ledger_code)::text ~~ 'DC%'::text)))"" -> Index Scan using ord_header_pkey on ord_header oh (cost=0.00..6.59 rows=1 wIDth=39) (actual time=0.010..0.011 rows=1 loops=691)"" Index Cond: (oh.ord_ID = ls.ord_ID)"" Filter: ((oh.division)::text = 'BSM'::text)"" SubPlan 1"" -> Aggregate (cost=10.25..10.26 rows=1 wIDth=8) (actual time=0.005..0.006 rows=1 loops=5847933)"" -> Index Scan using ledger_discount_ord_line_ID on ledger_discount (cost=0.00..10.25 rows=1 wIDth=8) (actual time=0.003..0.004 rows=0 loops=5847933)"" Index Cond: (ord_line_ID = )"" Filter: (((ledger_code)::text ~~ 'D%'::text) AND ((division)::text = ("HashAggregate (cost=395482.02..395482.08 rows=1 wIDth=311) (actual time=3176.868..3204.741 rows=4900 loops=1)"" -> nested Loop left Join (cost=391021.12..395481.97 rows=1 wIDth=311) (actual time=2074.121..3151.576 rows=5118 loops=1)"" Join Filter: ((oh.division)::text = (item_color_cd.division)::text)"" -> nested Loop left Join (cost=391021.12..395481.69 rows=1 wIDth=314) (actual time=2074.086..3113.537 rows=5118 loops=1)"" Join Filter: ((oh.division)::text = (item_size_cd.division)::text)"" -> nested Loop left Join (cost=391021.12..395481.40 rows=1 wIDth=304) (actual time=2074.067..3077.820 rows=5118 loops=1)"" Join Filter: ((oh.division)::text = (item_class_cd.division)::text)"" -> Hash Join (cost=391021.12..395481.11 rows=1 wIDth=286) (actual time=2074.062..3061.180 rows=5118 loops=1)"" Hash Cond: ((oh.ord_ID = ord_line.ord_ID) AND (ls.ord_line_ID = ord_line.ord_line_ID))"" -> HashAggregate (cost=390858.20..395310.62 rows=432 wIDth=66) (actual time=1089.300..1596.212 rows=38947 loops=1)"" -> nested Loop (cost=92108.27..390840.92 rows=432 wIDth=66) (actual time=603.101..1004.552 rows=38947 loops=1)"" -> Bitmap Heap Scan on ledger_demand ls (cost=92108.27..174982.73 rows=36833 wIDth=35) (actual time=603.076..708.321 rows=38947 loops=1)"" Recheck Cond: (((division)::text = 'BSM'::text) AND (date(trans_date) >= '2012-01-01'::date) AND (date(trans_date) <= '2012-04-17'::date))"" Filter: (((qty_ordered > 0) AND ((ledger_code)::text ~~ 'D%'::text)) OR ((ledger_code)::text ~~ 'DC%'::text))"" -> BitmapAnd (cost=92108.27..92108.27 rows=37705 wIDth=0) (actual time=600.722..600.722 rows=0 loops=1)"" -> Bitmap Index Scan on ledger_demand_ix2 (cost=0.00..12712.18 rows=559935 wIDth=0) (actual time=91.763..91.763 rows=572985 loops=1)"" Index Cond: ((division)::text = 'BSM'::text)"" -> Bitmap Index Scan on ledger_demand_ix4 (cost=0.00..79377.42 rows=4397941 wIDth=0) (actual time=483.385..483.385 rows=4444914 loops=1)"" Index Cond: ((date(trans_date) >= '2012-01-01'::date) AND (date(trans_date) <= '2012-04-17'::date))"" -> Index Scan using ord_header_pkey on ord_header oh (cost=0.00..5.85 rows=1 wIDth=39) (actual time=0.004..0.005 rows=1 loops=38947)"" Index Cond: (oh.ord_ID = ls.ord_ID)"" Filter: ((oh.division)::text = 'BSM'::text)"" SubPlan 1"" -> Aggregate (cost=10.25..10.26 rows=1 wIDth=8) (actual time=0.009..0.009 rows=1 loops=38947)"" -> Index Scan using ledger_discount_ord_line_ID on ledger_discount (cost=0.00..10.25 rows=1 wIDth=8) (actual time=0.007..0.007 rows=0 loops=38947)"" Index Cond: (ord_line_ID = )"" Filter: (((ledger_code)::text ~~ 'D%'::text) AND ((division)::text = ()::text) AND (ord_ID = ) AND (date(trans_date) = date()))"" -> Index Scan using item_class_cd_pkey on item_class_cd (cost=0.00..0.30 rows=1 wIDth=31) (actual time=0.001..0.001 rows=0 loops=5847933)"" Index Cond: (iin.class_cd_ID = item_class_cd.class_cd_ID)"" Filter: ((item_class_cd.division)::text = 'BSM'::text)"" -> Index Scan using item_size_cd_pkey on item_size_cd (cost=0.00..0.27 rows=1 wIDth=25) (actual time=0.002..0.003 rows=1 loops=5847933)"" Index Cond: (IIS.size_cd_ID = item_size_cd.size_cd_ID)"" Filter: ((item_size_cd.division)::text = 'BSM'::text)"" -> Index Scan using item_color_cd_pkey on item_color_cd (cost=0.00..0.27 rows=1 wIDth=25) (actual time=0.002..0.003 rows=1 loops=5847933)"" Index Cond: (IIS.color_cd_ID = item_color_cd.color_cd_ID)"" Filter: ((item_color_cd.division)::text = 'BSM'::text)"" -> Index Scan using ord_line_pkey on ord_line (cost=0.00..14.94 rows=1 wIDth=16) (actual time=0.004..0.005 rows=1 loops=5847933)"" Index Cond: (ord_line.ord_line_ID = ls.ord_line_ID)"" Filter: ((ord_line.division)::text = 'BSM'::text)""Total runtime: 187788.419 ms"SELECT trans_date AS order_date,iin.item_num AS item_num,color_cd AS color_code,color_desc AS color_description,size_cd AS size_code,size_desc AS size_description,class_cd AS class_code,class_desc AS class_description,dept_cd AS dept_code,dept_desc AS dept_description,Count(ord_ID) AS num_of_orders,Sum(vs.qty_ordered) AS units,To_char(Sum(merch_amt_new3),'99G999G990.00') AS merch_amt,To_char(Sum(gift_cert_new3 - gift_cert_canceled_new3),'99G999G990.00') AS gift_cert,To_char(Sum(discount_amt),'99G999G990.00') AS discount_amt,To_char(Sum(tax_amt),'99G999G990.00') AS tax,To_char(Sum(vs.qty_ordered * Coalesce(IIS.cost,iin.cost)),'99G999G990.00') AS extended_cost,To_char(Sum(merch_amt - discount_amt),'99G999G990.00') AS total,To_char(Sum(canceled_merch_new3),'99G999G990.00') AS canceled_merch,IIS.sku_IDFROM (SELECT oh.division,oh.ord_ID,clIEnt_order_num,source_ID,bus_type_ID,order_type_ID,Date(ls.trans_date) AS trans_date,ls.ord_line_ID,Sum(ls.qty_ordered) AS qty_ordered,Sum(ls.amount) AS merch_amt,Sum(tax) AS tax_amt,Coalesce((SELECT Sum(ledger_discount.amount) AS SUM FROM ledger_discount WHERE ledger_discount.ledger_code ~~ 'D%' AND ledger_discount.division = oh.division AND ledger_discount.ord_ID = oh.ord_ID AND ledger_discount.ord_line_ID = ls.ord_line_ID AND Date(ledger_discount.trans_date) = Date(ls.trans_date)),0.0) AS discount_amt,(CASE WHEN ls.ledger_code = 'DO' THEN Sum(ls.amount) ELSE 0.0 END) AS merch_amt_new3,(CASE WHEN ls.ledger_code = 'DOG' THEN Sum(ls.amount) ELSE 0.0 END) AS gift_cert_new3,(CASE WHEN ls.ledger_code = 'DCG' THEN Sum(-ls.amount) ELSE 0.0 END) AS gift_cert_canceled_new3,(CASE WHEN ls.ledger_code = 'DC' THEN Sum(-ls.amount) ELSE 0.0 END) AS canceled_merch_new3 FROM ledger_demand ls JOIN ord_header oh USING(division,ord_ID) WHERE (ls.qty_ordered > 0 AND ls.ledger_code ~~ 'D%') OR (ls.ledger_code ~~ 'DC%') GROUP BY oh.division,ls.trans_date,ls.ledger_code ) AS vs JOIN ord_line USING(division,ord_ID,ord_line_ID) JOIN item_sku AS IIS USING(division,item_ID) JOIN item_item_num AS iin USING(division,item_num_ID) left JOIN item_color_cd USING(division,color_cd_ID) left JOIN item_size_cd USING(division,size_cd_ID) left JOIN item_class_cd USING(division,class_cd_ID) left JOIN item_dept_cd USING(division,dept_cd_ID)WHERE DATE(trans_date) BETWEEN '2012-04-01' AND '2012-04-17' AND item_dept_cd.dept_cd liKE 'S'AND division = 'BSM' GROUP BY trans_date,iin.item_num,color_cd,color_desc,size_cd,size_desc,class_cd,class_desc,dept_cd,dept_desc,IIS.sku_ID)::text) AND (ord_ID = ) AND (date(trans_date) = date()))"" -> Hash (cost=162.91..162.91 rows=1 wIDth=83) (actual time=984.452..984.452 rows=112897 loops=1)"" -> nested Loop (cost=67.45..162.91 rows=1 wIDth=83) (actual time=0.736..852.432 rows=112897 loops=1)"" -> nested Loop (cost=67.45..91.07 rows=1 wIDth=75) (actual time=0.705..45.978 rows=8463 loops=1)"" -> nested Loop (cost=67.45..75.24 rows=1 wIDth=51) (actual time=0.675..4.358 rows=360 loops=1)"" -> Index Scan using item_dept_cd_division_key on item_dept_cd (cost=0.00..5.27 rows=1 wIDth=27) (actual time=0.030..0.032 rows=1 loops=1)"" Index Cond: (((division)::text = 'BSM'::text) AND ((dept_cd)::text = 'S'::text))"" Filter: ((dept_cd)::text ~~ 'S'::text)"" -> Bitmap Heap Scan on item_item_num iin (cost=67.45..69.96 rows=1 wIDth=32) (actual time=0.641..3.818 rows=360 loops=1)"" Recheck Cond: ((iin.dept_cd_ID = item_dept_cd.dept_cd_ID) AND ((iin.division)::text = 'BSM'::text))"" -> BitmapAnd (cost=67.45..67.45 rows=1 wIDth=0) (actual time=0.598..0.598 rows=0 loops=1)"" -> Bitmap Index Scan on item_item_num_dept_ID (cost=0.00..6.92 rows=213 wIDth=0) (actual time=0.089..0.089 rows=364 loops=1)"" Index Cond: (iin.dept_cd_ID = item_dept_cd.dept_cd_ID)"" -> Bitmap Index Scan on item_item_num_division_key (cost=0.00..59.64 rows=2573 wIDth=0) (actual time=0.479..0.479 rows=2753 loops=1)"" Index Cond: ((iin.division)::text = 'BSM'::text)"" -> Index Scan using item_sku_item_num_ID_key on item_sku IIS (cost=0.00..15.82 rows=1 wIDth=32) (actual time=0.015..0.074 rows=24 loops=360)"" Index Cond: (IIS.item_num_ID = iin.item_num_ID)"" Filter: ((IIS.division)::text = 'BSM'::text)"" -> Index Scan using ord_line_item_ID_IDx on ord_line (cost=0.00..71.79 rows=3 wIDth=16) (actual time=0.009..0.072 rows=13 loops=8463)"" Index Cond: (ord_line.item_ID = IIS.item_ID)"" Filter: ((ord_line.division)::text = 'BSM'::text)"" -> Index Scan using item_class_cd_pkey on item_class_cd (cost=0.00..0.27 rows=1 wIDth=31) (actual time=0.001..0.001 rows=0 loops=5118)"" Index Cond: (iin.class_cd_ID = item_class_cd.class_cd_ID)"" Filter: ((item_class_cd.division)::text = 'BSM'::text)"" -> Index Scan using item_size_cd_pkey on item_size_cd (cost=0.00..0.27 rows=1 wIDth=25) (actual time=0.003..0.004 rows=1 loops=5118)"" Index Cond: (IIS.size_cd_ID = item_size_cd.size_cd_ID)"" Filter: ((item_size_cd.division)::text = 'BSM'::text)"" -> Index Scan using item_color_cd_pkey on item_color_cd (cost=0.00..0.27 rows=1 wIDth=25) (actual time=0.003..0.004 rows=1 loops=5118)"" Index Cond: (IIS.color_cd_ID = item_color_cd.color_cd_ID)"" Filter: ((item_color_cd.division)::text = 'BSM'::text)""Total runtime: 3208.876 ms"
并且为了更快的查询：
EXPLAIN SELECT * FROM mytable WHERE myDate BETWEEN '2012-01-01' AND '2012-01-04'EXPLAIN SELECT * FROM mytable WHERE myDate BETWEEN '2012-01-01' AND '2012-04-17'
查询只有更改为WHERE DATE(trans_date)BETWEEN’2012-04-01’和’2012-04-17′
简短的答案是它真的取决于你的数据集/表/索引等.尝试为你的每个语句运行 EXPLAIN,它会告诉你究竟发生了什么. 总结以上是内存溢出为你收集整理的具有较小日期范围(结果集)的PostgreSQL查询慢于具有较大日期范围(结果)的日期范围全部内容，希望文章能够帮你解决具有较小日期范围(结果集)的PostgreSQL查询慢于具有较大日期范围(结果)的日期范围所遇到的程序开发问题。
如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。
欢迎分享，转载请注明来源：内存溢出
微信扫一扫
支付宝扫一扫
评论列表（0条）