-- 创建测试表：100 万个随机点
CREATE TABLE test_points AS
SELECT
    id,
    ST_SetSRID(ST_MakePoint(
        random() * 360 - 180,   -- 经度 -180 ~ 180
        random() * 180 - 90     -- 纬度 -90 ~ 90
    ), 4326) AS geom
FROM generate_series(1, 1000000) AS id;

-- 无索引查询
EXPLAIN ANALYZE
SELECT count(*) FROM test_points
WHERE ST_Intersects(geom, ST_GeomFromText('POLYGON((116 39, 117 39, 117 40, 116 40, 116 39))', 4326));
-- 预期: Seq Scan, ~500-800ms

-- 创建空间索引
CREATE INDEX idx_test_points_geom ON test_points USING GIST (geom);

-- 有索引查询
EXPLAIN ANALYZE
SELECT count(*) FROM test_points
WHERE ST_Intersects(geom, ST_GeomFromText('POLYGON((116 39, 117 39, 117 40, 116 40, 116 39))', 4326));
-- 预期: Index Scan, ~5-20ms (提升 25-100 倍)

-- 标准 GiST 索引
CREATE INDEX idx_table_geom ON table_name USING GIST (geom);

-- 并发创建（不锁表）
CREATE INDEX CONCURRENTLY idx_table_geom ON table_name USING GIST (geom);

-- 查看索引大小
SELECT pg_size_pretty(pg_relation_size('idx_table_geom')) AS index_size;

-- 查看索引使用情况
SELECT indexrelname, idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes
WHERE indexrelname = 'idx_table_geom';

-- 含过滤条件的索引（部分索引）
CREATE INDEX idx_active_stores_geom
ON stores USING GIST (geom)
WHERE status = 'active';

-- 复合索引（先过滤非空间条件）
CREATE INDEX idx_stores_type_geom
ON stores (store_type) INCLUDE (geom);
-- 注意：PostgreSQL 不支持 GiST 复合索引与 B-tree 混合
-- 通常的做法是先用 B-tree 过滤，再用 GiST 过滤空间

-- 创建 SP-GiST 索引
CREATE INDEX idx_points_spgist ON test_points USING SPGIST (geom);

-- 适用场景对比
-- GiST: 通用，支持所有空间操作
-- SP-GiST: 大量随机分布的点数据，KNN 查询更快

-- 创建 BRIN 索引
CREATE INDEX idx_events_brin ON events USING BRIN (geom);

-- BRIN 索引大小对比
SELECT
    pg_size_pretty(pg_relation_size('idx_events_gist')) AS gist_size,
    pg_size_pretty(pg_relation_size('idx_events_brin')) AS brin_size;
-- 典型结果: GIST 80MB, BRIN 256KB

-- 调整 BRIN 索引的块范围大小
CREATE INDEX idx_events_brin ON events USING BRIN (geom)
WITH (pages_per_range = 32);  -- 默认 128，更小的值更精确但索引更大

-- && 操作符：边界框相交过滤
SELECT name FROM districts
WHERE geom && ST_GeomFromText('POLYGON((116 39, 117 39, 117 40, 116 40, 116 39))', 4326);

-- @ 操作符：被包含于
SELECT name FROM pois
WHERE geom @ ST_GeomFromText('POLYGON((116 39, 117 39, 117 40, 116 40, 116 39))', 4326);

-- <-> 操作符：KNN 最近邻
SELECT name FROM stores
ORDER BY geom <-> ST_SetSRID(ST_MakePoint(116.4074, 39.9042), 4326)
LIMIT 5;

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT name FROM stores
WHERE ST_DWithin(
    geom::geography,
    ST_SetSRID(ST_MakePoint(116.4074, 39.9042), 4326)::geography,
    3000
);

-- 问题 1: 查询没有使用索引
-- 原因: 使用了 Geography 类型但索引是 Geometry
-- 解决: 确保索引类型与查询类型一致

-- 检查索引类型
SELECT indexname, indexdef FROM pg_indexes WHERE tablename = 'stores';

-- 问题 2: 索引选择性差
-- 原因: 查询范围太大，优化器选择全表扫描
-- 解决: 缩小查询范围，或强制使用索引

