PostgreSQL优化

PostgreSQL优化不是一件“把参数调大、把索引加满”的事情。PG的性能来自几个部分共同作用：

SQL写法是否让优化器容易生成好计划。
索引是否匹配真实查询模式。
统计信息是否准确。
MVCC产生的旧版本是否被及时清理。
连接数、内存、WAL、checkpoint、IO是否在合理范围内。
应用架构是否把数据库当成可无限扩展的黑盒。

这篇笔记面向后端开发，重点讲索引、执行计划、SQL优化、参数调优、日常维护和多租户/连接池等工程经验。

版本说明：本文以PostgreSQL 18官方文档为主要参考。大部分索引和优化方法在PG 12+都适用，但具体参数默认值、执行计划细节和扩展能力需要按实际版本确认。

1.优化前先建立正确直觉

1.1 慢查询通常不是单点问题

同一条SQL变慢，可能有很多原因：

SQL变慢
  ├── SQL本身写法不合适
  ├── 缺少匹配查询模式的索引
  ├── 索引太多，写入和维护变慢
  ├── 统计信息过旧，优化器估算错误
  ├── 表/索引膨胀，扫描页面变多
  ├── work_mem不足，排序/哈希落盘
  ├── 连接数过高，CPU被上下文切换消耗
  ├── 锁等待或长事务阻塞
  ├── checkpoint/VACUUM/备份造成IO抖动
  └── 业务流量或数据分布发生变化

所以PG优化要从证据开始，而不是先猜参数。

推荐顺序：

1. 找慢SQL
2. 看EXPLAIN (ANALYZE, BUFFERS)
3. 看索引是否匹配
4. 看统计信息和行数估算
5. 看锁等待、连接数、IO、WAL、VACUUM
6. 再考虑参数和架构调整

1.2 一条慢SQL的排查入口

当前正在执行的SQL：

SELECT
    pid,
    usename,
    application_name,
    client_addr,
    state,
    wait_event_type,
    wait_event,
    now() - query_start AS running_time,
    query
FROM pg_stat_activity
WHERE state <> 'idle'
ORDER BY running_time DESC;

长期慢SQL建议开启pg_stat_statements扩展：

1	`CREATE EXTENSION IF NOT EXISTS pg_stat_statements;`

查看总耗时最高的SQL：

SELECT
    calls,
    round(total_exec_time::numeric, 2) AS total_ms,
    round(mean_exec_time::numeric, 2) AS mean_ms,
    rows,
    query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

查看平均耗时最高的SQL：

SELECT
    calls,
    round(mean_exec_time::numeric, 2) AS mean_ms,
    round(max_exec_time::numeric, 2) AS max_ms,
    rows,
    query
FROM pg_stat_statements
WHERE calls > 10
ORDER BY mean_exec_time DESC
LIMIT 20;

注意：

total_exec_time高，说明它消耗了系统总资源。
mean_exec_time高，说明单次请求慢。
calls很高但平均耗时低，也可能是热点SQL，值得优化。

2.索引的本质

一句话：

索引是用额外存储、额外写入成本和额外维护成本，换取读取时更快定位数据。

PG的表数据和索引数据是分开的。常见B-Tree索引里保存的是“索引键 + 指向堆表行版本的TID”，找到索引项以后，通常还要回到堆表检查MVCC可见性并读取完整行。

WHERE user_id = 10001
        |
        v
  B-Tree Index
  user_id -> heap tuple id
        |
        v
  Heap Table
  真正的行数据 + MVCC版本信息

这带来几个重要结论：

索引不是越多越好。每多一个索引，INSERT、UPDATE、DELETE都要维护它。
索引不是建了就一定用。优化器会比较顺序扫描、索引扫描、位图扫描等成本。
小表顺序扫描很正常。几十行、几百行的小表走索引反而可能更慢。
选择性低的条件未必适合单列索引。例如status只有0/1，单独索引可能没用。
索引能优化过滤、排序、唯一性、JOIN，但不能弥补所有糟糕的数据模型和SQL写法。

3.PostgreSQL核心索引类型

PG内置常见索引访问方法包括：

B-Tree
Hash
GiST
SP-GiST
GIN
BRIN

默认CREATE INDEX创建的是B-Tree索引。

3.1 核心索引类型对比

索引类型	适用场景	底层特点与优势	创建语法示例
B-Tree	默认索引。适合等值、范围、排序、唯一约束、多列组合查询。	有序平衡树结构，支持`=`、`<`、`<=`、`>`、`>=`、`BETWEEN`、部分前缀`LIKE`和排序。绝大多数OLTP查询首选。	`CREATE INDEX idx_users_email ON users (email);`
Hash	只适合等值比较。	存储hash值，只支持`=`。PG 10后有WAL保护，但多数业务场景B-Tree足够好，Hash通常不是首选。	`CREATE INDEX idx_users_id_hash ON users USING hash (id);`
GIN	数组、`jsonb`、全文搜索、`pg_trgm`模糊搜索。	倒排索引。一个字段包含多个可搜索元素时，能从“元素”快速定位包含它的行。	`CREATE INDEX idx_events_payload_gin ON events USING gin (payload);`
GiST	PostGIS空间数据、范围类型、最近邻查询、排他约束。	通用搜索树框架，支持相交、包含、距离、重叠等复杂操作符，具体能力取决于operator class。	`CREATE INDEX idx_places_geom_gist ON places USING gist (geom);`
SP-GiST	非平衡数据结构、空间分区、前缀树类场景。	支持quadtrees、k-d trees、radix trees等非平衡分区结构，适合数据分布不均匀的特定查询。	`CREATE INDEX idx_points_spgist ON points USING spgist (location);`
BRIN	超大表、追加写、物理顺序和查询列高度相关的时间序列/日志/流水。	按块范围保存摘要，如最小值/最大值。索引极小、创建快，但只是粗过滤。	`CREATE INDEX idx_logs_created_brin ON access_logs USING brin (created_at);`

决策直觉：

普通业务表主键/外键/状态时间查询 -> B-Tree
jsonb/数组/全文检索             -> GIN
空间/范围/排他约束              -> GiST
超大按时间追加表                -> BRIN
特殊空间分区/前缀结构           -> SP-GiST
纯等值但B-Tree不够好            -> 再评估Hash

3.2 B-Tree索引

B-Tree是最常用索引，也是后端业务系统的默认选择。

适合：

主键：WHERE id = ?
唯一键：UNIQUE (tenant_id, order_no)
外键JOIN：orders.user_id = users.id
范围查询：created_at >= ? AND created_at < ?
排序分页：ORDER BY created_at DESC LIMIT 20
等值 + 范围 + 排序组合查询

基本语法：

CREATE INDEX idx_orders_user_id
ON orders (user_id);

CREATE UNIQUE INDEX idx_users_email
ON users (email);

CREATE INDEX idx_orders_user_created_desc
ON orders (user_id, created_at DESC);

一个典型查询：

SELECT id, status, amount, created_at
FROM orders
WHERE user_id = $1
ORDER BY created_at DESC
LIMIT 20;

匹配索引：

1 2	`CREATE INDEX CONCURRENTLY idx_orders_user_created_desc ON orders (user_id, created_at DESC);`

这个索引同时服务：

user_id = $1过滤。
created_at DESC排序。
LIMIT 20快速停止扫描。

3.3 复合索引

