PostgreSQL 高级查询与性能优化实战 2026 | 数据库性能完全指南

PostgreSQL 是功能最强大的开源关系型数据库之一。当数据量增长后，掌握高级查询技巧和性能优化方法成为后端工程师的必备能力。本文将系统讲解窗口函数、CTE 递归、索引策略、执行计划分析及分区表等核心优化手段。

---

一、窗口函数（Window Functions）

1.1 基本语法

-- 窗口函数语法
function_name() OVER (
  [PARTITION BY column1, column2, ...]
  [ORDER BY column3 [ASC|DESC]]
  [frame_clause]
)

1.2 排名函数

-- 员工表
CREATE TABLE employees (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100),
  department VARCHAR(50),
  salary NUMERIC(10, 2),
  hire_date DATE
);

-- 按部门排名薪资（相同薪资排名相同，跳号）
SELECT
  name,
  department,
  salary,
  RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank_in_dept,
  DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dense_rank,
  ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS row_num
FROM employees
ORDER BY department, rank_in_dept;

-- 结果示例：
-- name    | department | salary | rank_in_dept | dense_rank | row_num
-- Alice   | Engineering | 120000 |      1       |     1      |   1
-- Bob     | Engineering | 110000 |      2       |     2      |   2
-- Charlie | Engineering | 110000 |      2       |     2      |   3
-- Dave    | Engineering | 100000 |      4       |     3      |   4

1.3 聚合窗口函数

