Postgresql分區表timestamptz約束問題

該表reports按天分區表，例如reports_20170414，reports_20170415

約束SQL定義如下

CHECK (
   rpt_datetime >= '2017-04-14 00:00:00+00'::timestamp with time zone 
   AND 
   rpt_datetime < '2017-04-15 00:00:00+00'::timestamp with time zone
)

讓我們考慮兩種類型的查詢

SELECT SUM(rpt_unique_clicks) 
   FROM reports WHERE rpt_datetime >= '2017-04-14 00:00:00';

上面的查詢在亞秒內執行，一切都很好。

SELECT SUM(rpt_unique_clicks) 
   FROM reports WHERE rpt_datetime >= 
date_trunc('day', current_timestamp);

相反，上述查詢至少執行 15 秒。

SELECT date_trunc('day', CURRENT_TIMESTAMP), '2017-04-14 00:00:00';

返回

2017-04-14 00:00:00 +00:00 | 2017-04-14 00:00:00

當我檢查為什麼後者執行時間長（使用解釋分析）時，我完成了它訪問並掃描每個表（〜500）的結果，但前者僅訪問reports_20170414，因此約束檢查存在問題。

我想查詢今天，而不像後一個查詢那樣使用準備好的語句。為什麼date_trunc('day', CURRENT_TIMESTAMP)不等於 2017-04-14 00:00:00？

我無法完全回答為什麼date_trunc('day', CURRENT_TIMESTAMP)不等同於常數……即使兩者CURRENT_TIMESTAMP和date_trunc都被定義為IMMUTABLE.

但我認為我們可以做一個有根據的實驗猜測：顯然，PostgreSQL 規劃器不評估函式。因此，它沒有任何好的方法來知道要檢查哪些分區，並製定一個檢查所有分區的計劃。

---

實驗檢查

我們創建一個基（父）表：

-- Base table
CREATE TABLE reports
(
    rpt_datetime timestamp without time zone DEFAULT now() PRIMARY KEY,
    rpt_unique_clicks integer NOT NULL DEFAULT 1,
    something_else text
) ;

我們創建一個自動分區插入觸發器：

-- Auto-partition using trigger
-- Adapted from http://blog.l1x.me/post/2016/02/16/creating-partitions-automatically-in-postgresql.html
CREATE OR REPLACE FUNCTION create_partition_and_insert ()
RETURNS TRIGGER AS 
$$
DECLARE
    _partition_date text ;
    _partition_date_p1 text ;
    _partition text ;
