PostgreSQL 高级索引完全指南：GIN·GiST·BRIN·Partial Index 实战活用

-- 创建表
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    metadata JSONB NOT NULL
);

-- 插入 100 万条测试数据
INSERT INTO products (name, metadata)
SELECT
    'product_' || i,
    jsonb_build_object(
        'category', (ARRAY['electronics', 'clothing', 'food', 'toys'])[1 + (i % 4)],
        'price', (random() * 1000)::int,
        'tags', jsonb_build_array(
            (ARRAY['sale', 'new', 'popular', 'limited'])[1 + (i % 4)],
            (ARRAY['premium', 'budget', 'mid-range'])[1 + (i % 3)]
        ),
        'in_stock', (i % 2 = 0)
    )
FROM generate_series(1, 1000000) AS i;

-- 创建 GIN 索引（默认 jsonb_ops）
CREATE INDEX idx_products_metadata_gin ON products USING gin (metadata);

-- jsonb_path_ops 运算符类（索引更小，仅支持 @>）
CREATE INDEX idx_products_metadata_path ON products USING gin (metadata jsonb_path_ops);

-- 包含检索查询与 EXPLAIN ANALYZE
EXPLAIN ANALYZE
SELECT id, name
FROM products
WHERE metadata @> '{"category": "electronics", "in_stock": true}';

Bitmap Heap Scan on products  (cost=52.01..2084.18 rows=500 width=20)
  (actual time=1.234..5.678 rows=125000 loops=1)
  Recheck Cond: (metadata @> '{"category": "electronics", "in_stock": true}'::jsonb)
  Heap Blocks: exact=8334
  ->  Bitmap Index Scan on idx_products_metadata_gin  (cost=0.00..51.88 rows=500 width=0)
      (actual time=0.891..0.891 rows=125000 loops=1)
        Index Cond: (metadata @> '{"category": "electronics", "in_stock": true}'::jsonb)
Planning Time: 0.152 ms
Execution Time: 12.345 ms

-- 全文检索用的表以及 GIN 索引
CREATE TABLE articles (
    id SERIAL PRIMARY KEY,
    title TEXT NOT NULL,
    body TEXT NOT NULL,
    tsv tsvector GENERATED ALWAYS AS (
        setweight(to_tsvector('english', title), 'A') ||
        setweight(to_tsvector('english', body), 'B')
    ) STORED
);

-- 创建 GIN 索引
CREATE INDEX idx_articles_tsv ON articles USING gin (tsv);

-- 全文检索查询
EXPLAIN ANALYZE
SELECT id, title, ts_rank(tsv, q) AS rank
FROM articles, to_tsquery('english', 'postgresql & indexing') AS q
WHERE tsv @@ q
ORDER BY rank DESC
LIMIT 10;

-- 在标签数组上创建 GIN 索引
CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    title TEXT,
    tags TEXT[]
);

CREATE INDEX idx_posts_tags_gin ON posts USING gin (tags);

-- 数组包含检索
EXPLAIN ANALYZE
SELECT * FROM posts WHERE tags @> ARRAY['postgresql', 'performance'];

-- 数组重叠检索
EXPLAIN ANALYZE
SELECT * FROM posts WHERE tags && ARRAY['database', 'backend'];

-- 安装 PostGIS 扩展
CREATE EXTENSION IF NOT EXISTS postgis;

-- 空间数据表
CREATE TABLE stores (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    location GEOMETRY(Point, 4326) NOT NULL
);

-- 100 万条测试数据（首尔周边的随机坐标）
INSERT INTO stores (name, location)
SELECT
    'store_' || i,
    ST_SetSRID(ST_MakePoint(
        126.9 + random() * 0.2,   -- 经度
        37.4 + random() * 0.2     -- 纬度
    ), 4326)
FROM generate_series(1, 1000000) AS i;

-- 创建 GiST 索引
CREATE INDEX idx_stores_location_gist ON stores USING gist (location);

-- 检索半径 1km 以内的门店
EXPLAIN ANALYZE
SELECT id, name,
       ST_Distance(location::geography,
                   ST_SetSRID(ST_MakePoint(127.0, 37.5), 4326)::geography) AS distance_m
FROM stores
WHERE ST_DWithin(location::geography,
                 ST_SetSRID(ST_MakePoint(127.0, 37.5), 4326)::geography,
                 1000)
