組合時間和位置數據的索引

我有一個多 TB 表，其中包含某個項目 ID 和時間戳的地理位置數據。

它按時間分區，每個月都有一個分區（~500GB），並在項目 id 和時間索引上聚集（包括分區）。

CREATE TABLE data(
 itemid integer not null,
 ts timestamp not null,
 pos geometry(Point,4326) not null,
 otherdata varchar not null -- Stand in for other columns
) partition by range(ts);

CREATE INDEX data_cluster ON data (itemid, ts);
CREATE INDEX data_brin ON data USING brin (itemid, ts, pos) with (pages_per_range = 8);

CREATE TABLE data_201901 PARTITION OF data
   FOR VALUES FROM ('2019-01-01') TO ('2019-02-01');

CREATE TABLE data_201902 PARTITION OF data
   FOR VALUES FROM ('2019-02-01') TO ('2019-03-01');

這對於我們最常見的查詢表現得非常好，其中一個或少量項目的數據在一段時間內（幾個小時到幾天）被查詢。

我們想做的第二類查詢是在某個時間範圍內或附近的所有項目。我們的意圖是布林指數可以用於此，因為(item_id,ts)它與 高度相關pos。

像這樣的東西：

select itemid, ts, pos, otherdata from data
where ts >= '2019-02-02 00:00:00' AND ts < '2019-02-03 14:00:00'
AND st_within(pos,  st_setsrid(
               st_geomfromgeojson('{"coordinates":"coordinates":[[[-74.05059814453125,40.6920928987952],[-73.92013549804688,40.6920928987952],[-73.92013549804688,40.79405848578324],[-74.05059814453125,40.79405848578324],[-74.05059814453125,40.6920928987952]]],"type": "Polygon"}'),
               4326
             ) :: geometry)

然而，我們看到正常的 btree 索引用於此目的，並且查詢需要很長時間才能有用（幾分鐘到一個多小時）

啟用這些查詢的方法是什麼？

• 是否有結合時間和地理空間索引？
• 有什麼辦法可以使布林指數被使用嗎？
• 最好的方法是在一天之類的較小時間範圍內進行分區並為每個分區創建地理索引？

根據 eppesuig 的要求進行編輯，上述查詢的範例執行計劃：

Gather  (cost=1000.70..71744000.35 rows=1933 width=44) (actual time=164868.705..1227645.633 rows=65701 loops=1)
  Output: data_201902.itemid, data_201902.ts, data_201902.pos, data_201902.otherdata
  Workers Planned: 2
  Workers Launched: 2
  Buffers: shared hit=18009528 read=12157953 dirtied=246522 written=253711
  I/O Timings: read=2117627.130 write=2645.225
  ->  Parallel Append  (cost=0.70..71742807.05 rows=805 width=44) (actual time=164838.218..1227566.491 rows=21900 loops=3)
        Buffers: shared hit=18009528 read=12157953 dirtied=246522 written=253711
        I/O Timings: read=2117627.130 write=2645.225
        Worker 0: actual time=164823.732..1227566.470 rows=23522 loops=1
          Buffers: shared hit=6011928 read=4078259 dirtied=81890 written=85133
          I/O Timings: read=709048.959 write=873.459
        Worker 1: actual time=164824.015..1227556.922 rows=22578 loops=1
          Buffers: shared hit=5974480 read=4022282 dirtied=82190 written=84063
          I/O Timings: read=702613.742 write=856.134
        ->  Parallel Index Scan using data_201902_cluster on public.data_201902  (cost=0.70..71742803.02 rows=805 width=44) (actual time=164838.216..1227552.572 rows=21900 loops=3)
              Output: data_201902.itemid, data_201902.ts, data_201902.pos
              Index Cond: ((data_201902.ts >= '2019-02-01 00:00:00'::timestamp without time zone) AND (data_201902.ts < '2019-02-03 00:00:00'::timestamp without time zone))
              Filter: (('010...4540'::geometry, data_201902.pos))
              Rows Removed by Filter: 40950205
              Buffers: shared hit=18009528 read=12157953 dirtied=246522 written=253711
              I/O Timings: read=2117627.130 write=2645.225
              Worker 0: actual time=164823.730..1227551.115 rows=23522 loops=1
                Buffers: shared hit=6011928 read=4078259 dirtied=81890 written=85133
                I/O Timings: read=709048.959 write=873.459
              Worker 1: actual time=164824.013..1227542.124 rows=22578 loops=1
                Buffers: shared hit=5974480 read=4022282 dirtied=82190 written=84063
                I/O Timings: read=702613.742 write=856.134
Planning Time: 5.442 ms
Execution Time: 1227669.990 ms

您可以在空間和時間數據上創建多列索引。

create extension btree_gist;
create index on data using gist (ts, pos);

使用 GiST，您通常希望在查詢中更有選擇性地使用的列在索引中排在第一位。我懷疑那是“ts”，但我不確定。您可能想嘗試兩種方式。

多列 BRIN 索引與多個獨立的單列 BRIN 索引基本相同。如果您的數據聚集在“item_id”上，那麼 BRIN 索引中的“ts”列不太可能有用，除非“item_id”和“ts”彼此高度相關。

最好的方法是在一天之類的較小時間範圍內進行分區並為每個分區創建地理索引？

也許吧，但這不是我的第一選擇。我會USING GIST (ts,pos)先嘗試索引。看起來你甚至還沒有嘗試過USING GIST (pos)現有的分區。如果不先嘗試您已經擁有的東西，我不會四處更改分區。

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