主页 / 问题 / 1599148
Accepted
Айрат
Asked:2024-11-08 01:34:14 +0000 UTC2024-11-08 01:34:14 +0000 UTC

CTE 参数

  • 772

有一个按日期分区的表。

在一个请求中，我们将日期作为参数提供给 CTE 块并计算工作日，然后在主请求中使用它：

  WITH prm 
   AS (
      SELECT 
       :dateFrom::DATE AS d_f, 
       :dateTo::DATE d_t, 
       (SELECT wrk_day d_f_wrk FROM calendar WHERE on_date=:dateFrom)
      )
   SELECT * FROM prm,tab t WHERE t.open_date BETWEEN prm.d_f_wrk AND prm.d_t

所以，如果我们在条件的主查询中使用它

BETWEEN prm.d_f_wrk AND prm.d_t

然后对所有分区进行搜索。就过滤器中的查询而言，它是这样写的：

t.open_date>=$2 AND t.open_date<='2024-10-30'::DATE

如果你使用

BETWEEN prm.d_f AND prm.d_t

然后仅过滤必要的分区。就过滤器中的查询而言，它是这样写的：

t.open_date>='2024-10-01'::DATE AND t.open_date<='2024-10-30'::DATE

如何将计算出的日期从 CTE 或以其他方式以相同日期参数的形式传递到主查询，以便分区过滤器仅过滤掉必要的日期？

另外，原则上，按分区过滤不起作用；如果日期是从另一个请求中获取的，则滑翔机会遍历所有分区。

> EXPLAIN analyze SELECT * 
FROM calendar wc, tab t 
WHERE t.open_date =wc.wrk_day  
 AND wc.on_date ='2024-10-30'
 
QUERY PLAN                                                                                                                                       |
-------------------------------------------------------------------------------------------------------------------------------------------------+
Hash JOIN  (cost=2.38..9602.67 ROWS=1099 width=85) (actual TIME=85.080..89.473 ROWS=41 loops=1)                                                  |
  Hash Cond: (t.open_date = wc.wrk_day)                                                                                                |
  ->  Append  (cost=0.00..8758.21 ROWS=316133 width=69) (actual TIME=0.590..54.404 ROWS=315957 loops=1)                                          |
        ->  Seq Scan ON t_2024_02_01 t_1  (cost=0.00..0.00 ROWS=1 width=596) (actual TIME=0.060..0.061 ROWS=0 loops=1)           |
        ->  Seq Scan ON t_2024_02_02 t_2  (cost=0.00..0.00 ROWS=1 width=596) (actual TIME=0.012..0.012 ROWS=0 loops=1)           |
        ->  Seq Scan ON t_2024_02_03 t_3  (cost=0.00..0.00 ROWS=1 width=596) (actual TIME=0.008..0.008 ROWS=0 loops=1)           |
        ->  Seq Scan ON t_2024_02_04 t_4  (cost=0.00..0.00 ROWS=1 width=596) (actual TIME=0.007..0.007 ROWS=0 loops=1)           |
...
...
->  Hash  (cost=2.37..2.37 ROWS=1 width=16) (actual TIME=0.021..0.021 ROWS=1 loops=1)                                                         |
        Buckets: 1024  Batches: 1  Memory Usage: 9kB                                                                                            |
        ->  INDEX Scan USING calendar_pkey ON calendar wc  (cost=0.15..2.37 ROWS=1 width=16) (actual TIME=0.008..0.009 ROWS=1 loops=1)|
              INDEX Cond: (on_date = '2024-10-30'::DATE)

如果您在条件中明确指定日期，则会选择必要的分区

EXPLAIN analyze 
SELECT * 
FROM calendar wc, tab t 
WHERE t.open_date=wc.wrk_day  
 AND wc.on_date ='2024-10-30' AND t.open_date>='2024-10-29' AND t.open_date<'2024-11-01'
 
QUERY PLAN                                                                                                                                      |
------------------------------------------------------------------------------------------------------------------------------------------------+
Hash JOIN  (cost=2.38..33.53 ROWS=3 width=85) (actual TIME=0.037..0.320 ROWS=41 loops=1)                                                        |
  Hash Cond: (t.open_date = wc.wrk_day)                                                                                               |
  ->  Append  (cost=0.00..28.90 ROWS=845 width=69) (actual TIME=0.012..0.225 ROWS=845 loops=1)                                                  |
        ->  Seq Scan ON t_2024_10_29 t_1  (cost=0.00..1.61 ROWS=41 width=69) (actual TIME=0.011..0.016 ROWS=41 loops=1)         |
              FILTER: ((open_date >= '2024-10-29'::DATE) AND (open_date < '2024-11-01'::DATE))                                            |
        ->  Seq Scan ON t_2024_10_30 t_2  (cost=0.00..1.87 ROWS=58 width=69) (actual TIME=0.005..0.011 ROWS=58 loops=1)         |
              FILTER: ((open_date >= '2024-10-29'::DATE) AND (open_date < '2024-11-01'::DATE))                                            |
        ->  Seq Scan ON t_2024_10_31 t_3  (cost=0.00..21.19 ROWS=746 width=69) (actual TIME=0.006..0.135 ROWS=746 loops=1)      |
              FILTER: ((open_date >= '2024-10-29'::DATE) AND (open_date < '2024-11-01'::DATE))                                            |
  ->  Hash  (cost=2.37..2.37 ROWS=1 width=16) (actual TIME=0.015..0.016 ROWS=1 loops=1)                                                         |
        Buckets: 1024  Batches: 1  Memory Usage: 9kB                                                                                            |
        ->  INDEX Scan USING calendar_pkey ON calendar wc  (cost=0.15..2.37 ROWS=1 width=16) (actual TIME=0.012..0.013 ROWS=1 loops=1)|
              INDEX Cond: (on_date = '2024-10-30'::DATE)                                                                                        |
Planning TIME: 0.408 ms                                                                                                                         |
Execution TIME: 0.356 ms                                                                                                                        |
 
15 ROW(s) fetched.
postgresql

1 个回答

  1. Best Answer
    CREATE OR REPLACE FUNCTION 
work_day(DATE) RETURNS DATE AS $$
SELECT wc.prev_work_day FROM calendar wc WHERE wc.on_date= ($1)::DATE;
$$ LANGUAGE SQL IMMUTABLE;

EXPLAIN analyze
SELECT  *
FROM calendar wc 
LEFT JOIN tab t ON t.open_date=work_day('2024-10-30')
WHERE wc.on_date = '2024-10-30'

    查询计划

    LIMIT  (cost=0.15..4.29 ROWS=41 width=85) (actual TIME=0.018..0.041     ROWS=41 loops=1)                                                             
->  Nested Loop LEFT JOIN  (cost=0.15..4.29 ROWS=41 width=85) (actual     TIME=0.016..0.035 ROWS=41 loops=1)                                      |
->  INDEX Scan USING calendar_pkey ON calendar wc  (cost=0.15..2.37 ROWS=1 width=16) (actual TIME=0.005..0.006 ROWS=1 loops=1)|
      INDEX Cond: (on_date = '2024-10-30'::DATE)                                                                                        |
->  Seq Scan ON tab_2024_10_29 t  (cost=0.00..1.51 ROWS=41 width=69) (actual TIME=0.009..0.017 ROWS=41 loops=1)           |
      FILTER: (open_date = '2024-10-29'::DATE)                                                                                       |
 Planning TIME: 0.325 ms                                                                                                                         |
 Execution TIME: 0.070 ms                                                                                                                        |

 8 ROW(s) fetched.
    • 0

相关问题