ORDER BY distance_m
LIMIT 20;

Limit  (cost=8.45..8.50 rows=20 width=44)
  (actual time=2.345..2.678 rows=20 loops=1)
  ->  Sort  (cost=8.45..8.52 rows=25 width=44)
      (actual time=2.340..2.350 rows=20 loops=1)
        Sort Key: (st_distance(...))
        Sort Method: top-N heapsort  Memory: 27kB
        ->  Index Scan using idx_stores_location_gist on stores
            (cost=0.42..7.89 rows=25 width=44)
            (actual time=0.234..2.123 rows=785 loops=1)
              Index Cond: (location && ...)
              Filter: st_dwithin(...)
Planning Time: 0.456 ms
Execution Time: 2.789 ms

-- 预约系统中的时间范围重叠检索
CREATE TABLE reservations (
    id SERIAL PRIMARY KEY,
    room_id INT NOT NULL,
    period TSTZRANGE NOT NULL,
    guest_name TEXT
);

-- 用 GiST 索引优化范围重叠检索
CREATE INDEX idx_reservations_period_gist ON reservations USING gist (period);

-- 查询与特定时段重叠的预约
EXPLAIN ANALYZE
SELECT * FROM reservations
WHERE period && tstzrange('2026-03-10 14:00', '2026-03-10 18:00', '[)');

-- 用 EXCLUDE 约束防止重叠（必须有 GiST）
ALTER TABLE reservations
ADD CONSTRAINT no_overlap
EXCLUDE USING gist (room_id WITH =, period WITH &&);

-- 用索引扫描查询最近的 10 家门店
SELECT id, name, location <-> ST_SetSRID(ST_MakePoint(127.0, 37.5), 4326) AS dist
FROM stores
ORDER BY location <-> ST_SetSRID(ST_MakePoint(127.0, 37.5), 4326)
LIMIT 10;

-- 事件日志表（时序）
CREATE TABLE events (
    id BIGSERIAL PRIMARY KEY,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    event_type TEXT NOT NULL,
    payload JSONB
);

-- 插入 5000 万条测试数据
INSERT INTO events (created_at, event_type, payload)
SELECT
    '2025-01-01'::timestamptz + (i || ' seconds')::interval,
    (ARRAY['click', 'view', 'purchase', 'signup'])[1 + (i % 4)],
    jsonb_build_object('user_id', (i % 100000), 'value', random() * 100)
FROM generate_series(1, 50000000) AS i;

-- 创建 B-tree 索引（作为比较基准）
CREATE INDEX idx_events_created_btree ON events (created_at);

-- 创建 BRIN 索引
CREATE INDEX idx_events_created_brin ON events USING brin (created_at)
WITH (pages_per_range = 128);

-- 索引大小对比
SELECT
    indexname,
    pg_size_pretty(pg_relation_size(indexname::regclass)) AS index_size
FROM pg_indexes
WHERE tablename = 'events' AND indexname LIKE 'idx_events_created%';

       indexname              | index_size
------------------------------+------------
 idx_events_created_btree     | 1071 MB
 idx_events_created_brin      | 128 kB

-- 使用 BRIN 索引的范围检索
EXPLAIN ANALYZE
SELECT count(*) FROM events
WHERE created_at BETWEEN '2025-06-01' AND '2025-06-30';

Aggregate  (cost=456789.12..456789.13 rows=1 width=8)
  (actual time=234.567..234.568 rows=1 loops=1)
  ->  Bitmap Heap Scan on events  (cost=48.12..445678.90 rows=2592000 width=0)
      (actual time=12.345..198.765 rows=2592000 loops=1)
        Recheck Cond: (created_at >= ... AND created_at <= ...)
        Rows Removed by Recheck: 45678
        Heap Blocks: lossy=19200
        ->  Bitmap Index Scan on idx_events_created_brin  (cost=0.00..47.50 rows=2600000 width=0)
            (actual time=0.234..0.234 rows=192000 loops=1)
Planning Time: 0.123 ms
Execution Time: 256.789 ms

SELECT tablename, attname, correlation
FROM pg_stats
WHERE tablename = 'events' AND attname = 'created_at';
-- correlation 值在 0.95 以上就适合使用 BRIN