复合索引，也叫多列索引：

1 2	`CREATE INDEX idx_orders_tenant_status_created ON orders (tenant_id, status, created_at DESC);`

它不是三个单列索引的简单相加，而是一棵按多列组合排序的树：

(tenant_id, status, created_at)

tenant_id=1
  status=pending
    created_at=2026-06-01
    created_at=2026-06-02
  status=paid
    created_at=2026-06-01

tenant_id=2
  ...

适合：

WHERE tenant_id = $1

WHERE tenant_id = $1
  AND status = $2

WHERE tenant_id = $1
  AND status = $2
ORDER BY created_at DESC

不太适合：

1	`WHERE status = $1`

因为没有从最左侧的tenant_id开始约束。

3.3.1 “最左前缀”在PG里怎么理解

MySQL里经常讲“最左前缀原则”。PG的B-Tree多列索引也有类似直觉，但不要机械理解成“区分度最高的列永远放最前面”。

更实用的排序规则：

优先级	设计因素	说明
1	高频查询条件	索引必须服务真实SQL，而不是服务字段列表。
2	等值过滤列	`tenant_id = ?`、`user_id = ?`通常放在范围列前面。
3	范围过滤列	`created_at >= ?`放在等值列之后。
4	排序列	如果要避免额外排序，索引顺序要匹配`ORDER BY`。
5	覆盖列	只用于返回的列可以考虑`INCLUDE`，不要都塞进key列。

例如：

SELECT id, amount
FROM orders
WHERE tenant_id = $1
  AND status = $2
  AND created_at >= $3
ORDER BY created_at DESC
LIMIT 50;

常见索引：

1 2	`CREATE INDEX CONCURRENTLY idx_orders_tenant_status_created_desc ON orders (tenant_id, status, created_at DESC);`

如果另一个接口经常不带status：

SELECT id, amount
FROM orders
WHERE tenant_id = $1
  AND created_at >= $2
ORDER BY created_at DESC
LIMIT 50;

那么(tenant_id, created_at DESC)可能更合适。不要为了“看起来覆盖所有字段”把索引越建越长。

3.4 Hash索引

Hash索引只支持等值比较：

1 2	`CREATE INDEX idx_users_email_hash ON users USING hash (email);`

使用场景很窄：

1
2
3

SELECT *
FROM users
WHERE email = $1;

工程建议：

大多数等值查询优先用B-Tree。
Hash不支持范围查询和排序。
如果B-Tree能满足查询和唯一约束，就不要为了“hash听起来更快”换成Hash。
只有在明确测试Hash更适合某个超高频等值查询时，再考虑使用。

3.5 GIN索引

GIN可以理解为倒排索引，适合一个字段包含多个可搜索元素的情况。

row1 tags = ["postgresql", "database", "index"]
row2 tags = ["mysql", "database"]

GIN倒排结构：
"postgresql" -> row1
"database"   -> row1, row2
"index"      -> row1
"mysql"      -> row2

数组查询：

CREATE INDEX idx_articles_tags_gin
ON articles USING gin (tags);

SELECT *
FROM articles
WHERE tags @> ARRAY['postgresql'];

JSONB包含查询：

CREATE INDEX idx_events_payload_gin
ON events USING gin (payload);

SELECT *
FROM events
WHERE payload @> '{"event_type": "payment_succeeded"}'::jsonb;

全文搜索：

CREATE INDEX idx_articles_search_gin
ON articles USING gin (
    to_tsvector('simple', coalesce(title, '') || ' ' || coalesce(content, ''))
);

SELECT *
FROM articles
WHERE to_tsvector('simple', coalesce(title, '') || ' ' || coalesce(content, ''))
      @@ plainto_tsquery('simple', 'postgresql index');

三元组模糊搜索：

CREATE EXTENSION IF NOT EXISTS pg_trgm;

CREATE INDEX idx_users_name_trgm
ON users USING gin (name gin_trgm_ops);

SELECT *
FROM users
WHERE name ILIKE '%alice%';

GIN注意事项：

GIN写入成本通常比B-Tree高。
jsonb整列GIN索引可能很大，不要无脑给所有JSONB列加GIN。
固定JSON key的等值查询，表达式B-Tree经常更小、更快。
大批量导入时，通常先导入数据，再建GIN索引。
返回结果很多时，GIN只能帮你找到候选行，后续访问堆表仍然可能很重。

固定JSON key查询的替代方案：

CREATE INDEX idx_events_payload_user_id
ON events ((payload ->> 'user_id'));

SELECT *
FROM events
WHERE payload ->> 'user_id' = '10001';

3.6 GiST索引

GiST不是单一索引算法，更像一个通用搜索树框架。它常见于：

PostGIS几何空间查询。
范围类型查询。
最近邻查询。
排他约束。

范围类型示例：

CREATE TABLE room_bookings (
    id bigserial PRIMARY KEY,
    room_id bigint NOT NULL,
    period tstzrange NOT NULL
);

CREATE INDEX idx_room_bookings_period_gist
ON room_bookings USING gist (period);

SELECT *
FROM room_bookings
WHERE period && tstzrange(
    '2026-06-21 10:00:00+08',
    '2026-06-21 12:00:00+08'
);

排他约束，防止同一房间时间段重叠：

CREATE EXTENSION IF NOT EXISTS btree_gist;

ALTER TABLE room_bookings
ADD CONSTRAINT room_bookings_no_overlap
EXCLUDE USING gist (
    room_id WITH =,
    period WITH &&
);

PostGIS常见形式：

CREATE INDEX idx_places_geom_gist
ON places USING gist (geom);

SELECT *
FROM places
WHERE ST_Intersects(geom, ST_GeomFromText('POLYGON((...))', 4326));

3.7 SP-GiST索引

SP-GiST适合非平衡分区结构，例如quadtrees、k-d trees、radix trees。它不是日常业务表的第一选择，但在特定数据形态下很有价值。

常见场景：

二维点数据。
IP、前缀、路由类数据。
数据分布极不均匀的空间检索。
某些前缀匹配和范围搜索。

示例：

CREATE TABLE locations (
    id bigserial PRIMARY KEY,
    p point NOT NULL
);

CREATE INDEX idx_locations_p_spgist
ON locations USING spgist (p);

SELECT *
FROM locations
WHERE p <@ box(point(0, 0), point(10, 10));

工程建议：

普通等值、范围、排序不要优先考虑SP-GiST。
空间类业务先比较GiST和SP-GiST的真实执行计划。
数据分布不均匀时，SP-GiST可能比GiST更合适。

3.8 BRIN索引

BRIN是Block Range Index。它按一段连续数据块保存摘要信息，例如最小值和最大值，而不是为每一行保存精确索引项。

适合：

表非常大。
数据按时间或递增ID持续追加。
查询经常按时间范围过滤。
查询列和物理存储顺序高度相关。

CREATE INDEX idx_access_logs_created_brin
ON access_logs USING brin (created_at);

SELECT *
FROM access_logs
WHERE created_at >= now() - interval '1 day';

原理直觉：

access_logs按created_at大致递增写入

block range 1: 2026-06-01 00:00 ~ 2026-06-01 03:00
block range 2: 2026-06-01 03:00 ~ 2026-06-01 06:00
block range 3: 2026-06-01 06:00 ~ 2026-06-01 09:00

