PostgreSQL 高级查询优化与性能调优实战：从索引设计到执行计划的深度解析#

1
-- 创建 B-Tree 索引
2
CREATE INDEX idx_users_email ON users(email);
3

4
-- 等值查询（高效）
5
SELECT * FROM users WHERE email = 'user@example.com';
6

7
-- 范围查询（高效）
8
SELECT * FROM users WHERE created_at BETWEEN '2024-01-01' AND '2024-12-31';
9

10
-- ORDER BY（高效）
11
SELECT * FROM users ORDER BY created_at DESC LIMIT 10;

1
-- 创建 Hash 索引
2
CREATE INDEX idx_users_phone_hash ON users USING hash(phone);
3

4
-- 等值查询（Hash 更快）
5
SELECT * FROM users WHERE phone = '13800138000';

1
-- 地理空间数据（需要 PostGIS 扩展）
2
CREATE INDEX idx_locations_geom ON locations USING gist(geom);
3

4
-- 范围类型
5
CREATE INDEX idx_events_duration ON events USING gist(duration);
6

7
-- 模糊搜索
8
CREATE INDEX idx_products_name_trgm ON products USING gist(name gist_trgm_ops);

1
-- JSONB 数据
2
CREATE INDEX idx_docs_data ON documents USING gin(data);
3

4
-- 数组类型
5
CREATE INDEX idx_posts_tags ON posts USING gin(tags);
6

7
-- 全文搜索
8
CREATE INDEX idx_articles_search ON articles
9
  USING gin(to_tsvector('chinese', content));

1
-- 错误示范：列顺序不合理
2
CREATE INDEX idx_orders_bad ON orders(status, user_id, created_at);
3

4
-- 正确示范：遵循最左前缀原则
5
CREATE INDEX idx_orders_good ON orders(user_id, status, created_at);
6

7
-- 高效查询：使用了索引的前缀
8
SELECT * FROM orders
9
WHERE user_id = 123
10
  AND status = 'completed'
11
  AND created_at > '2024-01-01';
12

13
-- 低效查询：跳过了索引的第一列
14
SELECT * FROM orders WHERE status = 'completed'; -- 无法使用该索引

1
-- 基础用法
2
EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';
3

4
-- 详细输出（包含实际执行统计）
5
EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
6
SELECT * FROM users WHERE email = 'test@example.com';

1
EXPLAIN (ANALYZE, BUFFERS)
2
SELECT u.*, COUNT(o.id) as order_count
3
FROM users u
4
LEFT JOIN orders o ON u.id = o.user_id
5
WHERE u.created_at > '2024-01-01'
6
GROUP BY u.id
7
HAVING COUNT(o.id) > 5
8
ORDER BY order_count DESC
9
LIMIT 10;

1
-- 通常发生在没有合适索引或表很小的时候
2
Seq Scan on users  (cost=0.00..1500.00 rows=50000 width=150)

1
-- 使用索引定位数据，然后回表获取完整行
2
Index Scan using idx_users_email on users  (cost=0.42..8.44 rows=1 width=150)
3
  Index Cond: (email = 'test@example.com'::text)

1
-- 所有需要的数据都在索引中，无需回表
2
Index Only Scan using idx_users_email_id on users  (cost=0.42..4.44 rows=1 width=8)

1
-- 覆盖索引：包含查询需要的所有列
2
CREATE INDEX idx_users_email_covering ON users(email) INCLUDE (id, name, created_at);

1
-- ❌ 错误：返回不必要的列
2
SELECT * FROM users WHERE id = 1;
3

4
-- ✅ 正确：只查询需要的列
5
SELECT id, name, email FROM users WHERE id = 1;
6

7
-- 优化效果：减少网络传输和内存使用

1
-- ❌ 错误：随着 offset 增大，性能急剧下降
2
SELECT * FROM orders ORDER BY created_at DESC LIMIT 10 OFFSET 100000;
3