-- 在订单表里只索引活跃订单
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    customer_id INT NOT NULL,
    status TEXT NOT NULL DEFAULT 'pending',
    total_amount NUMERIC(10,2),
    created_at TIMESTAMPTZ DEFAULT now()
);

-- 全量索引 vs 部分索引的大小对比
CREATE INDEX idx_orders_status_full ON orders (status, created_at);
CREATE INDEX idx_orders_status_partial ON orders (created_at)
    WHERE status IN ('pending', 'processing');

-- 会用到部分索引的查询
EXPLAIN ANALYZE
SELECT * FROM orders
WHERE status = 'pending'
  AND created_at > now() - interval '7 days'
ORDER BY created_at DESC;

-- 每个用户只允许有一个活跃邮箱
CREATE UNIQUE INDEX idx_unique_active_email
ON users (email) WHERE is_active = true;

-- is_active = false 的行允许邮箱重复
-- is_active = true 的行保证邮箱唯一

-- 为忽略大小写的检索创建表达式索引
CREATE INDEX idx_users_email_lower ON users (lower(email));

-- 这个查询会使用索引
EXPLAIN ANALYZE
SELECT * FROM users WHERE lower(email) = 'user@example.com';

-- 日期提取表达式索引
CREATE INDEX idx_orders_created_date ON orders ((created_at::date));

-- 用于按日期聚合
EXPLAIN ANALYZE
SELECT created_at::date AS order_date, count(*)
FROM orders
WHERE created_at::date = '2026-03-10'
GROUP BY created_at::date;

-- 针对 JSONB 特定键的表达式索引（比 GIN 更小）
CREATE INDEX idx_products_category ON products ((metadata->>'category'));

-- 基于 B-tree，因此可以做等值/范围检索
EXPLAIN ANALYZE
SELECT * FROM products WHERE metadata->>'category' = 'electronics';

-- 优化最近 7 天活跃用户的邮箱检索
CREATE INDEX idx_recent_active_users_email
ON users (lower(email))
WHERE last_login_at > now() - interval '7 days'
  AND is_active = true;

-- 基本的 EXPLAIN ANALYZE
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM products
WHERE metadata @> '{"category": "electronics"}';

-- 执行计划的核心确认要点:
-- 1. Scan 类型: Index Scan vs Bitmap Index Scan vs Seq Scan
-- 2. actual time: 实际耗时
-- 3. rows: 预估行数 vs 实际行数的差距
-- 4. Buffers: shared hit (缓存) vs shared read (磁盘)

-- 查找未被使用的索引
SELECT
    schemaname || '.' || relname AS table,
    indexrelname AS index,
    pg_size_pretty(pg_relation_size(i.indexrelid)) AS index_size,
    idx_scan AS times_used,
    idx_tup_read AS tuples_read
FROM pg_stat_user_indexes i
JOIN pg_index USING (indexrelid)
WHERE idx_scan = 0
  AND NOT indisunique
  AND NOT indisprimary
ORDER BY pg_relation_size(i.indexrelid) DESC;

-- 用 pgstattuple 扩展测量膨胀
CREATE EXTENSION IF NOT EXISTS pgstattuple;

SELECT
    avg_leaf_density,
    leaf_pages,
    empty_pages,
    deleted_pages,
    round(100 - avg_leaf_density, 1) AS bloat_pct
FROM pgstatindex('idx_orders_status_full');

-- bloat_pct 超过 20% 就考虑 REINDEX

-- 重建特定索引（不停机）
REINDEX INDEX CONCURRENTLY idx_orders_status_full;

-- 重建表的所有索引
REINDEX TABLE CONCURRENTLY orders;

-- 重建整个 schema 的索引
REINDEX SCHEMA CONCURRENTLY public;

-- 确认无效索引
SELECT indexrelid::regclass AS index_name,
       indisvalid
FROM pg_index
WHERE NOT indisvalid;

-- 删除无效索引
-- DROP INDEX CONCURRENTLY idx_name_ccnew;

-- 自动找出膨胀超过 20% 的索引的监控查询
SELECT
    schemaname || '.' || tablename AS table,
    indexname,
    pg_size_pretty(pg_relation_size(indexname::regclass)) AS size,
    round(100 - (pgstatindex(indexname)).avg_leaf_density, 1) AS bloat_pct
FROM pg_indexes
WHERE schemaname = 'public'
ORDER BY bloat_pct DESC NULLS LAST;