查询 06:00 ~ 09:00：
  BRIN跳过range 1和2，只扫描range 3附近的数据块

BRIN优势：

索引极小。
创建速度快。
写入维护成本低。
对TB级追加表很友好。

BRIN限制：

它是粗过滤，不能像B-Tree那样精准定位少量行。
数据物理顺序越乱，效果越差。
如果要点查单条记录，B-Tree通常更合适。
如果历史数据被频繁乱序写入或更新，BRIN收益会下降。

常见组合：

时间分区表
  -> 分区裁剪先排除大部分分区
  -> 每个大分区内使用BRIN按时间粗过滤
  -> 小范围精确查询再考虑B-Tree

4.高阶索引技巧

4.1 部分索引

部分索引只为满足某个条件的行建索引。

1
2
3

CREATE INDEX idx_users_active_email
ON users (email)
WHERE deleted_at IS NULL;

适合：

软删除，只查未删除数据。
状态机，只查待处理、处理中、失败重试数据。
大表里只有少量“活跃数据”被高频访问。
某个租户、某类业务数据是明确热点。

示例：

CREATE INDEX idx_orders_pending_created
ON orders (created_at DESC)
WHERE status IN ('pending', 'paying');

SELECT id, amount, created_at
FROM orders
WHERE status IN ('pending', 'paying')
ORDER BY created_at DESC
LIMIT 100;

如果pending/paying只占全表1%，这个索引会比全量索引小很多，缓存命中率也更好。

注意：

查询条件必须能让优化器判断“满足部分索引谓词”，否则不会使用该索引。
谓词要稳定、简单，避免复杂表达式。
不要写动态时间谓词，例如WHERE created_at > now() - interval '7 days'。这类滑动窗口更适合分区。
部分索引不是分区表替代品，它只优化某些查询。

4.2 覆盖索引和Index Only Scan

PG支持INCLUDE列：

1
2
3

CREATE INDEX idx_orders_user_created_include
ON orders (user_id, created_at DESC)
INCLUDE (status, amount);

索引键列：

user_id
created_at

非键覆盖列：

status
amount

查询：

SELECT status, amount, created_at
FROM orders
WHERE user_id = $1
ORDER BY created_at DESC
LIMIT 20;

如果查询需要的列都在索引里，PG可能使用Index Only Scan，尽量不回表。

但要注意：Index Only Scan不是“建了覆盖索引就必然发生”。它还依赖visibility map。因为PG使用MVCC，索引项本身不保存行版本是否对当前事务可见。只有当某些堆表页面被标记为all-visible时，PG才能放心不回表。

Index Only Scan能否真正省IO
  ├── 索引类型是否支持
  ├── 查询列是否都在索引里
  └── visibility map是否显示页面对所有事务可见

执行计划里要看：

1 2	`Index Only Scan using idx_orders_user_created_include on orders Heap Fetches: 0`

如果Heap Fetches很高，说明仍然大量回表。

注意：

INCLUDE列会增加索引体积。
宽字段不要轻易放进INCLUDE。
频繁更新的字段放入索引，会增加写入成本。
带INCLUDE的B-Tree索引不能使用B-Tree deduplication，重复值很多时要评估索引膨胀。

4.3 表达式索引

表达式索引基于计算结果建索引。

大小写不敏感邮箱：

CREATE UNIQUE INDEX idx_users_lower_email
ON users (lower(email));

SELECT *
FROM users
WHERE lower(email) = lower('Alice@Example.com');

JSONB固定key：

CREATE INDEX idx_orders_ext_channel
ON orders ((ext ->> 'channel'));

SELECT *
FROM orders
WHERE ext ->> 'channel' = 'app';

日期范围不建议这样写：

-- 不推荐：对索引列做函数计算
SELECT *
FROM orders
WHERE date(created_at) = '2026-06-21';

更推荐：

SELECT *
FROM orders
WHERE created_at >= '2026-06-21 00:00:00+08'
  AND created_at <  '2026-06-22 00:00:00+08';

表达式索引注意：

查询表达式要和索引表达式匹配。
表达式里的函数必须是immutable，不能依赖当前时间、表数据等外部状态。
表达式索引会增加写入计算成本。
不要用表达式索引掩盖可以在写入时标准化的数据。例如邮箱可以存储时统一转小写。

4.4 唯一索引和约束

唯一约束通常会创建唯一索引：

1 2	`ALTER TABLE users ADD CONSTRAINT users_email_uk UNIQUE (email);`

也可以直接创建唯一索引：

1 2	`CREATE UNIQUE INDEX users_email_uk ON users (email);`

业务上更推荐用约束表达“不允许重复”的规则，因为约束是数据模型语义；唯一索引更偏执行结构。

部分唯一索引很实用：

1
2
3

CREATE UNIQUE INDEX users_active_email_uk
ON users (email)
WHERE deleted_at IS NULL;

含义：同一个邮箱只能有一个未删除用户，但历史软删除记录可以保留。

PG唯一索引默认认为NULL彼此不同。也就是说，唯一列里可以有多个NULL。如果希望NULL也视为相同，可以使用：

1 2	`CREATE UNIQUE INDEX users_phone_uk ON users (phone) NULLS NOT DISTINCT;`

4.5 外键列索引

PG创建外键时，不会自动给引用列创建索引。

例如：

CREATE TABLE orders (
    id bigserial PRIMARY KEY,
    user_id bigint NOT NULL REFERENCES users(id)
);

users.id作为被引用列通常已经是主键，有索引。但orders.user_id不会因为外键自动有索引。

建议：

1 2	`CREATE INDEX idx_orders_user_id ON orders (user_id);`

为什么重要：

子表按父ID查询时需要。
JOIN时需要。
删除或更新父表主键时，PG需要检查子表是否存在引用行；如果子表外键列没有索引，可能扫描整张子表。

4.6 索引创建、删除和重建

创建索引：

1 2	`CREATE INDEX idx_orders_created_at ON orders (created_at);`

线上大表建议：

1 2	`CREATE INDEX CONCURRENTLY idx_orders_created_at ON orders (created_at);`

CONCURRENTLY特点：

不阻塞普通INSERT、UPDATE、DELETE。
构建更慢。
不能放在显式事务块里。
失败后可能留下invalid索引，需要检查和清理。

查看invalid索引：

SELECT
    ns.nspname AS schema_name,
    cls.relname AS index_name,
    tbl.relname AS table_name,
    idx.indisvalid,
    idx.indisready
FROM pg_index idx
JOIN pg_class cls ON cls.oid = idx.indexrelid
JOIN pg_class tbl ON tbl.oid = idx.indrelid
JOIN pg_namespace ns ON ns.oid = cls.relnamespace
WHERE NOT idx.indisvalid OR NOT idx.indisready;

删除索引：

1 2	`DROP INDEX idx_orders_created_at; DROP INDEX CONCURRENTLY idx_orders_created_at;`

重建索引：

1 2	`REINDEX INDEX idx_orders_created_at; REINDEX INDEX CONCURRENTLY idx_orders_created_at;`

查看索引大小：

SELECT
    schemaname,
    relname AS table_name,
    indexrelname AS index_name,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
ORDER BY pg_relation_size(indexrelid) DESC
LIMIT 20;

查看低使用索引：

SELECT
    schemaname,
    relname AS table_name,
    indexrelname AS index_name,
    idx_scan,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
