繼承的表和索引的性能問題

我有一個帶有主表和 2 個子表的 PostgreSQL 數據庫。我的主表：

CREATE TABLE test (
   id serial PRIMARY KEY, 
   date timestamp without time zone
);
CREATE INDEX ON test(date);

我的子表：

CREATE TABLE test_20150812 (
   CHECK ( date >= DATE '2015-08-12' AND date < DATE '2015-08-13' )
) INHERITS (test);

CREATE TABLE test_20150811 (
   CHECK ( date >= DATE '2015-08-11' AND date < DATE '2015-08-12' )
) INHERITS (test);

CREATE INDEX ON test_20150812(date);
CREATE INDEX ON test_20150811(date);

當我執行如下查詢時：

select * from test_20150812 where date > '2015-08-12' order by date desc;

它返回非常快（20-30 毫秒）。EXPLAIN輸出：

Limit  (cost=0.00..2.69 rows=50 width=212)
  ->  Index Scan Backward using test_20150812_date_idx on test_20150812  (cost=0.00..149538.92 rows=2782286 width=212)
        Index Cond: (date > '2015-08-12 00:00:00'::timestamp without time zone)

但是，如果我執行如下查詢：

select * from test where date > '2015-08-12' order by date desc;

這需要很長時間（10-15 秒）。EXPLAIN輸出：

Limit  (cost=196687.06..196687.19 rows=50 width=212)
  ->  Sort  (cost=196687.06..203617.51 rows=2772180 width=212)
        Sort Key: public.test.date
        ->  Result  (cost=0.00..104597.24 rows=2772180 width=212)
              ->  Append  (cost=0.00..104597.24 rows=2772180 width=212)
                    ->  Seq Scan on test  (cost=0.00..0.00 rows=1 width=1857)
                          Filter: (date > '2015-08-12 00:00:00'::timestamp without time zone)
                    ->  Seq Scan on test_20150812 test  (cost=0.00..104597.24 rows=2772179 width=212)
                          Filter: (date > '2015-08-12 00:00:00'::timestamp without time zone)

constraint_exclusion在ON我的postgresql.conf. 因此它應該只在test_20150812.

我看到，如果在主表上執行查詢，則永遠不會使用索引。我該如何改進它？我想在我的主表上進行所有查詢。在查詢特定日期時，我希望在主表或子表上查詢之間沒有性能差異。

“日期”

不要將您的**timestamp**專欄稱為“日期”，這是非常具有誤導性的。更好的是，根本不要使用基本類型名稱“日期”作為標識符，這容易出錯，導致錯誤消息令人困惑，並且它是標準 SQL 中的保留字。應該是這樣的：

CREATE TABLE test (
 id serial PRIMARY KEY
, ts timestamp NOT NULL  -- also adding NOT NULL constraint
);
CREATE INDEX ON test(ts);

注意事項

請注意這個帶有約束排除的警告：

約束排除僅在查詢的WHERE子句包含 **常量（或外部提供的參數）**時才有效。例如，CURRENT_TIMESTAMP 無法優化與非不可變函式的比較，因為規劃器無法知道函式值在執行時可能落入哪個分區。

大膽的epmhasis我的。你躲過了這個，但你的混亂設置你可能很快就會絆倒它。

此外，由於您有每日分區：

在約束排除期間會檢查主表所有分區上的所有約束，因此大量分區可能會大大增加查詢計劃時間。使用這些技術進行分區可以很好地處理多達一百個分區；不要嘗試使用數千個分區。

大膽強調我的。如果您跨越兩個月以上，請嘗試每週或每月分區。

謂詞不匹配

您的檢查條件：

CHECK ( date >= DATE '2015-08-12' AND date < DATE '2015-08-13' )

但是您的查詢具有以下條件：

where date > '2015-08-12' order by date desc;  -- should be: >=

這會留下輕微的不匹配（可能不正確！）並迫使 Postgres 重新檢查條件。不好，但也無法回答你的問題。

使用>=，並創建列NOT NULL或附加NULLS LAST到語句：

WHERE ts >= '2015-08-12' ORDER BY ts DESC;

…並使索引匹配。

不潔CHECK約束

CHECK約束用date常量而不是常量保存timestamp。應該是這樣的：

CHECK (ts >= timestamp '2015-08-11' AND ts < timestamp '2015-08-12');

約束排除

你寫：

constraint_exclusion在ON我的postgresql.conf. 因此它應該只在test_20150812.

正如您在查詢計劃中看到的那樣，僅掃描test和test_20150812，但不掃描test_20150811。爾格：約束排除工作正常，儘管你有所有的偏差。那隻是另一個錯誤的軌道。

贏得了許多戰鬥，但沒有贏得戰爭

清理完所有這些後，我看到了子表的點陣圖索引掃描，而不是您的 seq 掃描。仍然比僅對子表進行查詢要慢。這顯然是因為父表本身也可以有匹配的行，這些行必須與其餘行一起排序，因此不能僅從索引中讀取結果。

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