-- 累计薪资（从入职最早到当前员工）
SELECT
  name,
  department,
  salary,
  hire_date,
  SUM(salary) OVER (
    PARTITION BY department
    ORDER BY hire_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS cumulative_salary,
  -- 部门平均薪资
  AVG(salary) OVER (PARTITION BY department) AS dept_avg,
  -- 移动平均（当前行和前后各一行）
  AVG(salary) OVER (
    PARTITION BY department
    ORDER BY hire_date
    ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
  ) AS moving_avg
FROM employees;

1.4 偏移函数

-- 与前一名员工的薪资差距
SELECT
  name,
  department,
  salary,
  LAG(salary, 1) OVER (
    PARTITION BY department ORDER BY salary DESC
  ) AS prev_salary,
  salary - LAG(salary, 1) OVER (
    PARTITION BY department ORDER BY salary DESC
  ) AS salary_diff,
  -- 下一名员工薪资
  LEAD(salary, 1) OVER (
    PARTITION BY department ORDER BY salary DESC
  ) AS next_salary,
  -- 部门第一/最后薪资
  FIRST_VALUE(salary) OVER (
    PARTITION BY department ORDER BY salary DESC
  ) AS highest_in_dept,
  LAST_VALUE(salary) OVER (
    PARTITION BY department ORDER BY salary DESC
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS lowest_in_dept
FROM employees;

1.5 分桶函数

-- 将薪资分为 4 个等级
SELECT
  name,
  salary,
  NTILE(4) OVER (ORDER BY salary DESC) AS salary_quartile
FROM employees;

-- 结果：
-- name  | salary | salary_quartile
-- Alice | 120000 |       1
-- Bob   | 110000 |       1
-- ...   |  ...   |       2
-- Dave  |  80000 |       4

---

二、CTE 与递归查询

2.1 普通 CTE

-- 查询各部门薪资高于部门平均的员工
WITH dept_avg AS (
  SELECT
    department,
    AVG(salary) AS avg_salary
  FROM employees
  GROUP BY department
)
SELECT
  e.name,
  e.department,
  e.salary,
  d.avg_salary
FROM employees e
JOIN dept_avg d ON e.department = d.department
WHERE e.salary > d.avg_salary
ORDER BY e.department, e.salary DESC;

2.2 多 CTE 组合

-- 综合统计：部门人数、平均薪资、最高薪资、高薪人数
WITH
  dept_stats AS (
    SELECT
      department,
      COUNT(*) AS emp_count,
      AVG(salary) AS avg_salary,
      MAX(salary) AS max_salary
    FROM employees
    GROUP BY department
  ),
  high_earners AS (
    SELECT
      department,
      COUNT(*) AS high_count
    FROM employees
    WHERE salary > 100000
    GROUP BY department
  )
SELECT
  d.department,
  d.emp_count,
  ROUND(d.avg_salary, 2) AS avg_salary,
  d.max_salary,
  COALESCE(h.high_count, 0) AS high_earners_count
FROM dept_stats d
LEFT JOIN high_earners h ON d.department = h.department
ORDER BY d.avg_salary DESC;

2.3 递归 CTE

-- 组织架构树
CREATE TABLE org_chart (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100),
  manager_id INTEGER REFERENCES org_chart(id)
);

-- 递归查询：从 CEO 开始遍历整个组织树
WITH RECURSIVE org_tree AS (
  -- 基础查询：找到根节点（CEO）
  SELECT
    id,
    name,
    manager_id,
    0 AS depth,
    name::TEXT AS path,
    ARRAY[name] AS name_path
  FROM org_chart
  WHERE manager_id IS NULL

  UNION ALL

  -- 递归查询：找到下属
  SELECT
    o.id,
    o.name,
    o.manager_id,
    ot.depth + 1,
    ot.path || ' > ' || o.name,
    ot.name_path || o.name
  FROM org_chart o
  JOIN org_tree ot ON o.manager_id = ot.id
)
SELECT
  REPEAT('  ', depth) || name AS org_structure,
  depth,
  path
FROM org_tree
ORDER BY name_path;

-- 结果示例：
--   org_structure  | depth | path
--   CEO            |   0   | CEO
--     VP-Eng       |   1   | CEO > VP-Eng
--       Eng-Mgr-1  |   2   | CEO > VP-Eng > Eng-Mgr-1
--       Eng-Mgr-2  |   2   | CEO > VP-Eng > Eng-Mgr-2
--     VP-Sales     |   1   | CEO > VP-Sales

2.4 递归查询：层级评论

-- 评论表
CREATE TABLE comments (
  id SERIAL PRIMARY KEY,
  post_id INTEGER,
  parent_id INTEGER REFERENCES comments(id),
  author VARCHAR(100),
  content TEXT,
  created_at TIMESTAMP DEFAULT NOW()
);

-- 查询某帖子的评论树
WITH RECURSIVE comment_tree AS (
  SELECT
    id,
    post_id,
    parent_id,
    author,
    content,
    created_at,
    0 AS depth,
    ARRAY[id] AS path
  FROM comments
  WHERE post_id = 42 AND parent_id IS NULL

  UNION ALL

  SELECT
    c.id,
    c.post_id,
    c.parent_id,
    c.author,
    c.content,
    c.created_at,
    ct.depth + 1,
    ct.path || c.id
  FROM comments c
  JOIN comment_tree ct ON c.parent_id = ct.id
  WHERE c.post_id = 42
)
SELECT
  REPEAT('  ', depth) || author || ': ' || LEFT(content, 50) AS comment_thread,
  depth,
  created_at
FROM comment_tree
ORDER BY path, created_at;

---

三、索引优化

3.1 索引类型

-- 1. B-Tree 索引（默认，适合等值查询和范围查询）
CREATE INDEX idx_emp_salary ON employees(salary);
CREATE INDEX idx_emp_dept_salary ON employees(department, salary);

-- 2. Hash 索引（仅等值查询，不支持范围）
CREATE INDEX idx_emp_name_hash ON employees USING HASH(name);

-- 3. GIN 索引（全文搜索、JSONB、数组）
CREATE INDEX idx_products_tags ON products USING GIN(tags);
CREATE INDEX idx_products_meta ON products USING GIN(metadata jsonb_path_ops);

-- 4. GiST 索引（几何类型、范围类型）
CREATE INDEX idx_stores_location ON stores USING GIST(location);

-- 5. BRIN 索引（大表、有序数据，占用极小）
CREATE INDEX idx_logs_created ON logs USING BRIN(created_at);

-- 6. 部分索引（只索引满足条件的行）
CREATE INDEX idx_active_users ON users(email) WHERE active = true;

-- 7. 表达式索引
CREATE INDEX idx_emp_lower_name ON employees(LOWER(name));

-- 8. 覆盖索引（INCLUDE 包含额外列）
CREATE INDEX idx_emp_dept ON employees(department) INCLUDE (salary, name);

3.2 复合索引策略

-- 复合索引的列顺序至关重要
-- 遵循：等值条件在前，范围条件在后

-- 场景：查询某部门薪资大于 10 万的员工
-- 查询：WHERE department = 'Engineering' AND salary > 100000

-- 好的索引：先等值（department），再范围（salary）
CREATE INDEX idx_emp_dept_salary ON employees(department, salary);

-- 差的索引：范围在前，等值在后无法利用索引
-- CREATE INDEX idx_emp_salary_dept ON employees(salary, department); -- 效果差

-- 验证索引使用情况
EXPLAIN ANALYZE
SELECT * FROM employees
WHERE department = 'Engineering' AND salary > 100000;

3.3 索引维护

-- 查看索引使用统计
SELECT
  schemaname,
  relname,
  indexrelname,
  idx_scan AS scans,
  idx_tup_read AS tuples_read,
  idx_tup_fetch AS tuples_fetched,
  pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
ORDER BY idx_scan ASC;

-- 查找未使用的索引
SELECT
  schemaname,
  relname,
  indexrelname,
  pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND schemaname = 'public'
ORDER BY pg_relation_size(indexrelid) DESC;

-- 重建索引（在线重建，不锁表）
REINDEX INDEX CONCURRENTLY idx_emp_dept_salary;

-- 分析表统计信息
ANALYZE employees;

---

四、EXPLAIN 执行计划分析

4.1 EXPLAIN 基础

-- 查看执行计划
EXPLAIN SELECT * FROM employees WHERE department = 'Engineering';

-- 查看执行计划 + 实际执行
EXPLAIN ANALYZE SELECT * FROM employees WHERE department = 'Engineering';

-- 查看执行计划 + 实际执行 + 缓冲区
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM employees WHERE department = 'Engineering';

-- JSON 格式输出
EXPLAIN (FORMAT JSON)
SELECT * FROM employees WHERE department = 'Engineering';

4.2 常见扫描类型

-- 1. Seq Scan（全表扫描）— 通常需要优化
EXPLAIN SELECT * FROM employees WHERE salary > 50000;
-- Seq Scan on employees  (cost=0.00..35.50 rows=800 width=...)
--   Filter: (salary > 50000)

-- 2. Index Scan（索引扫描）— 索引+回表
EXPLAIN SELECT * FROM employees WHERE id = 100;
-- Index Scan using employees_pkey on employees  (cost=0.15..8.17 rows=1)
--   Index Cond: (id = 100)

-- 3. Index Only Scan（仅索引扫描）— 覆盖索引
EXPLAIN SELECT department, salary FROM employees WHERE department = 'Engineering';
-- Index Only Scan using idx_emp_dept on employees  (cost=0.15..25.36)
--   Index Cond: (department = 'Engineering')

-- 4. Bitmap Index Scan → Bitmap Heap Scan（位图扫描）
EXPLAIN SELECT * FROM employees WHERE department = 'Engineering' AND salary > 100000;
-- Bitmap Heap Scan on employees  (cost=8.30..20.15)
--   Recheck Cond: (department = 'Engineering')
--   Filter: (salary > 100000)
--   -> Bitmap Index Scan on idx_emp_dept  (cost=0.00..8.30)
--        Index Cond: (department = 'Engineering')

4.3 连接类型

-- Nested Loop（嵌套循环）— 适合小表驱动大表
EXPLAIN ANALYZE
SELECT * FROM orders o JOIN customers c ON o.customer_id = c.id
WHERE c.city = 'Tokyo';

-- Hash Join（哈希连接）— 适合大表等值连接
EXPLAIN ANALYZE
SELECT * FROM orders o JOIN products p ON o.product_id = p.id;

-- Merge Join（合并连接）— 需要两端有序
EXPLAIN ANALYZE
SELECT * FROM orders o JOIN customers c ON o.customer_id = c.id
ORDER BY c.id;

4.4 关键指标解读

EXPLAIN ANALYZE 输出示例：

Index Scan using idx_emp_dept on employees  (cost=0.42..25.36 rows=50 width=...) (actual time=0.015..0.318 rows=48 loops=1)
  Index Cond: (department = 'Engineering')

关键指标：
- cost: 估算成本（启动..总成本）
- rows: 估算行数
- actual time: 实际执行时间（启动..总时间）
- rows (actual): 实际返回行数
- loops: 循环次数

优化判断：
1. 估算行数 vs 实际行数差距大 → 需要 ANALYZE
2. Seq Scan 在大表上 → 需要加索引
3. actual time 很高 → 重点优化该节点
4. Filter 过滤了大量行 → 索引策略有问题

---

五、分区表

5.1 范围分区

-- 按日期范围分区（日志表）
CREATE TABLE logs (
  id BIGSERIAL,
  created_at TIMESTAMP NOT NULL,
  level VARCHAR(20),
  message TEXT,
  metadata JSONB
) PARTITION BY RANGE (created_at);

-- 创建月度分区
CREATE TABLE logs_2026_01 PARTITION OF logs
  FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');

CREATE TABLE logs_2026_02 PARTITION OF logs
  FOR VALUES FROM ('2026-02-01') TO ('2026-03-01');

CREATE TABLE logs_2026_03 PARTITION OF logs
  FOR VALUES FROM ('2026-03-01') TO ('2026-04-01');

-- 默认分区（容纳不属于任何分区的数据）
CREATE TABLE logs_default PARTITION OF logs DEFAULT;

-- 在分区上创建索引（自动传播到所有分区）
CREATE INDEX idx_logs_level ON logs(level);
CREATE INDEX idx_logs_created ON logs(created_at);

5.2 列表分区

-- 按地区分区
CREATE TABLE orders (
  id BIGSERIAL,
  region VARCHAR(50) NOT NULL,
  amount NUMERIC(12, 2),
  status VARCHAR(20),
  created_at TIMESTAMP DEFAULT NOW()
) PARTITION BY LIST (region);

CREATE TABLE orders_asia PARTITION OF orders
  FOR VALUES IN ('China', 'Japan', 'Korea', 'Singapore');

CREATE TABLE orders_europe PARTITION OF orders
  FOR VALUES IN ('Germany', 'France', 'UK', 'Spain');

CREATE TABLE orders_americas PARTITION OF orders
  FOR VALUES IN ('USA', 'Canada', 'Brazil', 'Mexico');

5.3 哈希分区

-- 按哈希均匀分布
CREATE TABLE user_events (
  id BIGSERIAL,
  user_id BIGINT NOT NULL,
  event_type VARCHAR(50),
  event_data JSONB,
  created_at TIMESTAMP DEFAULT NOW()
) PARTITION BY HASH (user_id);

-- 创建 4 个分区
CREATE TABLE user_events_0 PARTITION OF orders
  FOR VALUES WITH (modulus 4, remainder 0);
CREATE TABLE user_events_1 PARTITION OF orders
  FOR VALUES WITH (modulus 4, remainder 1);
CREATE TABLE user_events_2 PARTITION OF orders
  FOR VALUES WITH (modulus 4, remainder 2);
CREATE TABLE user_events_3 PARTITION OF orders
  FOR VALUES WITH (modulus 4, remainder 3);

5.4 分区管理自动化

-- 使用 pg_partman 扩展自动管理分区
CREATE EXTENSION IF NOT EXISTS pg_partman;

-- 创建自动分区策略
SELECT partman.create_parent(
  p_parent_table => 'public.logs',
  p_control => 'created_at',
  p_type => 'range',
  p_interval => 'monthly',
  p_premake => 3  -- 预创建未来 3 个月的分区
);

-- 定期运行维护函数（通过 cron）
-- SELECT partman.run_maintenance_proc();

---

六、查询优化实战

6.1 避免 SELECT *

-- 差：查询所有列
SELECT * FROM employees WHERE department = 'Engineering';

-- 好：只查询需要的列
SELECT id, name, salary FROM employees WHERE department = 'Engineering';

6.2 批量操作优化

-- 差：逐行插入
INSERT INTO orders (customer_id, amount) VALUES (1, 100);
INSERT INTO orders (customer_id, amount) VALUES (2, 200);
INSERT INTO orders (customer_id, amount) VALUES (3, 300);

-- 好：批量插入
INSERT INTO orders (customer_id, amount) VALUES
  (1, 100), (2, 200), (3, 300);

-- 使用 COPY 导入大量数据
COPY orders FROM '/path/to/orders.csv' WITH (FORMAT csv, HEADER true);

-- 使用 UPSERT 处理冲突
INSERT INTO products (id, name, price)
VALUES (1, 'Widget', 29.99)
ON CONFLICT (id) DO UPDATE SET
  name = EXCLUDED.name,
  price = EXCLUDED.price,
  updated_at = NOW();

6.3 子查询优化

-- 差：相关子查询（每行执行一次子查询）
SELECT
  name,
  (SELECT AVG(salary) FROM employees e2 WHERE e2.department = e1.department) AS dept_avg
FROM employees e1;

-- 好：JOIN + 聚合
SELECT
  e.name,
  d.avg_salary AS dept_avg
FROM employees e
JOIN (
  SELECT department, AVG(salary) AS avg_salary
  FROM employees
  GROUP BY department
) d ON e.department = d.department;

6.4 EXISTS vs IN

-- 大子表用 EXISTS 更高效
SELECT * FROM orders o
WHERE EXISTS (
  SELECT 1 FROM order_items oi
  WHERE oi.order_id = o.id
  AND oi.product_id = 42
);

-- 小子表用 IN 更简洁
SELECT * FROM orders o
WHERE o.customer_id IN (
  SELECT id FROM customers WHERE city = 'Tokyo'
);

6.5 分页优化

-- 差：OFFSET 在大偏移量时极慢
SELECT * FROM orders ORDER BY created_at DESC LIMIT 20 OFFSET 100000;

-- 好：使用游标分页（keyset pagination）
SELECT * FROM orders
WHERE created_at < '2026-07-01 00:00:00'
ORDER BY created_at DESC
LIMIT 20;

---

七、服务器配置优化

7.1 核心参数

# postgresql.conf

# 内存相关
shared_buffers = 4GB                # 总内存的 25%
effective_cache_size = 12GB         # 总内存的 75%
work_mem = 64MB                     # 每个排序/哈希操作的内存
maintenance_work_mem = 512MB        # VACUUM/CREATE INDEX 内存

# WAL 相关
wal_buffers = 16MB
max_wal_size = 2GB
checkpoint_completion_target = 0.9
wal_compression = on

# 并行查询
max_worker_processes = 8
max_parallel_workers_per_gather = 4
max_parallel_workers = 8
parallel_setup_cost = 100
parallel_tuple_cost = 0.1

# 自动清理
autovacuum = on
autovacuum_max_workers = 3
autovacuum_naptime = 30s
autovacuum_vacuum_threshold = 50
autovacuum_analyze_threshold = 50

# 连接池
max_connections = 100

7.2 连接池配置（PgBouncer）

# pgbouncer.ini
[databases]
mydb = host=127.0.0.1 port=5432 dbname=mydb

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt

pool_mode = transaction
max_client_conn = 1000
default_pool_size = 25
reserve_pool_size = 5
reserve_pool_timeout = 3
server_idle_timeout = 300

---

八、监控与诊断

8.1 慢查询日志

# postgresql.conf
log_min_duration_statement = 100    # 记录超过 100ms 的查询
log_line_prefix = '%t [%p] %u@%d '
log_checkpoints = on
log_connections = on
log_lock_waits = on
log_temp_files = 0
log_autovacuum_min_duration = 0

8.2 pg_stat_statements

-- 启用扩展
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- 查看最慢的查询
SELECT
  query,
  calls,
  total_exec_time,
  mean_exec_time,
  rows,
  100.0 * shared_blks_hit /
    NULLIF(shared_blks_hit + shared_blks_read, 0) AS hit_percent
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;

-- 查看最频繁的查询
SELECT
  LEFT(query, 80) AS query,
  calls,
  total_exec_time,
  mean_exec_time
FROM pg_stat_statements
ORDER BY calls DESC
LIMIT 10;

8.3 锁等待分析

-- 查看锁等待
SELECT
  blocked.pid AS blocked_pid,
  blocked.query AS blocked_query,
  blocking.pid AS blocking_pid,
  blocking.query AS blocking_query,
  blocked.mode AS blocked_mode,
  blocking.mode AS blocking_mode
FROM pg_stat_activity blocked
JOIN pg_stat_activity blocking
  ON blocking.pid = ANY(pg_blocking_pids(blocked.pid));

-- 终止阻塞进程
-- SELECT pg_terminate_backend(<blocking_pid>);

---

九、总结

• ✅ 窗口函数（排名、聚合、偏移、分桶）
• ✅ CTE 与递归查询（组织树、评论树）
• ✅ 索引优化（7 种索引类型、复合索引策略、索引维护）
• ✅ EXPLAIN 执行计划分析（扫描类型、连接类型、关键指标）
• ✅ 分区表（范围、列表、哈希分区、自动管理）
• ✅ 查询优化实战（批量操作、子查询、分页）
• ✅ 服务器配置优化（内存、WAL、并行查询、连接池）
• ✅ 监控与诊断（慢查询、pg_stat_statements、锁等待）

PostgreSQL 性能优化是一个系统工程，从查询语句到索引策略再到服务器配置，每一层都需要精细调优。

---

相关阅读：

• Redis 缓存策略与分布式锁实战
• Node.js 性能调优与内存泄漏排查
• Kubernetes 入门与生产部署实战
• TypeScript 项目架构与工程化实践
• Web 安全防护与 XSS/CSRF 防御实战