ORDER BY idx_scan ASC, pg_relation_size(indexrelid) DESC
LIMIT 50;

注意：idx_scan = 0不代表立刻删除。可能是新索引、低频灾备查询、约束索引、统计刚重置，删除前要结合业务确认。

5.索引设计工程经验

5.1 从真实SQL出发

不要从表字段出发设计索引，要从接口SQL出发。

例如订单列表：

SELECT id, status, amount, created_at
FROM orders
WHERE tenant_id = $1
  AND user_id = $2
  AND status = $3
ORDER BY created_at DESC
LIMIT 20;

可能的索引：

1
2
3

CREATE INDEX idx_orders_tenant_user_status_created
ON orders (tenant_id, user_id, status, created_at DESC)
INCLUDE (amount);

但如果真实场景里status经常为空，查询变成：

1
2
3

WHERE tenant_id = $1
  AND user_id = $2
ORDER BY created_at DESC

那么上面的索引仍然可用，但不一定最优。也许需要：

1
2
3

CREATE INDEX idx_orders_tenant_user_created
ON orders (tenant_id, user_id, created_at DESC)
INCLUDE (status, amount);

索引设计要回答三个问题：

1
2
3

1. 这个索引服务哪些SQL？
2. 这些SQL的调用频率和延迟要求是什么？
3. 这个索引增加了多少写入、存储和维护成本？

5.2 常见索引组合模板

用户详情：

1 2	`CREATE UNIQUE INDEX users_id_pk ON users (id);`

租户内业务唯一号：

1 2	`CREATE UNIQUE INDEX orders_tenant_order_no_uk ON orders (tenant_id, order_no);`

列表分页：

1 2	`CREATE INDEX orders_tenant_created_id_desc ON orders (tenant_id, created_at DESC, id DESC);`

状态队列：

1
2
3

CREATE INDEX jobs_pending_next_run
ON jobs (next_run_at, id)
WHERE status = 'pending';

软删除唯一：

1
2
3

CREATE UNIQUE INDEX users_active_email_uk
ON users (tenant_id, email)
WHERE deleted_at IS NULL;

大小写不敏感：

1 2	`CREATE UNIQUE INDEX users_lower_email_uk ON users (tenant_id, lower(email));`

JSONB固定字段：

1 2	`CREATE INDEX events_payload_event_type ON events ((payload ->> 'event_type'));`

JSONB任意包含：

1 2	`CREATE INDEX events_payload_gin ON events USING gin (payload);`

时间序列大表：

1 2	`CREATE INDEX metrics_created_brin ON metrics USING brin (created_at);`

5.3 索引不生效的常见原因

原因	例子	处理方式
表太小	几百行小表	顺序扫描正常，不必强行优化。
条件选择性太低	`status = 1`命中80%数据	单列索引意义小，考虑复合索引或部分索引。
对字段做函数计算	`date(created_at) = ...`	改成范围查询，或建表达式索引。
隐式类型转换	`phone = 13800000000`，字段是`varchar`	参数类型和字段类型保持一致。
前置通配符	`LIKE '%abc'`	使用`pg_trgm` + GIN/GiST，或搜索引擎。
统计信息过旧	大批量导入后未`ANALYZE`	执行`ANALYZE table_name;`。
返回行太多	命中全表30%以上	顺序扫描可能更便宜。
排序不匹配	索引顺序和`ORDER BY`不同	调整索引列顺序和方向。
部分索引谓词不匹配	索引`WHERE deleted_at IS NULL`，查询没带该条件	查询条件保持一致。
参数化SQL泛化计划	不同参数选择性差异很大	评估`plan_cache_mode`或拆分SQL。

隐式类型转换示例：

-- phone是varchar，不推荐传数字
SELECT *
FROM users
WHERE phone = 13800000000;

-- 推荐
SELECT *
FROM users
WHERE phone = '13800000000';

5.4 索引太多的代价

索引的代价不只是不多一点磁盘。

每多一个索引
  ├── INSERT需要写更多索引页
  ├── UPDATE可能要更新更多索引项
  ├── DELETE后索引里也会产生待清理条目
  ├── VACUUM要清理更多结构
  ├── 备份体积变大
  ├── 缓存被更多索引页占用
  └── 建表迁移和恢复时间变长

写多读少的表尤其要克制：

任务流水表。
日志表。
埋点事件表。
频繁更新状态的表。

一个实用原则：

每个索引都要能说清楚服务哪个查询或哪个约束。说不清的索引，迟早会变成维护负担。

6.执行计划：EXPLAIN

6.1 基本用法

只看优化器计划，不执行SQL：

EXPLAIN
SELECT *
FROM orders
WHERE user_id = 10001;

实际执行并返回真实耗时：

EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM orders
WHERE user_id = 10001;

更详细：

EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT *
FROM orders
WHERE user_id = 10001;

机器可读格式：

EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
SELECT *
FROM orders
WHERE user_id = 10001;

注意：

EXPLAIN不真正执行。
EXPLAIN ANALYZE会真正执行。
对UPDATE、DELETE、INSERT使用EXPLAIN ANALYZE时，要包事务再回滚。

BEGIN;
EXPLAIN (ANALYZE, BUFFERS)
DELETE FROM orders WHERE id = 1;
ROLLBACK;

6.2 执行计划核心字段

典型输出：

Index Scan using idx_orders_user_id on orders
  (cost=0.43..8.45 rows=1 width=128)
  (actual time=0.031..0.033 rows=1 loops=1)
  Index Cond: (user_id = 10001)
  Buffers: shared hit=4
Planning Time: 0.210 ms
Execution Time: 0.058 ms

字段解释：

字段	含义	怎么看
`cost`	优化器估算成本，不是毫秒	用来比较计划，不要当成真实耗时。
`rows`	优化器估算返回行数	和`actual rows`差很多说明统计信息或条件相关性有问题。
`width`	估算每行宽度	太宽说明`SELECT *`或大字段可能增加IO。
`actual time`	真实执行时间	单节点耗时要结合`loops`看。
`actual rows`	真实返回行数	和估算差异是重点。
`loops`	节点重复执行次数	Nested Loop里尤其重要。
`Buffers`	缓冲区命中和读取	判断内存命中还是磁盘IO。

重点看估算和真实的差距：

1	`rows=100 actual rows=1000000`

这类偏差会导致：

错误的JOIN顺序。
错误的扫描方式。
错误的聚合方式。
错误的内存估算。

6.3 常见执行节点

节点	含义	关注点
`Seq Scan`	顺序扫描表	大表上要警惕；小表正常。
`Index Scan`	通过索引定位，再回表	看返回行数、随机IO、过滤掉多少行。
`Index Only Scan`	尽量只读索引	看`Heap Fetches`是否接近0。
`Bitmap Index Scan`	先扫描索引生成位图	适合返回较多行，或组合多个索引。
`Bitmap Heap Scan`	按位图访问堆表	看`Recheck Cond`和`Heap Blocks`。
`Nested Loop`	外层每行驱动内层查询	小结果集很好，大结果集可能很慢。
`Hash Join`	构建哈希表JOIN	看是否batch过多、是否落盘。
`Merge Join`	两边有序后合并	适合两边已有排序或索引顺序。
`Sort`	排序	看`Sort Method`是否`external merge Disk`。
`Aggregate`	聚合	看分组数量、内存、是否并行。
`Gather`	并行查询汇总	看worker是否真正启动。