-- 强制使用索引（谨慎使用）
SET enable_seqscan = off;
EXPLAIN ANALYZE SELECT ...;
RESET enable_seqscan;

-- 问题 3: 统计信息过时
-- 解决: 运行 ANALYZE
ANALYZE stores;

-- 索引膨胀时需要重建
REINDEX INDEX CONCURRENTLY idx_stores_geom;

-- 查看索引膨胀率
SELECT
    schemaname, tablename, indexname,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
    pg_size_pretty(pg_total_relation_size(indexrelid)) AS total_size
FROM pg_stat_user_indexes
JOIN pg_index USING (indexrelid)
WHERE idx_scan > 0;

-- 大量 DELETE/UPDATE 后需要 VACUUM
VACUUM (VERBOSE, ANALYZE) stores;

-- 设置自动 VACUUM 参数
ALTER TABLE stores SET (
    autovacuum_vacuum_scale_factor = 0.05,
    autovacuum_analyze_scale_factor = 0.02
);

-- 对于多级行政区划，按级别创建部分索引
CREATE INDEX idx_districts_province ON districts USING GIST (geom)
WHERE level = '省';

CREATE INDEX idx_districts_city ON districts USING GIST (geom)
WHERE level = '市';

CREATE INDEX idx_districts_county ON districts USING GIST (geom)
WHERE level = '区县';

-- 使用 INCLUDE 子句避免回表
CREATE INDEX idx_stores_covering ON stores USING GIST (geom)
INCLUDE (name, address, store_type);
-- 注意: GiST 的 INCLUDE 支持在 PostgreSQL 15+ 中可用

-- 当表 A 和表 B 之间有频繁的双向空间查询时
-- 在两个表上都创建索引
CREATE INDEX idx_a_geom ON table_a USING GIST (geom);
CREATE INDEX idx_b_geom ON table_b USING GIST (geom);

-- JOIN 查询会自动选择较小表做驱动表
SELECT a.name, b.name
FROM table_a a
JOIN table_b b ON ST_Intersects(a.geom, b.geom);

-- 创建测试数据
DROP TABLE IF EXISTS bench_points;
CREATE TABLE bench_points AS
SELECT
    id,
    ST_SetSRID(ST_MakePoint(
        100 + random() * 30,   -- 经度 100-130
        20 + random() * 30     -- 纬度 20-50
    ), 4326) AS geom
FROM generate_series(1, 1000000) AS id;

-- 测试 1: 无索引
DROP INDEX IF EXISTS idx_bench_geom;
EXPLAIN (ANALYZE, BUFFERS)
SELECT count(*) FROM bench_points
WHERE geom && ST_GeomFromText('POLYGON((116 39, 117 39, 117 40, 116 40, 116 39))', 4326);

-- 测试 2: GiST 索引
CREATE INDEX idx_bench_geom ON bench_points USING GIST (geom);
ANALYZE bench_points;
EXPLAIN (ANALYZE, BUFFERS)
SELECT count(*) FROM bench_points
WHERE geom && ST_GeomFromText('POLYGON((116 39, 117 39, 117 40, 116 40, 116 39))', 4326);

-- 测试 3: SP-GiST 索引
DROP INDEX idx_bench_geom;
CREATE INDEX idx_bench_spgist ON bench_points USING SPGIST (geom);
ANALYZE bench_points;
EXPLAIN (ANALYZE, BUFFERS)
SELECT count(*) FROM bench_points
WHERE geom && ST_GeomFromText('POLYGON((116 39, 117 39, 117 40, 116 40, 116 39))', 4326);

-- 查看并行设置
SHOW max_parallel_workers_per_gather;
SHOW parallel_tuple_cost;
SHOW parallel_setup_cost;

-- 启用并行查询（对于大表）
SET max_parallel_workers_per_gather = 4;

-- 查看查询计划是否使用并行
EXPLAIN ANALYZE
SELECT count(*) FROM bench_points
WHERE geom && ST_GeomFromText('POLYGON((116 39, 117 39, 117 40, 116 40, 116 39))', 4326);