BEGIN
    _partition_date := to_char(new.rpt_datetime, 'YYYYMMDD');
    _partition := 'reports_' || _partition_date ;

    -- Check if table exists... 
    -- (oversimplistic: doesn't take schemas into account... doesn't check for possible race conditions)
    if not exists (SELECT relname FROM pg_class WHERE relname=_partition) THEN
       _partition_date_p1 := to_char(new.rpt_datetime + interval '1 day', 'YYYYMMDD');

       RAISE NOTICE 'Creating %', _partition ;

       EXECUTE 'CREATE TABLE ' || _partition || 
             ' (CHECK (rpt_datetime >= timestamp ''' || _partition_date || ''' AND rpt_datetime < timestamp ''' || _partition_date_p1 || '''))' ||
             ' INHERITS (reports)' ;
    end if ;

    EXECUTE 'INSERT INTO ' || _partition || ' SELECT(reports ' || quote_literal(NEW) || ').* ;' ;

    -- We won't insert anything on parent table
    RETURN NULL ;
END 
$$
LANGUAGE plpgsql VOLATILE
COST 1000;

-- Attach trigger to parent table
CREATE TRIGGER reports_insert_trigger
BEFORE INSERT ON reports
FOR EACH ROW EXECUTE PROCEDURE create_partition_and_insert();

用一些數據填充（分區）表；並檢查觸發器所做的分區：

INSERT INTO 
    reports (rpt_datetime, rpt_unique_clicks, something_else)
SELECT
    d, 1, 'Hello'
FROM
    generate_series(timestamp '20170416' - interval '7 days', timestamp '20170416', interval '10 minutes') x(d) ;

-- Check how many partitions we made
SELECT 
    table_name 
FROM 
     information_schema.tables 
WHERE 
    table_name like 'reports_%' 
ORDER BY 
    table_name;

| 表名 |
| :--------------- |
| 報告_20170409 |
| 報告_20170410 |
| 報告_20170411 |
| 報告_20170412 |
| 報告_20170413 |
| 報告_20170414 |
| 報告_20170415 |
| 報告_20170416 |

此時，我們檢查兩個不同的查詢。第一個確實使用 aconstant比較rpt_datetime：

EXPLAIN (ANALYZE) 
SELECT 
    SUM(rpt_unique_clicks) 
FROM 
    reports 
WHERE 
    rpt_datetime >= timestamp '20170416' ; 

使用恆定時間戳，只檢查“報告”和適當的分區：

| 查詢計劃 |
| :---------------------------------------------------------------------------------------------------------------------- |
| 聚合（成本=25.07..25.08 行=1 寬度=8）（實際時間=0.015..0.015 行=1 循環=1）|
| -> 追加（成本=0.00..24.12 行=378 寬度=4）（實際時間=0.009..0.010 行=1 循環=1）|
| -> Seq Scan on reports (cost=0.00..0.00 rows=1 width=4) (實際時間=0.003..0.003 rows=0 loops=1) |
| 過濾器：（rpt_datetime >= '2017-04-16 00:00:00'::timestamp without time zone）|
| -> 在reports_20170416 上進行序列掃描（成本=0.00..24.12 行=377 寬度=4）（實際時間=0.006..0.007 行=1 循環=1）|
| 過濾器：（rpt_datetime >= '2017-04-16 00:00:00'::timestamp without time zone）|
| 規劃時間：0.713 ms |
| 執行時間：0.040 ms |

如果我們使用等價SELECT的函式呼叫（即使這個函式呼叫的結果是一個常量），那麼計劃就完全不同了：

EXPLAIN (ANALYZE) 
SELECT 
    SUM(rpt_unique_clicks) 
FROM 
    reports 
WHERE 
    rpt_datetime >= date_trunc('day', now()) ;  

| 查詢計劃 |
| :---------------------------------------------------------------------------------------------------------------------- |
| 聚合（成本=245.74..245.75 行=1 寬度=8）（實際時間=0.842..0.843 行=1 循環=1）|
| -> 追加（成本=0.00..238.20 行=3017 寬度=4）（實際時間=0.837..0.838 行=1 循環=1）|
| -> Seq Scan on reports (cost=0.00..0.00 rows=1 width=4) (實際時間=0.003..0.003 rows=0 loops=1) |
| 過濾器： (rpt_datetime >= date_trunc('day'::text, now())) |
| -> 在reports_20170409 上進行序列掃描（成本=0.00..29.78 行=377 寬度=4）（實際時間=0.214..0.214 行=0 循環=1）|
| 過濾器： (rpt_datetime >= date_trunc('day'::text, now())) |
| 過濾器刪除的行數：144 |
| -> 在reports_20170410 上進行序列掃描（成本=0.00..29.78 行=377 寬度=4）（實際時間=0.097..0.097 行=0 循環=1）|
| 過濾器： (rpt_datetime >= date_trunc('day'::text, now())) |
| 過濾器刪除的行數：144 |
| -> 在reports_20170411 上進行序列掃描（成本=0.00..29.78 行=377 寬度=4）（實際時間=0.095..0.095 行=0 循環=1）|
| 過濾器： (rpt_datetime >= date_trunc('day'::text, now())) |
| 過濾器刪除的行數：144 |
| -> 對報告_20170412 的 Seq 掃描（成本=0.00..29.78 行=377 寬度=4）（實際時間=0.096..0.096 行=0 循環=1）|
| 過濾器： (rpt_datetime >= date_trunc('day'::text, now())) |
| 過濾器刪除的行數：144 |
| -> 在reports_20170413 上進行序列掃描（成本=0.00..29.78 行=377 寬度=4）（實際時間=0.131..0.131 行=0 循環=1）|
| 過濾器： (rpt_datetime >= date_trunc('day'::text, now())) |
| 過濾器刪除的行數：144 |
| -> 對報告_20170414 的 Seq 掃描（成本=0.00..29.78 行=377 寬度=4）（實際時間=0.098..0.098 行=0 循環=1）|
| 過濾器： (rpt_datetime >= date_trunc('day'::text, now())) |
| 過濾器刪除的行數：144 |
| -> 在reports_20170415 上進行序列掃描（成本=0.00..29.78 行=377 寬度=4）（實際時間=0.095..0.095 行=0 循環=1）|
| 過濾器： (rpt_datetime >= date_trunc('day'::text, now())) |
| 過濾器刪除的行數：144 |
| -> 在reports_20170416 上進行序列掃描（成本=0.00..29.78 行=377 寬度=4）（實際時間=0.004..0.005 行=1 循環=1）|
| 過濾器： (rpt_datetime >= date_trunc('day'::text, now())) |
| 規劃時間：0.298 ms |
| 執行時間：0.892 ms |

dbfiddle在這裡

引用自：https://dba.stackexchange.com/questions/171057