Postgres索引詳解

banq發表於2022-02-04
索引
適用於 Postgres 初學者的 b-tree、GIN 和 GIST 索引示例。

資料庫索引的作用類似於書後的索引部分。資料庫索引儲存有關資料行在表中的位置的資訊，因此資料庫不必掃描整個表以獲取資訊。當資料庫有要檢索的查詢時，它首先轉到索引，然後使用該資訊檢索請求的資料。

索引是它們自己的資料結構，它們是 Postgres 資料定義語言 (DDL) 的一部分。它們與資料表和其他物件一起儲存在磁碟上。
  • B-tree 索引是最常見的索引型別，如果您建立索引但不指定型別，它將是預設索引。B 樹索引非常適合對您經常查詢的資訊進行通用索引。
  • BRIN 索引 是塊範圍索引，專門針對您正在搜尋的資料以塊為單位的非常大的資料集，例如時間戳和日期範圍。眾所周知，它們非常高效且節省空間。
  • GIST 索引在您的資料庫中構建搜尋樹，最常用於空間資料庫和全文搜尋用例。
  • 當您在單個列中有多個值時， GIN 索引很有用，這在您儲存陣列或 json 資料時很常見。




使用 Explain analyze

Explain analyze會給你提供資訊，如查詢計劃、執行時間，以及任何查詢的其他有用資訊。因此，當你在使用索引時，你將使用explain analyze檢查索引，以審查查詢路徑和查詢時間。

你會看到查詢計劃表明是一個 "Seq掃描"，或一個順序掃描。這意味著它掃描了表中的每一條資料行，看它是否符合查詢條件。你可能會猜到，對於較大的表，順序掃描可能會佔用相當多的時間，所以這就是索引節省資料庫工作量的地方。

Seq Scan on weather (cost=0.00..168.00 rows=496 width=102) (actual time=0.011..0.181 rows=100 loops=1)


如果你使用的是索引，你會在Explain的結果中看到索引掃描。

