分區約束不用於涉及按時間戳分區的表的連接

我有一個分區表結構，如：

CREATE TABLE measurements (
   sensor_id bigint,
   tx timestamp,
   measurement int
);

CREATE TABLE measurements_201201(
   CHECK (tx >= '2012-01-01 00:00:00'::timestamp without time zone 
      AND tx < ('2012-01-01 00:00:00'::timestamp without time zone + '1 mon'::interval))    
)INHERITS (measurements);
CREATE INDEX ON measurements_201201(sensor_id);
CREATE INDEX ON measurements_201201(tx);
CREATE INDEX ON measurements_201201(sensor_id, tx);
....

等等。每個表大約有 20M 行。

如果我在子句中查詢感測器樣本和時間戳樣本WHERE，查詢計劃會顯示選擇的正確表和使用的索引，例如：

SELECT *
FROM measurements
INNER JOIN sensors TABLESAMPLE BERNOULLI (0.01) USING (sensor_id)
WHERE tx BETWEEN '2015-01-04 05:00' AND '2015-01-04 06:00' 
   OR tx BETWEEN '2015-02-04 05:00' AND '2015-02-04 06:00' 
   OR tx BETWEEN '2014-03-05 05:00' AND '2014-04-07 06:00' ;

但是，如果我使用 CTE，或者將時間戳值放入表中（未顯示，即使在臨時表上有索引）。

WITH sensor_sample AS(
   SELECT sensor_id, start_ts, end_ts
   FROM sensors TABLESAMPLE BERNOULLI (0.01)
   CROSS JOIN (VALUES (TIMESTAMP '2015-01-04 05:00', TIMESTAMP '2015-01-04 06:00'),
       (TIMESTAMP '2015-02-04 05:00', TIMESTAMP '2015-02-04 06:00'),
       (TIMESTAMP  '2014-03-05 05:00', '2014-04-07 06:00') ) tstamps(start_ts, end_ts)
)

類似下面的東西

SET constraint_exclusion = on;
SELECT * FROM measurements
INNER JOIN sensor_sample USING (sensor_id)
WHERE tx BETWEEN start_ts AND end_ts

對每個表執行索引掃描。這仍然相對較快，但是隨著查詢複雜性的增加，這可能會變成 seq 掃描，這對於從有限的分區表子集（50 個中的 4-5 個）中檢索約 40K 行最終會非常慢。

我擔心這樣的事情是問題所在。

對於非平凡的表達式，您必須在查詢中重複或多或少的逐字條件，以使 Postgres 查詢規劃器了解它可以依賴 CHECK 約束。即使看起來多餘！

如何改進分區和查詢結構以減少對所有數據執行 seq 掃描的可能性？

基於約束的排除

$$ CBE $$在查詢計劃的早期階段執行，就在查詢被解析、映射到實際關係並重寫之後。（內部，規劃器/優化器階段） 計劃者不能假設“sensor_sample”表的任何內容。

因此，除非您在查詢中硬編碼了值，否則規劃器不會排除“分區”。

我猜 CTE 變體會發生什麼……計劃器受到限制，因為您使用 TABLESAMPLE 並且即使子查詢中的文字是靜態的，整個子查詢也可能被視為易失性。（這只是我的猜測，我不是規劃器程式碼專家）

從好的方面來說，結果為負的索引掃描速度非常快。（最多單頁掃描！）所以除非你有超過 10000 個分區，否則我不會打擾。

所以，直接回答你的問題：

• 您無法進一步改進此資料結構。
• 關於索引掃描 - 它們很便宜；
• 關於順序掃描 - 正如您在自己的範例中看到的那樣，盡可能避免使用它們。

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