6.4 Buffers怎么看

示例：

1	`Buffers: shared hit=100 read=200 dirtied=5 written=2`

含义：

shared hit：在PG共享缓冲区命中。
shared read：从磁盘或OS cache读取到PG缓冲区。
dirtied：弄脏了页面。
written：写出了页面。

直觉：

1
2
3

hit高、read低：多半是CPU或SQL逻辑问题
read高：可能是IO、索引不匹配、缓存不够、扫描数据太多
dirtied/written高：写入、排序临时数据、维护任务要关注

6.5 排序和哈希是否落盘

排序落盘通常能在执行计划看到：

1	`Sort Method: external merge Disk: 204800kB`

说明work_mem不够当前排序使用，PG把中间结果写到了临时文件。

Hash Join或Hash Aggregate也可能因为内存不足分批：

1 2	`Batches: 8 Memory Usage: 4096kB`

优化方向：

减少参与排序/哈希的数据量。
增加合适索引，避免排序。
只返回需要的列，降低行宽。
对单个会话临时提高work_mem。
不要全局盲目调大work_mem。

临时提高：

BEGIN;
SET LOCAL work_mem = '256MB';

-- 执行报表SQL

COMMIT;

7.SQL优化模式

7.1 避免深分页

低效写法：

SELECT id, created_at
FROM orders
ORDER BY created_at DESC
LIMIT 20 OFFSET 100000;

OFFSET越大，PG要跳过的行越多。它不是“直接跳到第100001行”。

更推荐游标式分页：

SELECT id, created_at
FROM orders
WHERE (created_at, id) < ($last_created_at, $last_id)
ORDER BY created_at DESC, id DESC
LIMIT 20;

配套索引：

1 2	`CREATE INDEX idx_orders_created_id_desc ON orders (created_at DESC, id DESC);`

如果是多租户：

1 2	`CREATE INDEX idx_orders_tenant_created_id_desc ON orders (tenant_id, created_at DESC, id DESC);`

7.2 少用SELECT *

-- 高频接口不推荐
SELECT *
FROM orders
WHERE id = $1;

-- 推荐
SELECT id, status, amount, created_at
FROM orders
WHERE id = $1;

明确列的好处：

减少堆表IO。
减少网络传输。
避免新增大字段后拖慢旧接口。
更容易命中覆盖索引。
让代码更清楚依赖哪些字段。

7.3 避免在索引列上做计算

低效：

1
2
3

SELECT *
FROM orders
WHERE date(created_at) = '2026-06-21';

高效：

SELECT *
FROM orders
WHERE created_at >= '2026-06-21 00:00:00+08'
  AND created_at <  '2026-06-22 00:00:00+08';

低效：

1
2
3

SELECT *
FROM users
WHERE lower(email) = 'alice@example.com';

可选优化：

1 2	`CREATE INDEX idx_users_lower_email ON users (lower(email));`

但如果业务允许，更推荐写入时就规范化：

1	`用户输入邮箱 -> trim -> lower -> 存储 normalized_email`

7.4 JOIN优化

JOIN优化先看数据量和过滤顺序。

常见问题：

JOIN前没有先过滤大表。
JOIN字段类型不一致。
子表外键列没有索引。
多列相关性导致估算错误。
结果集很大，还做排序和分页。

示例：

SELECT o.id, o.amount, u.name
FROM orders o
JOIN users u ON u.id = o.user_id
WHERE o.tenant_id = $1
  AND o.created_at >= $2
ORDER BY o.created_at DESC
LIMIT 100;

索引：

CREATE INDEX idx_orders_tenant_created_user
ON orders (tenant_id, created_at DESC, user_id);

-- users.id通常已经是主键

如果JOIN字段类型不一致：

1 2	`-- 不推荐：orders.user_id是bigint，users.id_text是text JOIN users u ON u.id_text = o.user_id::text`

应该修正模型，让两边字段类型一致。

7.5 EXISTS通常比COUNT判断存在更合适

只判断是否存在：

-- 不推荐
SELECT count(*)
FROM orders
WHERE user_id = $1;

更推荐：

SELECT EXISTS (
    SELECT 1
    FROM orders
    WHERE user_id = $1
);

EXISTS找到一行就可以停止，COUNT(*)通常要数完满足条件的所有行。

7.6 CTE不要滥用

PG 12以后，普通CTE在很多情况下可以被优化器内联，不再天然是优化屏障。但CTE依然可能影响优化，尤其是显式使用MATERIALIZED时。

普通写法：

WITH recent_orders AS (
    SELECT *
    FROM orders
    WHERE created_at >= now() - interval '7 days'
)
SELECT *
FROM recent_orders
WHERE status = 'paid';

如果CTE结果很大，而外层还能继续下推条件，最好用EXPLAIN确认优化器是否做了合适处理。

明确要求物化：

WITH recent_orders AS MATERIALIZED (
    SELECT *
    FROM orders
    WHERE created_at >= now() - interval '7 days'
)
SELECT *
FROM recent_orders
WHERE status = 'paid';

明确允许非物化：

WITH recent_orders AS NOT MATERIALIZED (
    SELECT *
    FROM orders
    WHERE created_at >= now() - interval '7 days'
)
SELECT *
FROM recent_orders
WHERE status = 'paid';

工程建议：

CTE适合提升可读性、复用中间结果、递归查询。
复杂查询不要因为“看起来整洁”拆成一堆CTE后不看计划。
大结果CTE是否物化，要用EXPLAIN (ANALYZE, BUFFERS)确认。

7.7 批量写入优化

单行循环插入：

1 2	`INSERT INTO events (id, payload, created_at) VALUES ($1, $2, $3);`

如果应用循环执行几万次，网络往返和事务开销会很大。

更好的方式：

多行INSERT。
使用COPY。
分批提交。
导入后ANALYZE。
大导入前评估是否先删除非必要索引，导入后重建。

多行插入：

INSERT INTO events (id, payload, created_at)
VALUES
    (1, '{"type":"a"}', now()),
    (2, '{"type":"b"}', now()),
    (3, '{"type":"c"}', now());

COPY：

1 2	`COPY events (id, payload, created_at) FROM STDIN WITH (FORMAT csv);`

导入后：

1	`ANALYZE events;`

事务不要无限大：

太大的事务
  ├── WAL暴涨
  ├── 锁持有时间长
  ├── 回滚代价高
  ├── 复制延迟上升
  └── VACUUM清理受影响

8.统计信息与优化器

PG优化器依赖统计信息选择计划。统计信息不准，索引再好也可能不用。

8.1 ANALYZE

更新某张表统计信息：

1	`ANALYZE orders;`

带清理：

1	`VACUUM (ANALYZE) orders;`

大批量导入、更新、删除后，建议手动执行：

1	`ANALYZE table_name;`

查看表统计时间：

SELECT
    relname,
    n_live_tup,
    n_dead_tup,
    last_analyze,
    last_autoanalyze,
    last_vacuum,
    last_autovacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 20;

8.2 提高单列统计目标

某些列分布极不均匀，例如：

status:
  paid      90%
  pending    5%
  failed     4%
  manual     1%

默认统计可能不足以描述真实分布，可以提高统计目标：

ALTER TABLE orders
ALTER COLUMN status SET STATISTICS 1000;

ANALYZE orders;

