• B-tree 索引是常见的索引类型，如果您创建索引但不指定类型，它将是默认索引。B-tree索引非常适合对您经常查询的信息进行通用索引。

• BRIN 索引 是块范围索引，专门针对您正在搜索的数据以块为单位的非常大的数据集，例如时间戳和日期范围。众所周知，它们非常高效且节省空间。

• GIST 索引在您的数据库中构建搜索树，常用于空间数据库和全文搜索用例。

• 当您在单个列中有多个值时， GIN 索引很有用，这在您存储数组或 json 数据时很常见。

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

Bitmap Index Scan on idx_weather_type  (cost=.00..8.00 rows=496 width=) (actual time=.027...027 rows=496 loops=1

SELECT * FROM weather where event_type='Winter Storm'

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);

Bitmap Index Scan on idx_weather_type  (cost=0.00..35.98 rows=3158 width=) (actual time=0.247...247 rows=3182 loops=1)
Execution Time: 3.005 ms

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

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);

Bitmap Index Scan on idx_storm_crop  (cost=0.00..38.15 rows=2586 width=) (actual time=0.339...339 rows=2896 loops=1)
Execution Time: 2.204 ms

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

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

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);

Bitmap Index Scan on iot_time  (cost=0.00..12.26 rows=54025 width=) (actual time=0.046...047 rows=10240 loops=1)
Execution Time: 10.513 ms

pg_size_pretty(pg_relation_size('iot_time'));

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%';

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

CREATE INDEX nyc_census_blocks_geom_idx
ON nyc_census_blocks
USING GIST (geom);

Execution Time: 7.575 ms

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=) (actual time=12.164..12.164 rows=3072 loops=1)
Execution Time: 22.017 ms

原文链接：https://mp.weixin.qq.com/s/yIYx9I1cwExhg20fzDO8zA