Bitmap Index Scan on idx_weather_type (cost=0.00..8.00 rows=496 width=0) (actual time=0.027..0.027 rows=496 loops=1




分析資料庫

ANALYZE命令收集關於內部查詢計劃器使用的表或資料庫的資訊。在某些情況下，你可能想在新增索引之前或之後執行這個命令，以便資料庫使用最新的查詢計劃。在我的測試中，這似乎影響了較大的資料庫。



B-樹樣本

對於B-Tree樣本，我使用了一些開放的天氣資料，資料事件按型別、損害、時間和地點分類1。對於只是一個非常基本的索引，我打算找到冬季風暴的一切。一旦我新增了這個索引，這就意味著為了獲得這些資料，資料庫不必掃描所有的天氣事件來為我獲得關於惡劣天氣事件的額外資料，它已經知道在哪裡可以找到這些資料。

SELECT * FROM weather where event_type='Winter Storm'




在持久索引之前的explain analyze：

Seq Scan on weather (cost=0.00..9204.64 rows=3158 width=853) (actual time=0.008..27.619 rows=3182 loops=1)

Execution Time: 27.778 ms




索引：

CREATE INDEX idx_weather_type ON weather(event_type);




索引之後的explain 分析：

Bitmap Index Scan on idx_weather_type (cost=0.00..35.98 rows=3158 width=0) (actual time=0.247..0.247 rows=3182 loops=1)

Execution Time: 3.005 ms




多列B型樹索引

索引並不總是隻為單列建立的 - Postgres也支援多列索引。如果你知道你將同時對多個列進行大量的查詢，那麼這些索引就會有用：

SELECT * FROM weather WHERE event_type='Winter Storm' AND damage_crops > '0'


在索引之前的explain分析：

Seq Scan on weather (cost=0.00..9402.36 rows=2586 width=853) (actual time=0.007..67.365 rows=2896 loops=1)

Execution Time: 67.499 ms




建立多列索引：

CREATE INDEX idx_storm_crop ON weather(event_type,damage_crops);


索引之後的explain分析：

Bitmap Index Scan on idx_storm_crop (cost=0.00..38.15 rows=2586 width=0) (actual time=0.339..0.339 rows=2896 loops=1)

Execution Time: 2.204 ms




哦，.... 如果你在建立索引的過程中迷失了方向，需要看看你已經得到了什麼，這將顯示你在一個特定表上的所有索引。

SELECT indexname, indexdef FROM pg_indexes WHERE tablename = 'weatherevents';




Drop index indexname可以刪除索引





BRIN指數樣本

在使用大型資料集，特別是有時間序列或時間戳的資料時，BRIN通常非常有用。我使用了一個物聯網資料集2的樣本，每天有成千上萬的資料行。

SELECT device, humidity FROM iot WHERE ts between '2020-07-13 0:00' AND '2020-07-14 0:00'




索引之前的Explain分析：

Parallel Seq Scan on iot (cost=0.00..10363.95 rows=896 width=28) (actual time=12.710..42.080 rows=16707 loops=3)

Execution Time: 67.851 ms




建立索引：

CREATE INDEX iot_time ON iot USING brin(ts);




再次explain分析結果：

Bitmap Index Scan on iot_time (cost=0.00..12.26 rows=54025 width=0) (actual time=0.046..0.047 rows=10240 loops=1)

Execution Time: 10.513 ms


你會經常聽到BRIN索引是非常節省空間的。所以當你透過索引工作時，你可能想查詢實際索引的大小。比如說

pg_size_pretty(pg_relation_size('iot_time'));



使用GIST的基本空間索引

如果你在你的資料庫中使用空間資料，你可能有很多資料，索引可能是至關重要的。對於空間索引，我使用了PostGIS教程3中的資料和例子。如果你剛開始使用空間資料，我強烈推薦這個教程的總體內容。

SQL query



SELECT count(blocks.blkid)

FROM nyc_census_blocks blocks

JOIN nyc_subway_stations subways

ON ST_Contains(blocks.geom, subways.geom)

WHERE subways.name LIKE 'B%';


開始 explain analyze



Timing: Generation 4.364 ms, Inlining 360.628 ms, Optimization 615.663 ms, Emission 559.573 ms, Total 1540.227 ms

Execution Time: 1467.916 ms




實現樣別索引spatial GIST index



CREATE INDEX nyc_census_blocks_geom_idx

ON nyc_census_blocks

USING GIST (geom);




explain analyze時間：



Execution Time: 7.575 ms






用於JSON的GIN索引

JSON資料在Postgres使用者中被廣泛採用，Postgres核心專案也透過廣泛的功能來支援JSON資料型別的採用。如果你的資料在每個欄位中都列出了幾個物件，那麼有一些索引型別可以真正幫助你。GIN索引型別通常用於此。在這個例子中，我使用了NASA的一個json檔案，其中有流星的位置資訊。



SQL query

SELECT data -> 'name' as name FROM meteors WHERE data @> '{"mass": "100"}';




索引前的解釋分析：



Parallel Seq Scan on meteors (cost=0.00..23926.28 rows=4245 width=32) (actual time=0.065..114.114 rows=1024 loops=3) Execution Time: 123.698 ms




建立索引：

CREATE INDEX gin_test ON meteors USING gin(data)




索引後解釋分析：

Bitmap Index Scan on gin_test (cost=0.00..116.40 rows=10187 width=0) (actual time=12.164..12.164 rows=3072 loops=1) Execution Time: 22.017 ms






用索引找到合適的位置

你不應該在你要執行一個一次性查詢的時候臨時建立一個索引。一個好的索引計劃需要計劃和測試。索引是儲存在磁碟上的，所以它們也會佔用空間，這也是一個考慮因素。對於每一個插入的新資料行或更新的現有資料行，資料庫會自動更新索引條目。索引絕對也會對資料庫寫操作的效能產生影響，所以一定要研究一些。就像Craig最近說的，每個人都需要找到他們的Goldilocks索引：不要太大，不要太小，但要恰到好處。

相關文章