sql - 具有较小日期范围(结果集)的 PostgreSQL 查询比具有较大日期范围(结果)的查询慢-6ren

sql - 具有较小日期范围(结果集)的 PostgreSQL 查询比具有较大日期范围(结果)的查询慢

转载作者：行者123 更新时间：2023-11-29 11:25:17

当我执行日期时，我在 postgresql 中有一个查询在“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 = $2)"
"                                                    Filter: (((ledger_code)::text ~~ 'D%'::text) AND ((division)::text = ($0)::text) AND (ord_id = $1) AND (date(trans_date) = date($3)))"
"                          ->  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"

为了更快的查询:

"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 = $2)"
"                                              Filter: (((ledger_code)::text ~~ 'D%'::text) AND ((division)::text = ($0)::text) AND (ord_id = $1) AND (date(trans_date) = date($3)))"
"                          ->  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"

查询仅更改为 WHERE DATE(trans_date) BETWEEN '2012-04-01' AND '2012-04-17'

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_id
FROM     (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,
                   oh.ord_id,
                   client_order_num,
                   source_id,
                   bus_type_id,
                   order_type_id,
                   ls.trans_date,
                   ls.ord_line_id,
                   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

最佳答案

简短的回答是，它实际上取决于您的数据集/表/索引等。尝试运行 EXPLAIN对于您的每个陈述，它都会准确地告诉您发生了什么。

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'

关于sql - 具有较小日期范围(结果集)的 PostgreSQL 查询比具有较大日期范围(结果)的查询慢，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/10218206/

文章推荐： hibernate - 超出非 super 用户的 PostgreSQL 连接限制

文章推荐： ios - Xcode 10、11 - 无法发出预编译 header

文章推荐： postgresql - 如何在 JPA 中使用 WHERE 子句注释唯一约束

EXCEL 列/范围 A 的平均值如果列/范围 B 或列/范围 C 等于否
我不能解决这个问题。和标题说的差不多…… 如果其他两个范围/列中有“否”，我如何获得范围或列的平均值？换句话说，我想计算 A 列的平均值，并且我有两列询问是/否问题(B 列和 C 列)。我只希望 B
python - 2to3 范围(...) -> 列表(范围(...))
我知道 python 2to3 将所有 xrange 更改为 range 我没有发现任何问题。我的问题是关于它如何将 range(...) 更改为 list(range(...)) :它是愚蠢的，只是
java - session 范围 Bean 不是 session 范围 Bean
我有一个 Primefaces JSF 项目，并且我的 Bean 注释有以下内容: @Named("reportTabBean") @SessionScoped public class Report
ruby-on-rails-3 - 我可以在 Rails 中将常见的 ActiveRecord 范围(范围)与模块一起使用吗？
在 rails3 中，我在模型中制作了相同的范围。例如 class Common ?" , at) } end 我想将公共(public)范围拆分为 lib 中的模块。所以我试试这个。 module
jsf - 在另一个 View 范围 bean 中注入(inject)一个 View 范围 bean 会导致它被重新创建
我需要在另一个 View 范围 bean 中使用保存在 View 范围 bean 中的一些数据。 @ManagedBean @ViewScoped public class Attivita impl
JavaScript 范围
为什么下面的代码输出4？谁能给我推荐一篇好文章来深入学习 javascript 范围。这段代码返回4，但我不明白为什么？ (function f(){ return f(); functio
JavaScript 范围
我有一个与此结构类似的脚本 $(function(){ var someVariable; function doSomething(){ //here } $('#som
Jquery 范围
我刚刚开始学习 Jquery，但这些示例对我帮助不大...... 现在，以下代码发生的情况是，我有 4 个表单，我使用每个表单的链接在它们之间进行切换。但我不知道如何在第一个函数中获取变量“postO
JavaScript 范围/this
为什么当我这样做时: function Dog(){ this.firstName = 'scrappy'; } Dog.firstName 未定义？但是我可以这样做: Dog.firstNa
Python解析文本文件的选定区域/范围
我想打印文本文件 text.txt 的选定部分，其中包含: tickme 1.1(no.3) lesson1-bases lesson2-advancedfurther para:using the
Javascript "this"范围
我正在编写一些 JavaScript 代码。我对这个关键字有点困惑。如何在 dataReceivedHandler 函数中访问 logger 变量？ MyClass: { logger: nu
vba - 范围 - 更改引用
我有这个代码: Public Sub test() Dim Tgt As Range Set Tgt = Range("A1") End Sub 我想更改当前为“A1”的 Tgt 的引
regex - 范围> = 0但小于1000的正则表达式
我正忙于此工作，以为我会把它放在我们那里。该数字必须是最多3个单位和最多5个小数位的数字，等等。有效的 999.99999 99.9 9 0.99999 0 无效的 -0.1 999.123456
spring - 未注释的参数覆盖@???范围
覆盖代码时: @Override public void open(ExecutionContext executionContext) { super.open(executio
PHP preg_match 范围
我想使用 preg_match 来匹配数字 1 - 21。我如何使用 preg_match 来做到这一点？如果数字大于 21，我不想匹配任何东西。 example preg_match('([0-9]
具有特定开始和无限结束的 Clojure 范围
根据docs range函数有四种形式: (range) 0 - 无穷大 (range end) 0 - 结束 (range start end)开始 - 结束 (range start end st
iPhone 范围 slider
我知道有一个UISlider，但是有人已经制作了RangeSlider(用两个拇指吗？)或者知道如何扩展 uislider？最佳答案我认为你不能直接扩展 UISlider，你可能需要扩展 UICo
要列出的 Python 范围
我正在尝试将范围转换为列表。 nums = [] for x in range (9000, 9004): nums.append(x) print nums 输出 [9000] [9
typescript - 如何使用TypeScript方法装饰器并保留常规的 `this`范围
请注意:此问题是由于在运行我的修饰方法时使用了GraphQL解析器。这意味着this的范围为undefined。但是，该问题的基础知识对于装饰者遇到问题的任何人都是有用的。这是我想使用的基本装饰器(
JavaScript 范围/代码迭代不同步
我正在尝试创建一个工具来从网页上抓取信息(是的，我有权限)。到目前为止，我一直在使用 Node.js 结合 requests 和 Cheerio 来拉取页面，然后根据 CSS 选择器查找信息。我已经

行者123

个人简介

我是一名优秀的程序员,十分优秀！

作者热门文章

滴滴打车优惠券免费领取

全站热门文章

首页

博学

6Ren·AI

商城

sql - 具有较小日期范围(结果集)的 PostgreSQL 查询比具有较大日期范围(结果)的查询慢