優化查詢以在分區表中跨多天獲取數據

我正在嘗試優化查詢以檢索分區表上多天的記錄（例如：條目）。該表在時間戳列上進行分區（假設：created_at），並且由於分區，每天都會創建一個新表。

表的架構

Partitioned table "public.entries"
       Column         |            Type             | Collation | Nullable |                 
-----------------------+-----------------------------+-----------+----------+-
id                    | character varying(48)       |           | not null |
person_id             | bigint                      |           | not null |
created_at            | timestamp without time zone |           | not null |
created_at_date       | date                        |           |          |
created_at_time       | time without time zone      |           |          |
Partition key: RANGE (created_at)
Indexes:
   "entries_pkey" PRIMARY KEY, btree (id, created_at)
   "person_id_created_at_key" UNIQUE CONSTRAINT, btree (person_id, created_at)
   "btree_gist_created_at" gist (person_id, tsrange(created_at, created_at, '[]'::text))
   "person_id_cd_ct_idx" btree (person_id, created_at_date, created_at_time)
   "person_id_created_at_idx" btree (person_id, (created_at::date), (created_at::time without time zone))
Number of partitions: 30 (Use \d+ to list them.)

我需要獲取在特定天數（例如：2022-06-01 到 2022-06-05，從 08:00 到 20:00）之間完成條目的特定人員的所有可能結果。每個分區表大約有 300 萬行，這意味著我們每天有大約 300 萬個條目。一旦我增加天數的範圍，我的查詢時間就會增加，當我需要滿足30天的結果時，這對我來說就成了一個問題。

我也嘗試在person_id&上添加不同類型的索引，created_at但我仍然無法比我需要的更快地查詢。我需要以一位數毫秒（~5ms - ~9ms）獲得結果（如果可能的話）。目前，對於提到的查詢，考慮到所有這些查詢，我得到的時間是 ~20ms 到 ~100ms，但我希望它是個位數。

如果我嘗試查詢超過 30 天的記錄，則以下查詢將花費超過 ~100 毫秒。我需要將這些查詢優化 30 天，儘管範例中提到的查詢僅為 5-6 天。

我正在嘗試使用以下查詢來獲取結果：

-- QUERY #1 (Most efficient)
SELECT person_id, created_at
FROM   entries
WHERE  person_id = '111111'
AND    (
   (
       created_at >= '2022-06-01 08:00:00'
       AND created_at <= '2022-06-01 20:00:00'
   ) OR (
       created_at >= '2022-06-02 08:00:00'
       AND created_at <= '2022-06-02 20:00:00'
   ) OR (
       created_at >= '2022-06-03 08:00:00'
       AND created_at <= '2022-06-03 20:00:00'
   ) OR (
       created_at >= '2022-06-04 08:00:00'
       AND created_at <= '2022-06-04 20:00:00'
   ) OR (
       created_at >= '2022-06-05 08:00:00'
       AND created_at <= '2022-06-05 20:00:00'
   )
);

-- QUERY #2 (2nd most efficient)
SELECT person_id, created_at
FROM   entries
WHERE  person_id = '111111'
AND    created_at_date >= '2022-06-01'
AND    created_at_date <= '2022-06-05'
AND    created_at_time >= '08:00:00'
AND    created_at_time <= '20:00:00';

-- QUERY #3 (Least efficient)
SELECT person_id, created_at
FROM   entries
WHERE  person_id = '111111'
AND    (
   '[2022-06-01 08:00:00, 2022-06-01 20:00:00]'::tsrange @> created_at
   OR '[2022-06-02 08:00:00, 2022-06-02 20:00:00]'::tsrange @> created_at
   OR '[2022-06-03 08:00:00, 2022-06-03 20:00:00]'::tsrange @> created_at
   OR '[2022-06-04 08:00:00, 2022-06-04 20:00:00]'::tsrange @> created_at
   OR '[2022-06-05 08:00:00, 2022-06-05 20:00:00]'::tsrange @> created_at
);

我正在分享上述查詢的查詢計劃：

• 查詢 #1
• 查詢 #2
• 查詢 #3

您能否向我建議是否有任何方法可以通過更改表的結構或使用某些不同類型的索引組合來提高查詢效率？是否有任何其他可能性或者我查詢數據庫的方式是我可以根據我的場景執行的最有效的查詢？

您的標准在這裡非常高，可能無法達到。

