第05章-空间索引与性能优化
第05章：空间索引与性能优化
5.1 空间索引概述
5.1.1 为什么需要空间索引
空间数据的查询与传统数据不同，无法使用简单的 B-Tree 索引进行优化。空间查询通常涉及范围搜索、最近邻查询、空间关系判断等操作，这些操作需要专门的空间索引结构来加速。
没有空间索引时的问题：
-- 无索引查询：需要全表扫描，检查每一行
-- 假设有 100 万行数据，每次查询都需要检查所有行
EXPLAIN ANALYZE
SELECT * FROM large_table
WHERE ST_Intersects(geom, ST_MakeEnvelope(116, 39, 117, 40, 4326));
-- 结果可能显示：
-- Seq Scan on large_table (cost=0.00..50000.00 rows=1000 width=100)
-- 执行时间: 5000ms
有空间索引后的改善：
-- 创建空间索引
CREATE INDEX idx_large_table_geom ON large_table USING GIST (geom);
-- 相同查询，使用索引后
EXPLAIN ANALYZE
SELECT * FROM large_table
WHERE ST_Intersects(geom, ST_MakeEnvelope(116, 39, 117, 40, 4326));
-- 结果显示：
-- Index Scan using idx_large_table_geom on large_table
-- 执行时间: 50ms (提升 100 倍)
5.1.2 PostGIS 支持的索引类型
┌─────────────────────────────────────────────────────────────┐
│ PostGIS 空间索引类型 │
├─────────────────────────────────────────────────────────────┤
│ │
│ GiST (Generalized Search Tree) │
│ ├── 最常用的空间索引类型 │
│ ├── 支持所有几何类型 │
│ ├── 支持 2D 和 N-D 操作 │
│ └── 基于 R-Tree 实现 │
│ │
│ SP-GiST (Space-Partitioned GiST) │
│ ├── 适用于非平衡数据分布 │
│ ├── 基于四叉树/八叉树 │
│ └── 某些场景下性能更好 │
│ │
│ BRIN (Block Range Index) │
│ ├── 适用于大表且数据有序 │
│ ├── 索引文件非常小 │
│ └── 建索引速度快 │
│ │
└─────────────────────────────────────────────────────────────┘
5.2 GiST 索引详解
5.2.1 GiST 索引原理
GiST（Generalized Search Tree）是 PostgreSQL 的通用搜索树框架，PostGIS 使用它实现了类似 R-Tree 的空间索引结构。
R-Tree 原理：
┌─────────────────────────────────────────────────────────────┐
│ R-Tree 结构示意图 │
│ │
│ 根节点 (包含整个空间范围的 MBR) │
│ │ │
│ ┌────┴────┐ │
│ │ │ │
│ MBR1 MBR2 (中间节点，包含子区域的 MBR) │
│ │ │ │
│ ┌──┴──┐ ┌──┴──┐ │
│ │ │ │ │ │
│ obj1 obj2 obj3 obj4 (叶子节点，存储实际几何对象的 MBR) │
│ │
│ MBR = Minimum Bounding Rectangle (最小外接矩形) │
└─────────────────────────────────────────────────────────────┘
查询过程：
- 从根节点开始，检查查询范围与子节点 MBR 是否相交
- 递归下降到相交的子节点
- 在叶子节点进行精确几何判断
5.2.2 创建 GiST 索引
-- 基本语法
CREATE INDEX index_name ON table_name USING GIST (geometry_column);
-- 创建 2D GiST 索引（默认）
CREATE INDEX idx_poi_geom ON poi USING GIST (geom);
-- 创建 N-D GiST 索引（支持 3D 查询）
CREATE INDEX idx_poi_geom_nd ON poi USING GIST (geom gist_geometry_ops_nd);
-- 创建带 SRID 检查的索引
CREATE INDEX idx_poi_geom_srid ON poi USING GIST (geom)
WHERE ST_SRID(geom) = 4326;
-- 创建部分索引（只索引特定数据）
CREATE INDEX idx_poi_geom_beijing ON poi USING GIST (geom)
WHERE city = '北京';
-- 创建并发索引（不阻塞表写入）
CREATE INDEX CONCURRENTLY idx_poi_geom_concurrent ON poi USING GIST (geom);
-- 创建覆盖索引（INCLUDE，PostgreSQL 11+）
CREATE INDEX idx_poi_geom_covering ON poi USING GIST (geom) INCLUDE (name, category);
5.2.3 GiST 索引操作符
-- GiST 索引支持的操作符
-- 2D 操作符
&& -- 边界框相交 (Bounding Box Intersects)
@ -- 包含 (Contains)
~ -- 包含于 (Contained by)
~= -- 相同 (Same)
<-> -- 距离 (Distance)
-- N-D 操作符（需要 gist_geometry_ops_nd）
&&& -- N-D 边界框相交
<<->> -- N-D 距离
-- 示例：边界框相交查询（非常快）
SELECT * FROM poi
WHERE geom && ST_MakeEnvelope(116, 39, 117, 40, 4326);
-- 示例：精确相交查询（先用边界框过滤，再精确判断）
SELECT * FROM poi
WHERE geom && ST_MakeEnvelope(116, 39, 117, 40, 4326) -- 使用索引
AND ST_Intersects(geom, ST_MakeEnvelope(116, 39, 117, 40, 4326)); -- 精确判断
-- 示例：最近邻查询（KNN）
SELECT name, geom <-> ST_SetSRID(ST_MakePoint(116.4, 39.9), 4326) AS distance
FROM poi
ORDER BY geom <-> ST_SetSRID(ST_MakePoint(116.4, 39.9), 4326)
LIMIT 10;
5.2.4 GiST 索引调优
-- 查看索引大小
SELECT pg_size_pretty(pg_relation_size('idx_poi_geom')) AS index_size;
-- 查看索引使用统计
SELECT
schemaname, tablename, indexname,
idx_scan, -- 索引扫描次数
idx_tup_read, -- 通过索引读取的行数
idx_tup_fetch -- 通过索引获取的行数
FROM pg_stat_user_indexes
WHERE indexrelname = 'idx_poi_geom';
-- 重建索引（优化索引结构）
REINDEX INDEX idx_poi_geom;
-- 并发重建索引
REINDEX INDEX CONCURRENTLY idx_poi_geom;
-- 设置索引存储参数
CREATE INDEX idx_poi_geom_optimized ON poi USING GIST (geom)
WITH (fillfactor = 90); -- 默认是 90
-- 聚簇表（按空间顺序重排数据）
CLUSTER poi USING idx_poi_geom;
-- 查看索引是否被使用
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM poi WHERE ST_Intersects(geom, ST_MakeEnvelope(116, 39, 117, 40, 4326));
5.3 SP-GiST 索引
5.3.1 SP-GiST 原理
SP-GiST（Space-Partitioned GiST）使用空间分区策略，基于四叉树（2D）或八叉树（3D）结构。
┌─────────────────────────────────────────────────────────────┐
│ 四叉树结构示意图 │
│ │
│ ┌──────────┬──────────┐ │
│ │ NW │ NE │ │
│ │ ┌──┬──┐│ │ │
│ │ │NW│NE││ * │ │
│ │ ├──┼──┤│ │ │
│ │ │SW│SE││ │ │
│ ├───┴──┴──┼──────────┤ │
│ │ SW │ SE │ │
│ │ │ * * │ │
│ │ * │ │ │
│ └─────────┴──────────┘ │
│ │
│ * 表示数据点 │
│ 四叉树递归分割空间，直到每个区域内数据量足够小 │
└─────────────────────────────────────────────────────────────┘
5.3.2 创建 SP-GiST 索引
-- 创建 2D SP-GiST 索引
CREATE INDEX idx_poi_spgist ON poi USING SPGIST (geom);
-- 创建 3D SP-GiST 索引
CREATE INDEX idx_poi_spgist_3d ON poi USING SPGIST (geom spgist_geometry_ops_3d);
-- 创建 N-D SP-GiST 索引
CREATE INDEX idx_poi_spgist_nd ON poi USING SPGIST (geom spgist_geometry_ops_nd);
5.3.3 GiST vs SP-GiST
| 特性 | GiST | SP-GiST |
|---|---|---|
| 索引结构 | R-Tree | 四叉树/八叉树 |
| 构建速度 | 中等 | 较快 |
| 索引大小 | 较大 | 较小 |
| 点查询 | 好 | 更好 |
| 范围查询 | 好 | 中等 |
| 更新性能 | 好 | 中等 |
| 数据分布 | 通用 | 适合均匀分布 |
-- 比较两种索引的性能
-- 创建测试表
CREATE TABLE index_test AS
SELECT generate_series(1, 1000000) AS id,
ST_SetSRID(ST_MakePoint(
random() * 360 - 180,
random() * 180 - 90
), 4326) AS geom;
-- 创建两种索引
CREATE INDEX idx_test_gist ON index_test USING GIST (geom);
CREATE INDEX idx_test_spgist ON index_test USING SPGIST (geom);
-- 比较索引大小
SELECT indexname, pg_size_pretty(pg_relation_size(indexname::regclass))
FROM pg_indexes
WHERE tablename = 'index_test';
-- 强制使用特定索引进行查询
SET enable_indexscan = on;
SET enable_seqscan = off;
-- 测试 GiST 索引
DROP INDEX idx_test_spgist;
EXPLAIN ANALYZE SELECT * FROM index_test
WHERE geom && ST_MakeEnvelope(-10, -10, 10, 10, 4326);
-- 测试 SP-GiST 索引
DROP INDEX idx_test_gist;
CREATE INDEX idx_test_spgist ON index_test USING SPGIST (geom);
EXPLAIN ANALYZE SELECT * FROM index_test
WHERE geom && ST_MakeEnvelope(-10, -10, 10, 10, 4326);
5.4 BRIN 索引
5.4.1 BRIN 索引原理
BRIN（Block Range Index）索引记录数据块范围的汇总信息。它特别适合大表且数据具有自然排序的场景。
┌─────────────────────────────────────────────────────────────┐
│ BRIN 索引结构 │
│ │
│ 数据块 1 (1-128行) → 记录：min_geom_bbox, max_geom_bbox │
│ 数据块 2 (129-256行) → 记录：min_geom_bbox, max_geom_bbox │
│ 数据块 3 (257-384行) → 记录：min_geom_bbox, max_geom_bbox │
│ ... │
│ │
│ 优点： │
│ - 索引非常小（通常只有表大小的 1%） │
│ - 构建速度快 │
│ - 维护成本低 │
│ │
│ 缺点： │
│ - 需要数据有序 │
│ - 查询性能不如 GiST │
│ - 不支持 KNN 查询 │
└─────────────────────────────────────────────────────────────┘
5.4.2 创建 BRIN 索引
-- 创建 BRIN 索引
CREATE INDEX idx_poi_brin ON poi USING BRIN (geom);
-- 指定 pages_per_range（默认 128）
CREATE INDEX idx_poi_brin_32 ON poi USING BRIN (geom) WITH (pages_per_range = 32);
-- 创建 2D BRIN 索引
CREATE INDEX idx_poi_brin_2d ON poi USING BRIN (geom brin_geometry_inclusion_ops_2d);
-- 创建 3D BRIN 索引
CREATE INDEX idx_poi_brin_3d ON poi USING BRIN (geom brin_geometry_inclusion_ops_3d);
-- 创建 4D BRIN 索引
CREATE INDEX idx_poi_brin_4d ON poi USING BRIN (geom brin_geometry_inclusion_ops_4d);
5.4.3 BRIN 索引适用场景
-- 场景：时序空间数据（GPS 轨迹）
CREATE TABLE gps_tracks (
id SERIAL PRIMARY KEY,
device_id VARCHAR(50),
recorded_at TIMESTAMP,
geom GEOMETRY(POINT, 4326)
);
-- 数据按时间顺序插入，空间上也有一定聚集性
-- 此时 BRIN 索引效果很好
-- 创建复合 BRIN 索引
CREATE INDEX idx_gps_brin ON gps_tracks USING BRIN (recorded_at, geom);
-- 比较索引大小
-- 假设表有 1000 万行
-- GiST 索引可能 500MB
-- BRIN 索引可能 5MB
-- 查询示例（BRIN 可以快速排除大量数据块）
SELECT * FROM gps_tracks
WHERE recorded_at BETWEEN '2024-01-01' AND '2024-01-02'
AND geom && ST_MakeEnvelope(116, 39, 117, 40, 4326);
5.4.4 何时使用 BRIN
| 条件 | 推荐索引 |
|---|---|
| 表较小（<100万行） | GiST |
| 表很大，数据随机分布 | GiST |
| 表很大，数据有序 | BRIN |
| 需要 KNN 查询 | GiST |
| 存储空间紧张 | BRIN |
| 频繁插入更新 | BRIN |
| 高查询性能要求 | GiST |
5.5 索引选择策略
5.5.1 综合对比
-- 创建测试数据集
CREATE TABLE index_comparison (
id SERIAL PRIMARY KEY,
geom GEOMETRY(POINT, 4326),
category VARCHAR(50),
created_at TIMESTAMP DEFAULT NOW()
);
-- 插入随机数据
INSERT INTO index_comparison (geom, category)
SELECT
ST_SetSRID(ST_MakePoint(
116 + random() * 2, -- 经度范围 116-118
39 + random() * 2 -- 纬度范围 39-41
), 4326),
(ARRAY['A', 'B', 'C', 'D'])[ceil(random() * 4)]
FROM generate_series(1, 1000000);
-- 创建不同类型的索引
CREATE INDEX idx_comp_gist ON index_comparison USING GIST (geom);
CREATE INDEX idx_comp_spgist ON index_comparison USING SPGIST (geom);
CREATE INDEX idx_comp_brin ON index_comparison USING BRIN (geom);
-- 分析表
ANALYZE index_comparison;
-- 查看索引大小
SELECT
indexname,
pg_size_pretty(pg_relation_size(indexname::regclass)) AS size
FROM pg_indexes
WHERE tablename = 'index_comparison';
-- 性能测试函数
CREATE OR REPLACE FUNCTION test_index_performance(test_index_name TEXT)
RETURNS TABLE(index_used TEXT, execution_time NUMERIC, rows_returned BIGINT) AS $$
DECLARE
start_time TIMESTAMP;
end_time TIMESTAMP;
row_count BIGINT;
BEGIN
-- 禁用所有索引
EXECUTE 'SET enable_indexscan = off';
EXECUTE 'SET enable_bitmapscan = off';
-- 只启用指定索引类型
IF test_index_name = 'gist' THEN
EXECUTE 'SET enable_indexscan = on';
ELSIF test_index_name = 'seq' THEN
-- 保持禁用状态
ELSE
EXECUTE 'SET enable_indexscan = on';
EXECUTE 'SET enable_bitmapscan = on';
END IF;
start_time := clock_timestamp();
SELECT COUNT(*) INTO row_count
FROM index_comparison
WHERE geom && ST_MakeEnvelope(116.5, 39.5, 117, 40, 4326);
end_time := clock_timestamp();
RETURN QUERY SELECT
test_index_name,
EXTRACT(EPOCH FROM (end_time - start_time)) * 1000,
row_count;
-- 恢复设置
EXECUTE 'RESET enable_indexscan';
EXECUTE 'RESET enable_bitmapscan';
END;
$$ LANGUAGE plpgsql;
5.5.2 索引选择决策树
开始
│
├─ 数据量 < 10,000 行？
│ ├─ 是 → 不需要索引，全表扫描足够快
│ └─ 否 ↓
│
├─ 需要 KNN（最近邻）查询？
│ ├─ 是 → 使用 GiST 索引
│ └─ 否 ↓
│
├─ 数据有自然排序且表很大（>1000万行）？
│ ├─ 是 → 考虑 BRIN 索引
│ └─ 否 ↓
│
├─ 数据均匀分布？
│ ├─ 是 → SP-GiST 可能更好
│ └─ 否 → 使用 GiST 索引
│
└─ 默认选择 GiST 索引
5.6 查询优化
5.6.1 使用 EXPLAIN ANALYZE
-- 基本用法
EXPLAIN ANALYZE
SELECT * FROM poi WHERE ST_Intersects(geom, ST_MakeEnvelope(116, 39, 117, 40, 4326));
-- 详细输出
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM poi WHERE ST_Intersects(geom, ST_MakeEnvelope(116, 39, 117, 40, 4326));
-- JSON 格式输出（便于程序解析）
EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
SELECT * FROM poi WHERE ST_Intersects(geom, ST_MakeEnvelope(116, 39, 117, 40, 4326));
-- 解读关键指标
-- Seq Scan: 全表扫描（无索引或索引不可用）
-- Index Scan: 索引扫描
-- Bitmap Index Scan: 位图索引扫描
-- Index Only Scan: 仅索引扫描（覆盖索引）
-- actual time: 实际执行时间
-- rows: 返回行数
-- Buffers: 缓冲区命中情况
5.6.2 优化空间查询
-- 1. 使用边界框过滤（&&）加速
-- 差：直接使用 ST_Intersects
SELECT * FROM poi WHERE ST_Intersects(geom, query_geom);
-- 好：先用 && 过滤，再精确判断
SELECT * FROM poi
WHERE geom && query_geom
AND ST_Intersects(geom, query_geom);
-- 2. 使用 ST_DWithin 代替 ST_Distance
-- 差：计算所有距离再过滤
SELECT * FROM poi WHERE ST_Distance(geom::geography, point::geography) < 1000;
-- 好：使用 ST_DWithin（可以使用索引）
SELECT * FROM poi WHERE ST_DWithin(geom::geography, point::geography, 1000);
-- 3. 优化 KNN 查询
-- 差：计算所有距离排序
SELECT * FROM poi ORDER BY ST_Distance(geom, query_point) LIMIT 10;
-- 好：使用 <-> 操作符（索引支持）
SELECT * FROM poi ORDER BY geom <-> query_point LIMIT 10;
-- 4. 使用 Geography 时的优化
-- Geography 类型查询通常较慢
-- 可以存储 Geometry 副本用于快速过滤
ALTER TABLE poi ADD COLUMN geom_4326 GEOMETRY(POINT, 4326);
UPDATE poi SET geom_4326 = geog::geometry;
CREATE INDEX idx_poi_geom_4326 ON poi USING GIST (geom_4326);
-- 先用 Geometry 过滤，再用 Geography 计算
SELECT * FROM poi
WHERE geom_4326 && ST_Expand(query_geom, 0.01)
AND ST_DWithin(geog, query_geog, 1000);
-- 5. 减少几何传输
-- 差：返回完整几何
SELECT id, name, geom FROM large_polygons;
-- 好：只返回需要的数据
SELECT id, name, ST_Centroid(geom) AS center FROM large_polygons;
-- 或者简化几何
SELECT id, name, ST_Simplify(geom, 0.001) AS simplified_geom FROM large_polygons;
5.6.3 复杂查询优化
-- 空间连接优化
-- 差：笛卡尔积后过滤
SELECT a.name, b.name
FROM table_a a, table_b b
WHERE ST_Intersects(a.geom, b.geom);
-- 好：确保两个表都有空间索引
CREATE INDEX idx_a_geom ON table_a USING GIST (geom);
CREATE INDEX idx_b_geom ON table_b USING GIST (geom);
-- 使用 LATERAL 优化
SELECT a.name, b.name
FROM table_a a
CROSS JOIN LATERAL (
SELECT name FROM table_b b
WHERE ST_Intersects(a.geom, b.geom)
LIMIT 100
) b;
-- 分块处理大范围查询
CREATE OR REPLACE FUNCTION process_in_chunks(bbox GEOMETRY)
RETURNS TABLE(id INT, name TEXT) AS $$
DECLARE
chunk_size NUMERIC := 0.1; -- 每块 0.1 度
x_min NUMERIC; y_min NUMERIC;
x_max NUMERIC; y_max NUMERIC;
x NUMERIC; y NUMERIC;
BEGIN
x_min := ST_XMin(bbox);
y_min := ST_YMin(bbox);
x_max := ST_XMax(bbox);
y_max := ST_YMax(bbox);
FOR x IN SELECT generate_series(x_min, x_max, chunk_size) LOOP
FOR y IN SELECT generate_series(y_min, y_max, chunk_size) LOOP
RETURN QUERY
SELECT p.id, p.name
FROM poi p
WHERE p.geom && ST_MakeEnvelope(x, y, x + chunk_size, y + chunk_size, 4326)
AND ST_Intersects(p.geom, bbox);
END LOOP;
END LOOP;
END;
$$ LANGUAGE plpgsql;
5.7 性能调优
5.7.1 PostgreSQL 配置优化
-- 查看当前配置
SHOW shared_buffers;
SHOW work_mem;
SHOW effective_cache_size;
SHOW random_page_cost;
-- 推荐配置（在 postgresql.conf 中设置）
-- 内存配置
-- shared_buffers: 系统内存的 25%
-- effective_cache_size: 系统内存的 75%
-- work_mem: 根据并发连接数调整，通常 64MB-256MB
-- 查询优化器配置
-- random_page_cost: SSD 建议 1.1，HDD 建议 4.0
-- effective_io_concurrency: SSD 建议 200，HDD 建议 2
-- 并行查询配置
-- max_parallel_workers_per_gather: 建议 2-4
-- max_parallel_workers: 建议 CPU 核心数
-- 动态修改配置（当前会话）
SET work_mem = '256MB';
SET random_page_cost = 1.1;
SET effective_io_concurrency = 200;
-- 重载配置
SELECT pg_reload_conf();
5.7.2 表维护
-- 分析表统计信息
ANALYZE poi;
-- 详细分析（包括直方图）
ANALYZE VERBOSE poi;
-- 查看统计信息
SELECT
attname,
n_distinct,
correlation,
most_common_vals,
histogram_bounds
FROM pg_stats
WHERE tablename = 'poi' AND attname = 'geom';
-- 清理死元组
VACUUM poi;
-- 完全清理并压缩
VACUUM FULL poi;
-- 自动清理设置
ALTER TABLE poi SET (autovacuum_vacuum_scale_factor = 0.1);
ALTER TABLE poi SET (autovacuum_analyze_scale_factor = 0.05);
-- 查看表膨胀情况
SELECT
schemaname, tablename,
pg_size_pretty(pg_total_relation_size(schemaname || '.' || tablename)) AS total_size,
pg_size_pretty(pg_relation_size(schemaname || '.' || tablename)) AS table_size,
n_live_tup,
n_dead_tup,
round(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 2) AS dead_ratio
FROM pg_stat_user_tables
WHERE tablename = 'poi';
5.7.3 分区表
对于大型空间表，使用分区可以显著提高查询性能：
-- 按地理区域分区
CREATE TABLE spatial_data_partitioned (
id SERIAL,
name VARCHAR(100),
geom GEOMETRY(POINT, 4326),
region VARCHAR(20)
) PARTITION BY LIST (region);
-- 创建分区
CREATE TABLE spatial_data_north PARTITION OF spatial_data_partitioned
FOR VALUES IN ('north');
CREATE TABLE spatial_data_south PARTITION OF spatial_data_partitioned
FOR VALUES IN ('south');
CREATE TABLE spatial_data_east PARTITION OF spatial_data_partitioned
FOR VALUES IN ('east');
CREATE TABLE spatial_data_west PARTITION OF spatial_data_partitioned
FOR VALUES IN ('west');
-- 为每个分区创建空间索引
CREATE INDEX idx_north_geom ON spatial_data_north USING GIST (geom);
CREATE INDEX idx_south_geom ON spatial_data_south USING GIST (geom);
CREATE INDEX idx_east_geom ON spatial_data_east USING GIST (geom);
CREATE INDEX idx_west_geom ON spatial_data_west USING GIST (geom);
-- 使用范围分区（按经度）
CREATE TABLE spatial_data_by_lon (
id SERIAL,
name VARCHAR(100),
geom GEOMETRY(POINT, 4326)
) PARTITION BY RANGE (ST_X(geom));
CREATE TABLE spatial_data_lon_70_90 PARTITION OF spatial_data_by_lon
FOR VALUES FROM (70) TO (90);
CREATE TABLE spatial_data_lon_90_110 PARTITION OF spatial_data_by_lon
FOR VALUES FROM (90) TO (110);
CREATE TABLE spatial_data_lon_110_130 PARTITION OF spatial_data_by_lon
FOR VALUES FROM (110) TO (130);
-- 查询时自动分区修剪
EXPLAIN SELECT * FROM spatial_data_by_lon
WHERE ST_X(geom) BETWEEN 100 AND 120;
5.7.4 并行查询
-- 启用并行查询
SET max_parallel_workers_per_gather = 4;
SET parallel_tuple_cost = 0.01;
SET parallel_setup_cost = 100;
-- 对大表进行并行扫描
EXPLAIN (ANALYZE)
SELECT COUNT(*) FROM large_spatial_table
WHERE ST_Intersects(geom, ST_MakeEnvelope(100, 30, 120, 40, 4326));
-- 设置表级并行度
ALTER TABLE large_spatial_table SET (parallel_workers = 4);
-- 并行聚合
SET enable_parallel_append = on;
SET enable_parallel_hash = on;
-- 检查是否使用了并行
EXPLAIN (ANALYZE, VERBOSE)
SELECT region, COUNT(*)
FROM large_spatial_table
WHERE geom && ST_MakeEnvelope(100, 30, 120, 40, 4326)
GROUP BY region;
5.8 监控与诊断
5.8.1 查询性能监控
-- 启用查询日志
ALTER SYSTEM SET log_min_duration_statement = 1000; -- 记录超过1秒的查询
SELECT pg_reload_conf();
-- 安装 pg_stat_statements 扩展
CREATE EXTENSION pg_stat_statements;
-- 查看最耗时的空间查询
SELECT
query,
calls,
total_time / 1000 AS total_seconds,
mean_time / 1000 AS avg_seconds,
rows
FROM pg_stat_statements
WHERE query LIKE '%ST_%' OR query LIKE '%geom%'
ORDER BY total_time DESC
LIMIT 10;
-- 重置统计
SELECT pg_stat_statements_reset();
5.8.2 索引使用监控
-- 查看索引使用情况
SELECT
schemaname,
tablename,
indexrelname,
idx_scan,
idx_tup_read,
idx_tup_fetch,
pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE schemaname = 'public'
ORDER BY idx_scan DESC;
-- 查找未使用的索引
SELECT
schemaname || '.' || relname AS table,
indexrelname AS index,
pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0 AND NOT indisunique
ORDER BY pg_relation_size(indexrelid) DESC;
-- 查找缺失索引的表（大表无空间索引）
SELECT
c.relname AS table_name,
pg_size_pretty(pg_relation_size(c.oid)) AS table_size,
a.attname AS geom_column
FROM pg_class c
JOIN pg_attribute a ON c.oid = a.attrelid
JOIN pg_type t ON a.atttypid = t.oid
LEFT JOIN pg_index i ON c.oid = i.indrelid
WHERE t.typname = 'geometry'
AND c.relkind = 'r'
AND NOT EXISTS (
SELECT 1 FROM pg_index idx
JOIN pg_class ic ON idx.indexrelid = ic.oid
JOIN pg_am am ON ic.relam = am.oid
WHERE idx.indrelid = c.oid AND am.amname = 'gist'
)
ORDER BY pg_relation_size(c.oid) DESC;
5.8.3 锁和死锁监控
-- 查看当前锁
SELECT
pid,
usename,
query_start,
state,
query
FROM pg_stat_activity
WHERE state = 'active'
AND query LIKE '%ST_%';
-- 查看锁等待
SELECT
blocked_locks.pid AS blocked_pid,
blocked_activity.usename AS blocked_user,
blocking_locks.pid AS blocking_pid,
blocking_activity.usename AS blocking_user,
blocked_activity.query AS blocked_statement
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
JOIN pg_catalog.pg_locks blocking_locks
ON blocking_locks.locktype = blocked_locks.locktype
AND blocking_locks.relation = blocked_locks.relation
JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.granted;
-- 终止长时间运行的查询
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'active'
AND query_start < NOW() - INTERVAL '10 minutes'
AND pid != pg_backend_pid();
5.9 本章小结
本章详细介绍了 PostGIS 的空间索引与性能优化：
- 索引概述：了解了空间索引的重要性和类型
- GiST 索引：深入学习了最常用的空间索引类型
- SP-GiST 索引：了解了基于空间分区的索引
- BRIN 索引：掌握了适合大表的轻量级索引
- 索引选择：学习了不同场景下的索引选择策略
- 查询优化：掌握了空间查询优化技巧
- 性能调优：了解了配置优化、表维护、分区等技术
- 监控诊断：学习了性能监控和问题诊断方法
5.10 下一步
在下一章中，我们将学习几何构造函数，包括：
- 创建点、线、面等几何对象
- 从文本格式创建几何
- 从其他格式转换几何
- 几何生成与变换
相关资源：
浙公网安备 33010602011771号