空間連接和範圍查找的最佳索引策略
語境
我們有兩個表（不是下面範例中使用的真實表。它們來自用於測試的玩具數據庫）：
- Incidents_2（感興趣的列是一個 geom 和一個報告的_at (int8)）
- tmp_points（感興趣的列是一個geom，代表天的時間幀整數，以米為單位的半徑整數）
tmp_points 表中的每一行都有一個位置，我們正在尋找它附近的事件，在一個時間範圍內。每個都可以有不同的半徑和時間範圍。
對於我的虛擬數據，我有 350000 個事件和 1500 個 tmp_points。
我在兩個區域列上都有一個 gist 索引，在
incidents_2.reported_at.事件表包含 6 年的數據。tmp_points 最大時間範圍為 30 天。
第一個查詢在冷執行時大約在 6 秒內返回，隨後在 600 毫秒內返回。我嘗試將事件表劃分為兩個分區。一個覆蓋查詢的有效範圍，另一個覆蓋查詢範圍。這在reported_at 上進行了分區。
第一個查詢仍然掃描兩個分區。第二個查詢僅掃描較小的分區以查找最近的事件。
explain analyze select to_timestamp(i.reported_at), i.id, i.description, i.area, tp.point, tp."name", tp.radius from incidents_2 i join tmp_points tp on to_timestamp(i.reported_at) >= now() - (tp.days*2 || 'days')::interval and ST_Dwithin(i.area, tp.point, tp.radius) explain analyze select reported_at, i.id, i.description, i.area, tp.point, tp."name", tp.radius from incidents_2 i join tmp_points tp on i.reported_at > 1583586702 and ST_Dwithin(i.area, tp.point, tp.radius )問題
雖然我知道第二個查詢採用固定數字，所以規劃者知道它可以敲出一個分區，但實際上我需要的第一個查詢不是。
我嘗試了幾種重寫它的方法，但想不出一種獲得相同結果但只訪問一個分區的方法。除了直接訪問分區。
QUERY PLAN | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------| Nested Loop (cost=0.41..185299.97 rows=51 width=319) (actual time=102.313..662.713 rows=2 loops=1) | -> Seq Scan on tmp_points tp (cost=0.00..28.33 rows=1333 width=61) (actual time=0.008..0.259 rows=1333 loops=1) | -> Append (cost=0.41..138.97 rows=2 width=262) (actual time=0.497..0.497 rows=0 loops=1333) | -> Index Scan using incidents2_old_area_idx on incidents2_old i (cost=0.41..137.65 rows=1 width=262) (actual time=0.479..0.479 rows=0 loops=1333) | Index Cond: (area && _st_expand((tp.point)::geography, (tp.radius)::double precision)) | Filter: ((to_timestamp((reported_at)::double precision) >= (now() - ((((tp.days * 2))::text || 'days'::text))::interval)) AND ((tp.point)::geography && _st_expand(area, (tp.radius)::double precision)) AND _st_dwithin(area, (tp.point)::geogra| Rows Removed by Filter: 90 | -> Index Scan using incidents2_new_area_idx on incidents2_new i_1 (cost=0.27..1.31 rows=1 width=299) (actual time=0.015..0.015 rows=0 loops=1333) | Index Cond: (area && _st_expand((tp.point)::geography, (tp.radius)::double precision)) | Filter: ((to_timestamp((reported_at)::double precision) >= (now() - ((((tp.days * 2))::text || 'days'::text))::interval)) AND ((tp.point)::geography && _st_expand(area, (tp.radius)::double precision)) AND _st_dwithin(area, (tp.point)::geogra| Rows Removed by Filter: 1 | Planning Time: 0.717 ms | Execution Time: 662.747 ms |我唯一的另一個想法是創建查詢的物化視圖並定期刷新它。這將使我能夠保持低於 50 毫秒的響應，但會創建陳舊的數據。我正在與業務部門就數據的新鮮度進行談判，但如果可能的話，我更願意在查詢時這樣做！
更新 16/05 根據一些回饋，我對此進行了一些整理。
PG 版本：11.2。
事故表
CREATE TABLE public.incidents_tz ( id varchar(255) NOT NULL, description text NOT NULL, area geography NULL, reported_at_tz timestamptz NOT NULL, CONSTRAINT incidents_tz_pkey PRIMARY KEY (reported_at_tz, id) ) PARTITION BY RANGE (reported_at_tz); CREATE INDEX incidents_tz_area_gist_index ON ONLY public.incidents_tz USING gist (area); CREATE INDEX incidentstz_started_at_index ON ONLY public.incidents_tz USING btree (reported_at_tz);Tmp點表
CREATE TABLE public.tmp_points ( point geometry NULL, "name" varchar NULL, radius int4 NULL, days int4 NULL ); CREATE INDEX tmp_points_st_expand_idx ON public.tmp_points USING gist (st_expand(point, (radius)::double precision));我現在使用第一個答案中給出的範例：
explain analyze SELECT i.reported_at_tz, i.id, i.description, i.area, tp.point, tp."name", tp.radius, tp.days FROM incidents_tz i JOIN tmp_points tp ON i.reported_at_tz >= now() - interval '1 day' * tp.days -- 1 day? AND ST_Dwithin(i.area, tp.point, tp.radius)不幸的是，這仍然導致了計劃（同時使用了兩個分區）：
UERY PLAN | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------| ested Loop (cost=0.41..57673.48 rows=22 width=298) (actual time=0.241..178.554 rows=6111 loops=1) | -> Seq Scan on tmp_points tp (cost=0.00..27.79 rows=1279 width=61) (actual time=0.007..0.159 rows=1279 loops=1) | -> Append (cost=0.41..45.05 rows=2 width=238) (actual time=0.094..0.138 rows=5 loops=1279) | -> Index Scan using incidents_tz_old_area_idx on incidents_tz_old i (cost=0.41..39.30 rows=1 width=245) (never executed) | Index Cond: (area && _st_expand((tp.point)::geography, (tp.radius)::double precision)) | Filter: ((reported_at_tz >= (now() - ('1 day'::interval * (tp.days)::double precision))) AND ((tp.point)::geography && _st_expand(area, (tp.radius)::double precision)) AND _st_dwithin(area, (tp.point)::geography, (tp.radius)::double precisio| -> Index Scan using incidents_tz_new_area_idx on incidents_tz_new i_1 (cost=0.41..5.74 rows=1 width=211) (actual time=0.093..0.136 rows=5 loops=1279) | Index Cond: (area && _st_expand((tp.point)::geography, (tp.radius)::double precision)) | Filter: ((reported_at_tz >= (now() - ('1 day'::interval * (tp.days)::double precision))) AND ((tp.point)::geography && _st_expand(area, (tp.radius)::double precision)) AND _st_dwithin(area, (tp.point)::geography, (tp.radius)::double precisio| Rows Removed by Filter: 12 | lanning Time: 0.314 ms | xecution Time: 178.857 ms |
為什麼
reported_at (int8)？時間戳的一般優選實現是timestamptz. 您節省了來迴轉換的成本和麻煩。並且您對這些值進行了內置的健全性檢查。另外，它是您查詢中一個主要問題的根源：
... join tmp_points tp **on to_timestamp(i.reported_at) >= now() - (tp.days*2 || 'days')::interval** ...由於多種原因，這很糟糕。
- 替換
(tp.days*2 || 'days')::interval為interval '2 days' * tp.days。那是一次乘法，而不是相對昂貴的字元串連接、乘法和類型轉換。- 更重要的是，使用以下等效表達式將計算從表列移開：
ON i.reported_at >= EXTRACT (EPOCH FROM now() - interval '2 days' * tp.days)這樣，在與許多列值進行比較之前，必須計算*一次該值。*該表達式是“sargable”，這意味著可以使用索引，並且如果分區鍵基於- 正是您似乎正在尋找的內容，則分區修剪現在是一個選項。
reported_at``reported_at詢問：
SELECT to_timestamp(i.reported_at), i.id, i.description, i.area, tp.point, tp."name", tp.radius FROM incidents_2 i JOIN tmp_points tp ON ST_Dwithin(i.area, tp.point, tp.radius) WHERE i.reported_at >= EXTRACT (EPOCH FROM now() - interval '2 days' * tp.days);我還轉換為
WHERE子句，因為謂詞僅適用於一個表。這在 100% 等效的同時更直覺。看：隨著
incidents_2.reported_at實現，timestamptz這可能會更簡單，更快，但是：SELECT i.reported_at, i.id, i.description, i.area, tp.point, tp."name", tp.radius FROM incidents_2 i JOIN tmp_points tp ON ST_Dwithin(i.area, tp.point, tp.radius) WHERE i.reported_at >= now() - interval '1 day' * tp.days; -- 1 day?我也把間隔減半。明顯的邏輯是檢查事件，因為一旦.
days應用建議的效果
應用建議的改進後，您似乎不相信：
不幸的是，這仍然導致了計劃（同時使用了兩個分區）：
但實際上只執行了*“新”分區的**一個*計劃。正是我的目標：
-> 在事件_tz_old i 上使用事件_tz_old_area_idx 進行索引掃描 （成本=0.41..39.30 行=1 寬度=245）**（從未執行）**大膽強調我的。手冊中有關分區修剪的大引號：
分區修剪不僅可以在給定查詢的計劃期間執行，也可以在其執行期間執行。這很有用，因為當子句包含在查詢計劃時其值未知的表達式時，它可以允許修剪更多分區，例如，在
PREPARE語句中定義的參數，使用從子查詢中獲得的值，或在嵌套循環連接的內側。執行期間的分區修剪可以在以下任何時間執行：
- 在查詢計劃初始化期間。對於在執行的初始化階段已知的參數值，可以在此處執行分區修剪。在此階段修剪的分區不會顯示在查詢的
EXPLAINorEXPLAIN ANALYZE中。通過觀察輸出中的“Subplans Removed”屬性，可以確定在此階段刪除的分區數EXPLAIN。- 在查詢計劃的實際執行期間。也可以在這裡執行分區修剪以使用僅在實際查詢執行期間才知道的值來刪除分區。這包括來自子查詢的值和來自執行時參數的值，例如來自參數化嵌套循環連接的值。由於這些參數的值在查詢執行期間可能會多次更改，因此只要分區修剪使用的執行參數之一發生更改，就會執行分區修剪。確定在此階段是否修剪分區需要仔細檢查輸出中的
loops屬性EXPLAIN ANALYZE。與不同分區對應的子計劃可能具有不同的值，具體取決於它們在執行期間被修剪的次數。 有些可能會顯示為(never executed)好像每次都被修剪過。再次大膽強調我的。
由於每個
(point, radius)intmp_points(rows=1333) 在嵌套循環中訪問索引，Postgres 不能在計劃階段應用分區修剪，但它可以在執行期間應用。因此，新查詢
rows=6111在 179 毫秒內檢索到，而您的舊查詢rows=2在 663 毫秒內檢索到 (!!)。如果我見過一個，那是一種改進。更智能的索引而不是單獨的分區？
最新行的單獨分區需要大量成本和復雜性。對於大型表，具有更多分區的聲明性分區可能仍然有意義。
但是考慮一個具有更智能索引的**表。**對於初學者，多列索引，如：
CREATE INDEX foo ON incidents USING gist (reported_at_tz, area);首先使用通常更具選擇性的表達方式。
btree_gist必須安裝附加模組。看：由於您的查詢專門針對少數最新行，因此部分索引可能更有意義。不幸的是，感興趣的時間範圍是一個移動目標，取決於目前時間 (
now())。這使得優化變得更加困難（對於分區也是如此）。從恆定的截止時間開始：CREATE INDEX foo ON incidents USING gist (area, reported_at_tz) WHERE reported_at_tz >= '2020-05-01 00:00+0';將截止時間調整為
'2020-05-01 00:00+0'您用於分區的時間。現在，
area作為第一個索引表達式。根據reported_at_tz仍然的選擇性，您可能會將其作為附加索引表達式刪除。然後在這裡繼續閱讀：