隨著日期範圍的增加，您從按日期分區中獲得的好處越來越少。實際上，它可能已經適得其反，因為訪問的每個分區都意味著需要訪問另一個索引，因此還有另一個記憶體未命中的機會。尤其如此，因為 person_id 似乎極具選擇性，因此整個表中一個值的所有條目可能只適合一個或幾個索引頁，但在考慮分區時，它會改為幾個不同頁面的一小部分.

如果您絕對需要按時間分區，則可以按週或按月進行，而不是每天。

您的計劃時間在節點級別似乎相當不穩定。我懷疑時間只是由特定頁面或少數頁面是否碰巧在記憶體中找到，或者需要真正的磁碟讀取來決定的。因此，更快的 IO 或更多 RAM 用於記憶體，或者如果您已經有足夠的 RAM 只是沒有填充數據，則只是預熱數據，這可能是加快速度的最簡單方法。

您的第二個查詢無法從分區中受益（但仍需要為碎片索引付出代價），因為計劃者不知道 created_at 和 created_at_date 之間的關係，因此無法修剪分區。您可以通過將該片段更改為使用 created_at 來輕鬆修復該部分：

AND created_at >= '2022-06-01'
AND created_at < '2022-06-06'

我沒有用 Postgres 14 或 15 重新測試，但在早期版本中，這種查詢的最快方法是直接將其拆分為分區上的單獨查詢，然後**UNION ALL**.

SELECT *
FROM   entries_p2022_06_01
WHERE  person_id = '111111'
AND    created_at >= '2022-06-01 08:00:00'
AND    created_at <  '2022-06-01 20:00:00'

UNION ALL
SELECT *
FROM   entries_p2022_06_02
WHERE  person_id = '111111'
AND    created_at >= '2022-06-02 08:00:00'
AND    created_at <  '2022-06-02 20:00:00'

-- etc.

SQL 字元串很容易用一個簡單的函式生成person_id，日期和時間範圍。（我會考慮一個直接建構和執行查詢並返回結果的函式。）

注意<在created_at < '2022-06-01 20:00:00'. <=在這樣的範圍內通常是不正確的。

這完全繞過了分區邏輯，直接進入分區（表）。它還從查詢中刪除了**“醜陋OR”**，這在您最快的計劃中似乎不會花費太多，但是隨著您計劃的更多時間段（30 個而不是 5 個），成本會增加。看：

• 為什麼 OR 語句比 UNION 慢？

您需要的唯一索引(person_id, created_at)是 on 。你已經擁有了，接近完美。

但是，如果您的首要目標是優化所述查詢的讀取性能，並且由於高速記憶體的大小似乎是一個限制因素，那麼您可以做更多的事情。您的表定義顯示：

       Column         |            Type             | Collation | Nullable |                 
-----------------------+-----------------------------+-----------+----------+-
id                    | character varying(48)       |           | not null |
person_id             | bigint                      |           | not null |
created_at            | timestamp without time zone |           | not null |
created_at_date       | date                        |           |          |
created_at_time       | time without time zone      |           |          |

您目前有Range Partitioning on created_at，每日分區。這對於手頭的任務來說不是很好。

要麼形成更大的分區 - 我建議每個月。並刪除完全冗餘的列created_at_date和created_at_time. 大小事項。這些可以廉價地created_at::date生成。created_at::time

或者，如果您想保留每日分區（並且不使用跨天的時間範圍？）請改用List Partitioning on created_at_date，然後刪除該列created_at。它可以created_at_date + created_at_time廉價地生成。

**person_id聽起來像是對人類的參考。您是否預計在您的數據庫的整個生命週期中涉及超過 20 億不同的人？還是你燒了很多ID？因為，如果不是，那麼平原integer應該做 - 其範圍-2147483648 到 2147483648。bigint表和索引中的 , 是 4 個字節而不是 8 個字節。

然後，您的UNIQUE約束可以替換為(person_id, created_at_time)每個分區上的一個 - 索引元組的優化組合有效負載大小為 8 字節。看：

• 複合索引是否也適用於第一個欄位的查詢？

並且您的查詢簡化為：

SELECT *
FROM   entries_p2022_06_01
WHERE  person_id = '111111'
AND    created_at_time >= '08:00'
AND    created_at_time <  '20:00'
...

刪除冗餘列（以及基於它們的索引）還意味著您將有限的記憶體記憶體集中在越來越少的索引上（以及更小的主表）。

在旁邊

id character varying(48)並且PRIMARY KEY (id, created_at)看起來也很可疑。

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