不要全库盲目提高统计目标，因为会增加ANALYZE时间和统计信息体积。

8.3 扩展统计信息

如果多个列强相关，单列统计可能误判。

例子：

tenant_id = 10001 的订单大多是 pending
tenant_id = 10002 的订单大多是 paid

优化器只看tenant_id和status各自分布时，
可能不知道这两个条件放在一起会命中多少行。

可以创建扩展统计：

CREATE STATISTICS st_orders_tenant_status
ON tenant_id, status
FROM orders;

ANALYZE orders;

适合：

多租户下tenant_id和业务字段强相关。
country和city强相关。
category_id和brand_id强相关。
多个状态字段组合选择性和单列估算差异大。

9.MVCC、VACUUM与表膨胀

PG使用MVCC。更新和删除不是马上原地覆盖或物理移除旧行。

UPDATE一行
  ├── 写入新版本
  ├── 旧版本标记为不可见
  └── 等没有事务需要旧版本后，由VACUUM清理

如果清理跟不上，就会出现：

表膨胀。
索引膨胀。
查询扫描更多页面。
缓存命中率下降。
备份变大。
autovacuum压力增加。

9.1 VACUUM做什么

VACUUM主要做几件事：

回收或标记可复用的死元组空间。
更新优化器统计信息，配合ANALYZE。
更新visibility map，帮助Index Only Scan。
防止事务ID回卷问题。

常用命令：

1
2
3

VACUUM orders;
VACUUM (ANALYZE) orders;
VACUUM (VERBOSE, ANALYZE) orders;

VACUUM FULL：

1	`VACUUM FULL orders;`

注意：

标准VACUUM可以和普通读写并行。
VACUUM FULL会重写整张表，需要强锁，通常会阻塞业务。
生产环境不要把VACUUM FULL当成日常维护手段。
如果经常需要VACUUM FULL，说明表设计、写入模式或autovacuum配置有问题。

9.2 监控死元组

SELECT
    schemaname,
    relname,
    n_live_tup,
    n_dead_tup,
    round(n_dead_tup * 100.0 / nullif(n_live_tup + n_dead_tup, 0), 2) AS dead_pct,
    last_autovacuum,
    last_autoanalyze
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 20;

如果某张表持续n_dead_tup很高，要看：

autovacuum是否触发。
是否有长事务阻止清理。
表是否写入/更新过于频繁。
是否需要按表调整autovacuum参数。

9.3 大表autovacuum调优

默认触发阈值大致是：

1 2	`触发阈值 = autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor × 表行数`

大表上比例阈值可能太高。比如1亿行表，20%才触发就是2000万行变化，已经很晚。

可以按表设置：

ALTER TABLE orders SET (
    autovacuum_vacuum_scale_factor = 0.02,
    autovacuum_analyze_scale_factor = 0.01,
    autovacuum_vacuum_threshold = 5000,
    autovacuum_analyze_threshold = 5000
);

工程建议：

大表、高更新表按表调小scale factor。
不要直接关闭autovacuum。
批量导入后手动ANALYZE。
长事务是VACUUM的大敌，要设置超时。

9.4 长事务排查

SELECT
    pid,
    usename,
    application_name,
    state,
    xact_start,
    now() - xact_start AS xact_age,
    query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY xact_start ASC;

建议设置：

1	`ALTER SYSTEM SET idle_in_transaction_session_timeout = '5min';`

或者按角色设置：

1	`ALTER ROLE app_user SET idle_in_transaction_session_timeout = '5min';`

10.服务端核心参数调优

参数不是越大越好。调参前先确认工作负载：

工作负载	特点	优化重点
OLTP	短事务、高并发、点查/小范围查询	连接池、索引、锁、WAL、checkpoint。
报表	大扫描、JOIN、聚合、排序	`work_mem`、并行、统计信息、读库隔离。
写入密集	大量INSERT/UPDATE/DELETE	WAL、checkpoint、autovacuum、索引数量。
时序/日志	追加写、大表、按时间查	分区、BRIN、批量写、历史清理。
混合负载	在线接口和批处理混跑	限流、读写隔离、资源治理。

10.1 内存参数

参数	作用	常见起点	注意事项
`shared_buffers`	PG自己的共享缓存池	专用DB机器从物理内存25%左右开始	PG也依赖OS cache，通常不建议盲目超过40%。
`effective_cache_size`	告诉优化器可用缓存大概有多大	物理内存50%~75%估算	它不是实际分配内存，而是优化器成本估算参数。
`work_mem`	单个排序、哈希、聚合等操作可用内存	OLTP几MB到几十MB	不是每连接只用一次，复杂SQL可能用多份。
`maintenance_work_mem`	`VACUUM`、`CREATE INDEX`等维护任务内存	512MB、1GB或更高	看机器内存和维护任务并发数。

work_mem风险最大。粗略估算：

潜在内存消耗 =
  活跃连接数
  × 单SQL平均排序/哈希节点数
  × work_mem

所以不要这样做：

1 2	`max_connections = 1000 work_mem = 256MB`

这类配置在并发报表或复杂JOIN下很容易把内存打爆。

更合理：

全局work_mem保守。
报表任务按会话临时调大。
控制活跃连接数。
使用连接池。

10.2 连接参数

参数	作用	建议
`max_connections`	最大连接数	不要为了应用方便调到几千，优先PgBouncer。
`superuser_reserved_connections`	保留管理员连接	生产必须保留，避免故障时连不上。
`idle_in_transaction_session_timeout`	空闲事务超时	建议设置，防止长事务拖垮VACUUM。
`statement_timeout`	SQL执行超时	按业务、角色或连接设置。
`lock_timeout`	等锁超时	防止DDL或请求无限等待。

连接数不是越多越好：

1 2	`应用实例数 × 每实例连接池大小 <= 数据库可承受连接数`

PG更适合少量活跃连接高效执行，不适合几千个连接同时抢CPU。

10.3 WAL和checkpoint

参数	作用	建议
`max_wal_size`	checkpoint前允许积累的WAL量	写入密集业务适当增大，减少频繁checkpoint。
`checkpoint_timeout`	checkpoint时间间隔	常见5~15分钟，结合恢复时间要求。
`checkpoint_completion_target`	平滑checkpoint写入	常见设置接近0.9。
`wal_compression`	WAL压缩	大字段更新、full page image多时评估。
`wal_level`	WAL级别	物理复制至少`replica`，逻辑复制需要`logical`。

checkpoint太频繁会造成周期性IO抖动。可以观察：

1 2	`SELECT * FROM pg_stat_bgwriter;`

PG 16+也可以关注：

1 2	`SELECT * FROM pg_stat_checkpointer;`

10.4 参数调整原则

调参前：

SHOW shared_buffers;
SHOW work_mem;
SHOW maintenance_work_mem;
SHOW effective_cache_size;
SHOW max_connections;

修改方式：

1 2	`ALTER SYSTEM SET work_mem = '16MB'; SELECT pg_reload_conf();`

有些参数需要重启，例如shared_buffers：

1 2	`ALTER SYSTEM SET shared_buffers = '8GB'; -- 重启PG后生效`

工程建议：

先记录当前值。
一次只改少数参数。
改完观察一段时间。
保留回滚方案。
不要直接照搬网上“XX核XXG标准配置”。

11.连接池与PgBouncer

微服务架构里，很多服务实例直连PG，很容易把连接数打爆。

