PostgreSQL 分區 - 約束排除不起作用

我有一個大約有 900 萬行的表，我在其中保存了客戶的 ID、時間戳和特定日期時間的成本值。

它看起來像這樣：

customer|t|consumption

我需要將我的數據集拆分為較小的表，這些表將從表主表繼承。

主表：

CREATE TABLE my_table_master (
customer TEXT NOT NULL,
t TIMESTAMP NOT NULL,
consumption INTEGER NOT NULL 
);

子表：

CREATE TABLE my_table_2 (
CHECK ( t='2013-07-01 01:00:00')
) INHERITS (my_table_master);

CREATE TABLE my_table_3 (
CHECK ( my_extract(t)=0 )
) INHERITS (my_table_master);

CREATE TABLE my_table_4 (
CHECK ( t <> '2013-07-01 01:00:00' AND my_extract(t) <> 0 )
) INHERITS (my_table_master);

問題

約束排除在這裡不起作用。請注意，我已經執行了命令：

SET constraint_exclusion = on;

例如，如果我執行以下查詢，則計劃器包括 my_table_2 和 my_table_3 而不是my_table_2.

SELECT * FROM my_table_master WHERE t='2013-07-01 01:00:00';

查詢計劃：

Append  (cost=0.00..24601.55 rows=967 width=24) (actual time=164.514..164.693 rows=993 loops=1)
->  Seq Scan on public.my_table_master  (cost=0.00..0.00 rows=1 width=44) (actual time=0.010..0.010 rows=0 loops=1)
Output: my_table_master.customer, my_table_master.t, my_table_master.consumption
Filter: (my_table_master.t = '2013-07-01 01:00:00'::timestamp without time zone)
->  Seq Scan on public.my_table_3  (cost=0.00..24577.43 rows=960 width=24) (actual time=164.472..164.472 rows=0 loops=1)
Output: my_table_3.customer, my_table_3.t, my_table_3.consumption
Filter: (my_table_3.t = '2013-07-01 01:00:00'::timestamp without time zone)
Rows Removed by Filter: 1237954
->  Seq Scan on public.my_table_2  (cost=0.00..24.13 rows=6 width=44) (actual time=0.029..0.165 rows=993 loops=1)
Output: my_table_2.customer, my_table_2.t, my_table_2.consumption
Filter: (my_table_2.t = '2013-07-01 01:00:00'::timestamp without time zone)
Planning time: 0.298 ms
Execution time: 164.731 ms

編輯：

如果我只詢問數據庫上的兩個特定查詢，它們分別包含以下 WHERE 子句：

WHERE t='2013-07-01 01:00:00' 
WHERE my_extract(t)=0

你認為我應該以不同的方式拆分我的數據集嗎？如果是，請告訴我。

仔細檢查…… PostgreSQL實際上按預期執行：

1. my_table_master 被掃描，因為它沒有約束。主表實際上可以有行，在 PostgreSQL 繼承中沒有什麼可以阻止它。
2. my_table_3 被掃描是因為它的檢查約束 ( ) 與子句t='2013-07-01 01:00:00'中的條件（原則上）沒有任何關係。WHERE即使函式的定義與不兼容，數據庫顯然也沒有my_extract“*足夠智能”*來推斷它。如果合適，您可以在 where 子句中添加，即使是多餘的。這將防止被掃描。my_extract(t) = 0``t='2013-07-01 01:00:00'``NOT (my_extract(t) = 0)``my_table_3
3. my_table_2 按預期掃描。
4. my_table_4 實際上並未掃描。

因此，約束會阻止從 4個表中掃描出一個表。

備用查詢，添加 (my_extract(t) = 0) 子句：

EXPLAIN 
   SELECT * 
   FROM my_table_master 
   WHERE t='2013-07-01 01:00:00' 
         AND NOT (my_extract(t) = 0)  /* This is the new addition */ ;

返回

Append  (cost=0.00..309.45 rows=7 width=44)
 ->  Seq Scan on my_table_master  (cost=0.00..0.00 rows=1 width=44)
       Filter: ((t = '2013-07-01 01:00:00'::timestamp without time zone) AND (my_extract(t) <> 0))
 ->  Seq Scan on my_table_2  (cost=0.00..309.45 rows=6 width=44)
       Filter: ((t = '2013-07-01 01:00:00'::timestamp without time zone) AND (my_extract(t) <> 0))

通過添加額外的條件，my_table_3可以防止掃描。

注意：為了進行試驗，我做了my_extract這樣的準備：

CREATE FUNCTION my_extract(_t timestamp without time zone) 
RETURNS integer AS
$$
begin
   return extract(day from _t)::integer ;
end
$$
LANGUAGE plpgsql IMMUTABLE /* attention! */ ;

我已經使用 plpgsql 來確保它沒有被內聯（即使它可以很簡單地完成）。請注意，必須將函式標記為**IMMUTABLE**（即其結果僅取決於其參數），否則將無法按預期工作。

附錄

EXPLAIN 
   SELECT * FROM my_table_master 
   WHERE my_extract(t) = 0 ;

 Append  (cost=0.00..613.25 rows=13 width=44)
   ->  Seq Scan on my_table_master  (cost=0.00..0.00 rows=1 width=44)
         Filter: (my_extract(t) = 0)
   ->  Seq Scan on my_table_2  (cost=0.00..306.62 rows=6 width=44)
         Filter: (my_extract(t) = 0)
   ->  Seq Scan on my_table_3  (cost=0.00..306.62 rows=6 width=44)
         Filter: (my_extract(t) = 0)

工作正常：

1. my_table_master-> 和以前一樣
2. my_table_2-> 被掃描，因為它的約束與
3. my_table_3-> 被掃描是因為它的約束實際上匹配WHERE 子句。

PostgreSQL 可以做的（但它沒有）是FILTER my_extract(t)=0在掃描表 3 時消除，因為已經知道條件為真。讓我們希望這種優化在未來的某個時候實現。

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