B-tree不是万能药：PostgreSQL索引失效的7种高频场景与破解方案

-- 创建测试表与索引
CREATE TABLE user_actions (
    id SERIAL PRIMARY KEY,
    device_id VARCHAR(32) NOT NULL,  -- 字符类型存储设备ID
    action_time TIMESTAMPTZ NOT NULL
);
CREATE INDEX idx_device_id ON user_actions(device_id);

-- 失效查询（错误使用数值类型查询）
EXPLAIN ANALYZE
SELECT * FROM user_actions 
WHERE device_id = 12345678;  -- 整数类型条件

-- 执行计划输出
Seq Scan on user_actions  (cost=0.00..10238.90 rows=1 width=48)
  Filter: ((device_id)::text = '12345678'::text)

WHERE device_id = 12345678::text  -- 将参数转为文本类型

ALTER TABLE user_actions 
ALTER COLUMN device_id TYPE INTEGER USING device_id::integer;

-- 索引列参与计算
EXPLAIN ANALYZE
SELECT * FROM sales 
WHERE EXTRACT(YEAR FROM order_date) = 2023; 

-- 输出结果
Seq Scan on sales  (cost=0.00..26482.00 rows=5000 width=36)
  Filter: (date_part('year'::text, order_date) = '2023'::double precision)

-- 创建基于表达式的函数索引
CREATE INDEX idx_sales_order_year ON sales(EXTRACT(YEAR FROM order_date));

-- 优化后执行计划
Index Scan using idx_sales_order_year on sales  (cost=0.29..348.29 rows=100 width=36)
  Index Cond: (date_part('year'::text, order_date) = 2023::double precision)

-- 测试表：100万条文本数据
CREATE TABLE documents (
    id BIGSERIAL PRIMARY KEY,
    content TEXT
);
CREATE INDEX idx_content ON documents(content);

-- 场景1：后缀匹配（索引有效）
EXPLAIN ANALYZE
SELECT * FROM documents WHERE content LIKE 'postgres%';

-- 输出：Index Scan using idx_content...

-- 场景2：前导通配符（索引失效）
EXPLAIN ANALYZE
SELECT * FROM documents WHERE content LIKE '%index%';

-- 输出：Parallel Seq Scan...

-- 启用pg_trgm扩展
CREATE EXTENSION pg_trgm;

-- 创建GIN索引
CREATE INDEX idx_content_trigram ON documents USING gin(content gin_trgm_ops);

-- 优化后查询
EXPLAIN ANALYZE
SELECT * FROM documents WHERE content LIKE '%index%';

-- 执行计划
Bitmap Heap Scan on documents  (cost=52.89..228.43 rows=100 width=68)
  Recheck Cond: (content ~~ '%index%'::text)
  ->  Bitmap Index Scan on idx_content_trigram  (cost=0.00..52.86 rows=100)
        Index Cond: (content ~~ '%index%'::text)

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    cancel_reason TEXT
);
CREATE INDEX idx_cancel_reason ON orders(cancel_reason);

-- 查询未取消的订单（约90%数据）
EXPLAIN ANALYZE
SELECT * FROM orders WHERE cancel_reason IS NULL;

-- 输出结果：Seq Scan...

-- 创建针对NULL的局部索引
CREATE INDEX idx_null_cancel_reason ON orders(cancel_reason)
WHERE cancel_reason IS NULL;

-- 优化后执行计划
Index Scan using idx_null_cancel_reason on orders  (cost=0.12..8.14 rows=1 width=68)
  Index Cond: (cancel_reason IS NULL)

CREATE TABLE events (
    id BIGSERIAL,
    tenant_id INT NOT NULL,
    event_type SMALLINT NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE INDEX idx_event_composite ON events(tenant_id, event_type, created_at);

-- 有效查询（使用最左列）
EXPLAIN ANALYZE
SELECT * FROM events 
WHERE tenant_id = 1001 AND event_type = 5;

-- 输出：Index Scan...

-- 失效查询（跳过tenant_id）
EXPLAIN ANALYZE
SELECT * FROM events 
WHERE event_type = 5 AND created_at > '2023-01-01';

-- 输出：Seq Scan...

-- 查看列分布统计
SELECT attname, n_distinct, most_common_vals 
FROM pg_stats 
WHERE tablename='orders' AND attname='status';

-- 输出示例：
attname | n_distinct |          most_common_vals
--------+------------+----------------------------------
 status |          4 | {1,2,3,4}  -- 4种状态均匀分布

-- 查询覆盖50%数据的场景
EXPLAIN ANALYZE
SELECT * FROM orders WHERE status IN (1,2); -- 50%数据

-- 结果：即使有索引，优化器选择Seq Scan

SET enable_seqscan = off;  -- 临时关闭全表扫描

EXPLAIN ANALYZE
SELECT * FROM orders WHERE status IN (1,2);

-- 输出变为：Bitmap Heap Scan...

-- 步骤1：清空表并禁用autovacuum
TRUNCATE large_table;
ALTER TABLE large_table SET (autovacuum_enabled = off);

-- 步骤2：批量插入100万条特定特征数据
INSERT INTO large_table SELECT generate_series(1,1000000), 'special_value';

-- 步骤3：强制使用旧统计信息（不触发ANALYZE）
EXPLAIN ANALYZE
SELECT * FROM large_table WHERE special_column = 'special_value';

-- 输出：误用索引（实际应全表扫描更快）

-- 手动更新统计信息（全表）
ANALYZE VERBOSE large_table;

-- 加大特定列统计粒度
ALTER TABLE large_table 
ALTER COLUMN special_column 
SET STATISTICS 1000;  -- 默认100

-- 重新收集统计信息
ANALYZE large_table;