4
-- ✅ 正确：使用键集分页（Keyset Pagination）
5
SELECT * FROM orders
6
WHERE created_at < '2024-02-20 10:00:00'  -- 上一页最后一条的时间
7
ORDER BY created_at DESC
8
LIMIT 10;

1
-- ❌ 错误：逐条插入
2
INSERT INTO logs (message, created_at) VALUES ('msg1', now());
3
INSERT INTO logs (message, created_at) VALUES ('msg2', now());
4
-- ... 重复 1000 次
5

6
-- ✅ 正确：批量插入
7
INSERT INTO logs (message, created_at) VALUES
8
  ('msg1', now()),
9
  ('msg2', now()),
10
  ('msg3', now());
11

12
-- 或者使用 COPY 命令（更快）
13
COPY logs (message, created_at) FROM '/path/to/data.csv' WITH CSV;

1
-- ❌ 错误：关联子查询（每一行都执行一次子查询）
2
SELECT u.*,
3
       (SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id) as order_count
4
FROM users u;
5

6
-- ✅ 正确：使用 JOIN
7
SELECT u.*, COALESCE(o.order_count, 0) as order_count
8
FROM users u
9
LEFT JOIN (
10
  SELECT user_id, COUNT(*) as order_count
11
  FROM orders
12
  GROUP BY user_id
13
) o ON u.id = o.user_id;

1
WITH monthly_stats AS (
2
  SELECT
3
    DATE_TRUNC('month', created_at) as month,
4
    user_id,
5
    COUNT(*) as order_count,
6
    SUM(total_amount) as total_amount
7
  FROM orders
8
  WHERE created_at >= '2024-01-01'
9
  GROUP BY DATE_TRUNC('month', created_at), user_id
10
),
11
ranked_users AS (
12
  SELECT *,
13
    RANK() OVER (PARTITION BY month ORDER BY total_amount DESC) as rank
14
  FROM monthly_stats
15
)
16
SELECT * FROM ranked_users WHERE rank <= 10;

1
-- 创建范围分区表
2
CREATE TABLE events (
3
  id BIGSERIAL,
4
  event_type VARCHAR(50),
5
  event_data JSONB,
6
  created_at TIMESTAMP NOT NULL
7
) PARTITION BY RANGE (created_at);
8

9
-- 创建分区
10
CREATE TABLE events_2024_01 PARTITION OF events
11
  FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');
12

13
CREATE TABLE events_2024_02 PARTITION OF events
14
  FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');
15

16
-- 查询时自动只扫描相关分区
17
SELECT * FROM events WHERE created_at >= '2024-01-15' AND created_at < '2024-01-20';

1
-- ✅ 正确：为常用的 JSONB 查询创建 GIN 索引
2
CREATE INDEX idx_products_attrs ON products USING gin(attrs);
3

4
-- 高效查询 JSONB 字段
5
SELECT * FROM products WHERE attrs @> '{"color": "red"}';
6

7
-- ✅ 正确：将经常查询的 JSONB 字段提取为生成列
8
ALTER TABLE products ADD COLUMN price NUMERIC
9
  GENERATED ALWAYS AS ((attrs->>'price')::numeric) STORED;
10

11
CREATE INDEX idx_products_price ON products(price);

1
-- 共享缓冲区（推荐设置为内存的 25%）
2
shared_buffers = 4GB
3

4
-- 工作内存（用于排序、哈希操作）
5
work_mem = 256MB
6

7
-- 维护工作内存（用于 VACUUM、CREATE INDEX 等）
8
maintenance_work_mem = 1GB
9

10
-- 有效缓存大小（查询优化器使用）
11
effective_cache_size = 12GB

1
-- 最大连接数
2
max_connections = 200
3

4
-- 并发维护操作数
5
max_parallel_maintenance_workers = 4
6

7
-- 并行查询工作进程
8
max_parallel_workers_per_gather = 4
9
max_parallel_workers = 8