1	`20个服务 × 每个服务10个Pod × 每个Pod 20个连接 = 4000个连接`

这对PG通常不是好事。

推荐架构：

1
2
3

application
  -> PgBouncer
  -> PostgreSQL

PgBouncer的目标是降低创建和维持大量PostgreSQL连接的成本。

11.1 池化模式

模式	含义	适用
Session pooling	客户端连接期间占用一个服务端连接	兼容性最好，连接复用率较低。
Transaction pooling	一个事务期间占用服务端连接，事务结束归还	最常用，连接复用率高，但要求应用不要依赖session状态。
Statement pooling	每条语句后归还连接	最激进，多语句事务不支持，业务系统少用。

Transaction pooling注意：

不要依赖SET search_path这类session状态长期保留。
临时表、session级advisory lock、部分prepared statement用法要验证。
应用连接池和PgBouncer连接池要一起设计，避免池套池造成排队不可控。

11.2 应用连接池建议

应用侧连接池不要按“越大越好”配置。

建议关注：

数据库CPU核心数。
SQL平均执行时间。
请求并发。
PgBouncer池大小。
业务超时时间。

一个更稳的思路：

应用侧连接池：
  控制每个实例到PgBouncer的并发

PgBouncer：
  控制到PostgreSQL的真实连接数

PostgreSQL：
  保持有限活跃连接，减少上下文切换和内存放大

12.Schema多租户优化

PG的schema适合做命名空间隔离，但不等于“租户越多越适合schema隔离”。

12.1 Schema隔离的优点

database
  ├── tenant_a.users
  ├── tenant_a.orders
  ├── tenant_b.users
  └── tenant_b.orders

优点：

租户数据物理表层面隔离。
单租户备份、迁移、清理比较直观。
某些企业客户定制字段或对象更容易处理。
权限隔离模型清晰。

12.2 Schema隔离的性能暗礁

12.2.1 search_path切换

常见做法：

1	`SET search_path TO tenant_a, public;`

问题：

每次切换都有额外SQL和会话状态管理成本。
使用连接池时，连接复用后必须清理状态。
PgBouncer transaction pooling下，不能假设session状态一直绑定客户端。

更稳的做法：

SQL显式带schema，或者在单事务内设置SET LOCAL search_path。
使用连接池的reset机制清理状态。
对transaction pooling做兼容性测试。

BEGIN;
SET LOCAL search_path TO tenant_a, public;
SELECT * FROM orders WHERE id = $1;
COMMIT;

12.2.2 系统表膨胀

如果每个租户一套表，租户达到数千甚至上万，系统目录会急剧膨胀：

pg_class
pg_attribute
pg_index
pg_constraint
pg_namespace

影响：

DDL变慢。
元数据查询变慢。
备份恢复变复杂。
migration成本放大。
管理工具打开对象列表变慢。

经验判断：

租户规模	建议
几个到几十个强隔离租户	schema隔离可以接受。
几百个租户	谨慎，需要压测元数据和迁移流程。
几千上万个租户	优先考虑共享表 + `tenant_id`，或分库/分实例。

12.3 共享表tenant_id模式

public.orders
  tenant_id
  order_id
  status
  created_at

常见索引：

CREATE INDEX idx_orders_tenant_created
ON orders (tenant_id, created_at DESC);

CREATE UNIQUE INDEX orders_tenant_order_no_uk
ON orders (tenant_id, order_no);

优点：

元数据规模稳定。
migration简单。
跨租户统计方便。
更适合大量中小租户。

注意：

所有查询必须带tenant_id。
权限和数据隔离要有工程约束。
可以配合RLS，但要理解性能和调试成本。

13.分区表与大表优化

大表优化不能只靠索引。很多时候要先把数据生命周期设计清楚。

适合分区：

时间序列数据。
日志、事件、流水。
按租户或业务线天然隔离的大表。
历史数据需要按周期归档或删除。

时间分区示例：

CREATE TABLE events (
    id bigint NOT NULL,
    payload jsonb NOT NULL,
    created_at timestamptz NOT NULL
) PARTITION BY RANGE (created_at);

CREATE TABLE events_2026_06
PARTITION OF events
FOR VALUES FROM ('2026-06-01') TO ('2026-07-01');

分区表优化点：

查询条件必须带分区键，才能分区裁剪。
每个分区的索引更小、更容易缓存。
删除历史分区比大表DELETE高效得多。
分区太多也会增加规划和管理成本。
全局唯一约束受分区键限制，要提前设计。

删除历史数据：

1	`DROP TABLE events_2025_01;`

比下面这种大批量删除更容易控制：

1 2	`DELETE FROM events WHERE created_at < '2025-02-01';`

14.锁与并发优化

慢不一定是SQL慢，也可能是在等锁。

14.1 查看锁等待

SELECT
    blocked.pid AS blocked_pid,
    blocked.query AS blocked_query,
    blocking.pid AS blocking_pid,
    blocking.query AS blocking_query,
    now() - blocked.query_start AS blocked_duration
FROM pg_stat_activity blocked
JOIN pg_locks blocked_locks
    ON blocked_locks.pid = blocked.pid
JOIN pg_locks blocking_locks
    ON blocking_locks.locktype = blocked_locks.locktype
   AND blocking_locks.database IS NOT DISTINCT FROM blocked_locks.database
   AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
   AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
   AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
   AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
   AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
   AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
   AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
   AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
   AND blocking_locks.pid <> blocked_locks.pid
JOIN pg_stat_activity blocking
    ON blocking.pid = blocking_locks.pid
WHERE NOT blocked_locks.granted
  AND blocking_locks.granted;

常见锁问题：

事务打开后长时间不提交。
后台任务批量更新太多行。
DDL直接在线执行，等待或阻塞业务。
外键缺少索引，删除父表时扫子表。
应用重试没有退避，造成锁竞争放大。

14.2 生产DDL建议

低风险原则：

大表建索引用CREATE INDEX CONCURRENTLY。
大表加约束可以先NOT VALID，再VALIDATE CONSTRAINT。
加NOT NULL、改字段类型、重写表这类操作要提前评估锁和重写成本。
DDL设置lock_timeout，避免无限等待。

示例：

SET lock_timeout = '3s';

CREATE INDEX CONCURRENTLY idx_orders_created_at
ON orders (created_at);

约束：

ALTER TABLE orders
ADD CONSTRAINT orders_amount_check
CHECK (amount >= 0) NOT VALID;

ALTER TABLE orders
VALIDATE CONSTRAINT orders_amount_check;

15.常用监控和排查SQL

15.1 表大小和索引大小

SELECT
    schemaname,
    relname,
    pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
    pg_size_pretty(pg_relation_size(relid)) AS table_size,
    pg_size_pretty(pg_total_relation_size(relid) - pg_relation_size(relid)) AS index_size
FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC
LIMIT 20;

15.2 索引使用情况

SELECT
    schemaname,
    relname AS table_name,
    indexrelname AS index_name,
    idx_scan,
    idx_tup_read,
    idx_tup_fetch,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
ORDER BY idx_scan ASC, pg_relation_size(indexrelid) DESC
LIMIT 50;

字段含义：

idx_scan：索引扫描次数。
idx_tup_read：从索引读到的索引项数量。
idx_tup_fetch：通过索引回表取到的行数。

15.3 表扫描情况

SELECT
    schemaname,
    relname,
    seq_scan,
    seq_tup_read,
    idx_scan,
    idx_tup_fetch,
    n_live_tup,
    n_dead_tup
FROM pg_stat_user_tables
ORDER BY seq_tup_read DESC
LIMIT 20;

如果大表seq_tup_read持续很高，要确认：

是否有合理索引。
是否是报表大扫描。
是否缺少查询条件。
是否统计信息不准。

15.4 临时文件和落盘

SELECT
    datname,
    temp_files,
    pg_size_pretty(temp_bytes) AS temp_size
FROM pg_stat_database
ORDER BY temp_bytes DESC;

临时文件多，通常和排序、Hash Join、Hash Aggregate有关。优化方向不是只调work_mem，还要看SQL是否处理了过多数据。

15.5 当前等待事件

SELECT
    wait_event_type,
    wait_event,
    count(*) AS cnt
FROM pg_stat_activity
WHERE state <> 'idle'
GROUP BY wait_event_type, wait_event
ORDER BY cnt DESC;

常见判断：

Lock：锁等待。
IO：磁盘或文件读写等待。
Client：可能是客户端不读结果、网络慢、应用处理慢。
LWLock：内部轻量锁竞争，需要结合版本和具体事件分析。

16.优化流程清单

16.1 单条慢SQL

1. 拿到SQL和参数
2. EXPLAIN (ANALYZE, BUFFERS)
3. 看是否Seq Scan大表
4. 看估算rows和actual rows是否差异巨大
5. 看Sort/Hash是否落盘
6. 看Index Cond和Filter区别
7. 看返回列是否过宽
8. 看JOIN顺序和loops
9. 更新统计信息或创建扩展统计
10. 设计或调整索引
11. 复测执行计划和耗时

16.2 全库性能下降

1. 看连接数和活跃SQL
2. 看锁等待
3. 看CPU、IO、内存、磁盘空间
4. 看checkpoint和WAL增长
5. 看autovacuum是否滞后
6. 看是否有批处理、备份、DDL、报表任务
7. 看pg_stat_statements里是否有新热点SQL
8. 看应用发布是否改变SQL模式

16.3 建索引前检查

1. 这个索引服务哪几条SQL？
2. SQL调用频率多高？
3. 查询选择性如何？
4. 是否能同时服务过滤和排序？
5. 是否需要INCLUDE覆盖列？
6. 是否可以用部分索引缩小体积？
7. 该表写入频率如何？
8. 线上是否必须CONCURRENTLY？
9. 建完如何验证是否被使用？
10. 如果无效，如何回滚？

16.4 删除索引前检查

1. 是否是主键/唯一约束/外键相关索引？
2. idx_scan低是否因为统计刚重置？
3. 是否有低频但关键的后台任务依赖？
4. 是否有灾备、审计、报表SQL依赖？
5. 是否能先在预发或只读副本验证？
6. 删除后是否会影响写入约束？

17.MySQL开发者看PG优化的差异

17.1 MVCC和VACUUM要纳入日常维护

MySQL InnoDB也有MVCC，但PG的表膨胀、autovacuum、visibility map对查询性能的影响更需要开发和DBA共同关注。

PG里：

高频更新表要关注n_dead_tup。
长事务会阻止旧版本清理。
Index Only Scan效果受visibility map影响。
VACUUM FULL不是日常手段。

17.2 JSONB很强，但不要把PG当MongoDB

PG的jsonb加GIN索引很强，但不能替代清晰的数据模型。

适合放JSONB：

扩展属性。
事件payload。
不稳定、低频过滤的字段。
第三方回调原始报文。

不适合长期只放JSONB：

高频过滤字段。
高频JOIN字段。
强约束字段。
需要精确统计和权限控制的核心业务字段。

工程做法：

核心字段列化
扩展字段jsonb
固定key高频查询用表达式索引
任意包含查询再用GIN

17.3 PG更依赖统计信息和成本模型

PG优化器很强，但它需要准确统计信息。大批量变更后，ANALYZE不是可选项。

1	`ANALYZE orders;`

对多列相关性，要考虑：

1
2
3

CREATE STATISTICS st_orders_tenant_status
ON tenant_id, status
FROM orders;

17.4 PG的索引类型更多，别只会B-Tree

MySQL日常业务里大部分索引都是B+Tree。PG里索引类型更丰富：

GIN处理数组、JSONB、全文检索。
GiST处理空间、范围、排他约束。
BRIN处理超大顺序数据。
表达式索引、部分索引、覆盖索引非常实用。

但更多选择也意味着更容易滥用。索引类型必须和操作符、查询模式匹配。

18.常见场景建议

18.1 订单列表接口

查询：

SELECT id, status, amount, created_at
FROM orders
WHERE tenant_id = $1
  AND user_id = $2
ORDER BY created_at DESC, id DESC
LIMIT 20;

索引：

1
2
3

CREATE INDEX idx_orders_tenant_user_created_id
ON orders (tenant_id, user_id, created_at DESC, id DESC)
INCLUDE (status, amount);

分页：

SELECT id, status, amount, created_at
FROM orders
WHERE tenant_id = $1
  AND user_id = $2
  AND (created_at, id) < ($3, $4)
ORDER BY created_at DESC, id DESC
LIMIT 20;

18.2 任务队列

查询：

SELECT id
FROM jobs
WHERE status = 'pending'
  AND next_run_at <= now()
ORDER BY next_run_at ASC, id ASC
LIMIT 100
FOR UPDATE SKIP LOCKED;

索引：

1
2
3

CREATE INDEX idx_jobs_pending_next_run
ON jobs (next_run_at ASC, id ASC)
WHERE status = 'pending';

注意：

用部分索引只覆盖待处理任务。
SKIP LOCKED适合多worker抢任务。
单次领取数量不要过大。
任务执行和任务领取事务要拆开，避免长事务。

18.3 日志事件表

数据特点：

追加写。
按时间范围查询。
数据量大。
历史数据定期清理。

建议：

按时间分区
每个分区created_at建BRIN
高频固定过滤字段单独B-Tree
payload用jsonb保存原始事件
固定payload key查询用表达式索引

示例：

CREATE INDEX idx_events_2026_06_created_brin
ON events_2026_06 USING brin (created_at);

CREATE INDEX idx_events_2026_06_type
ON events_2026_06 ((payload ->> 'event_type'));

18.4 用户搜索

精确邮箱：

1 2	`CREATE UNIQUE INDEX users_lower_email_uk ON users (lower(email));`

手机号：

1 2	`CREATE INDEX users_phone_idx ON users (phone);`

昵称模糊搜索：

CREATE EXTENSION IF NOT EXISTS pg_trgm;

CREATE INDEX users_nickname_trgm
ON users USING gin (nickname gin_trgm_ops);

注意：

前缀搜索和包含搜索不是一种问题。
LIKE 'abc%'可考虑B-Tree操作符类。
LIKE '%abc%'通常考虑pg_trgm。
更复杂的搜索排序、分词、高亮，可能应该用专门搜索引擎。

19.参考资料

数据库

#PostgreSQL #数据库优化 #索引 #执行计划

PostgreSQL优化

https://www.albeltbo.com/posts/43c03313.html

作者

AlbeltBo

发布于

2026年7月14日

许可协议

空间计算基础上一篇

etcd原理与高可用下一篇