PostgreSQL常用SQL速查：执行计划与锁等待排查

PG 常用 SQL 这个话题看着基础，但真到了项目里，手边没一份能直接抄的语句清单，效率会掉一大截。我维护过几个数据量从几百万到上亿行的库，踩过的坑大多不是"不会写 SQL"，而是"记不清某个系统视图叫什么、某个参数该怎么查、某个报错到底该看哪张表"。所以我把日常高频用到的语句按场景重新梳理了一遍，从最普通的一条 SELECT 到执行计划拆解、锁等待排查，尽量做到拿过来改个表名就能跑。这篇内容适合刚上手 PG 的后端同学，也适合用惯了别的数据库、想快速把思路切到 PG 上的老手，读完至少能对"什么场景该翻哪类 SQL"有个清晰印象，而不是每次遇到问题都去搜索引擎里翻半天。

1. PG 常用 SQL 的整体思路与场景拆解

1.1 为什么不建议死记语法，而要按场景归档

SQL 语法本身不多，SELECT、INSERT、UPDATE、DELETE 加上 DDL，一页纸能写完。但真正让人卡住的从来不是语法，而是"我知道要做这件事，但不知道 PG 里对应的入口在哪"。比如想看看哪条 SQL 最耗 CPU，得去pg_stat_statements；想看看现在有没有锁等待，得去pg_locks和pg_stat_activity对着看；想确认某张表是不是膨胀了，得算死元组比例。这些都不是语法问题，是"知识索引"问题。

我的做法是按使用频率和紧急程度把常用 SQL 分成三层。第一层是天天要写的：条件查询、聚合、分页、UPSERT、批量导入，这层追求的是顺手和不出错。第二层是改表结构、加索引、调字段类型时才用的 DDL，这层追求的是知道代价在哪，别在业务高峰期执行。第三层是出问题时才翻的排查语句，这层追求的是准确，因为排查时你只有一两次机会，查错了方向会浪费大量时间。这三层的记忆方式完全不同，第一层靠肌肉记忆，第二层靠查文档加验证，第三层靠提前准备好脚本。

1.2 三类 SQL 的使用边界与常见误用

第一层查询类 SQL 最容易出的问题是"能跑但慢"。很多人写分页直接LIMIT 20 OFFSET 100000，小表没事，大表上就是灾难，因为 PG 要扫描并丢弃前十万行。这类语句必须知道它的边界在哪，超过一定偏移量就得换成基于游标或者基于索引键的分页。

第二层 DDL 的边界在于锁。ALTER TABLE ... ADD COLUMN在 PG 11 之后加带默认值的列已经很快了，但改字段类型、加约束、建索引默认都会拿强锁，大表上执行可能直接把业务堵死。我见过有人在千万级表上直接跑ALTER TABLE ... ALTER COLUMN ... TYPE，结果锁等了十几分钟，连接池全满。正确的做法是用CREATE INDEX CONCURRENTLY、分批次回填、或者干脆走"新列 + 双写 + 切换"的路子。

第三层排查语句的边界在于"别在从库上查主库的状态"。有些系统视图在只读副本上是空的或者滞后的，查之前先确认pg_is_in_recovery()的返回值。另外排查语句本身也会消耗资源，EXPLAIN ANALYZE会真实执行语句，在写操作上千万别随手加 ANALYZE。

1.3 一份适合自己的 SQL 速查清单该怎么建

我自己的习惯是建一个pg_cheatsheet.sql文件，按注释分块，每块前面写清楚"什么时候用"。比如"查表膨胀"这块下面放两条：一条算死元组比例，一条算表实际占用空间。用的时候直接搜注释关键字，比搜语法快得多。

清单里我建议至少覆盖这几类：连接与库表信息、查询与分页、聚合与窗口、写入与冲突处理、DDL 与索引、执行计划、锁与活动会话、空间与膨胀、权限、备份导出。每一类不用多，三到五条高频的就够，多了反而记不住。下面几个章节我就按这个思路展开，把我实际用得最多的语句和背后的判断逻辑都写出来。

2. 日常查询与数据操作的常用 SQL

2.1 条件查询里的几个高频写法与坑

最基础的查询没什么好说的，真正值得记的是几个 PG 特有的写法。模糊匹配上，LIKE对大小写敏感，ILIKE不敏感，很多人从别的库迁过来会习惯性写LOWER(col) LIKE '%x%'，其实用ILIKE更直观，不过两者都没法走普通 B-tree 索引，数据量大时得考虑pg_trgm扩展加 GIN 索引。

-- 大小写不敏感匹配 SELECT id, name FROM users WHERE name ILIKE '%keyword%'; -- 判断 NULL 用 IS NULL，别用 = NULL SELECT id FROM orders WHERE paid_at IS NULL; -- NULL 安全的等值比较 SELECT id FROM orders WHERE shipped_at IS NOT DISTINCT FROM NULL;

IS NOT DISTINCT FROM这个写法知道的人不多，它在做"两个字段是否相等且都能为 NULL"的判断时非常有用，比如数据比对场景，用=会把 NULL 行漏掉。

分页是另一个高频踩坑点。深分页必须换成基于键的分页：

-- 不推荐：深偏移 SELECT * FROM logs ORDER BY id LIMIT 20 OFFSET 500000; -- 推荐：基于上一页最后一行的 id SELECT * FROM logs WHERE id > 500000 ORDER BY id LIMIT 20;

这里的前提是id有索引且单调，如果排序字段不唯一，需要带上第二排序键做游标，比如(created_at, id) > ('2024-01-01', 123)这种元组比较，PG 对行值比较支持得很好，可以直接写在 WHERE 里。

还有一个很实用的写法是DISTINCT ON，它能在每个分组里取第一条，比窗口函数写起来短：

-- 每个用户最新的一笔订单 SELECT DISTINCT ON (user_id) user_id, id, amount, created_at FROM orders ORDER BY user_id, created_at DESC;

注意DISTINCT ON后面的列必须和 ORDER BY 的前缀一致，否则结果不确定，这是它唯一的硬约束。

2.2 聚合、分组与窗口函数的实战用法

聚合里最容易被忽略的是FILTER子句，它能在一次扫描里算出多个口径，不用写多个子查询：

SELECT count(*) AS total, count(*) FILTER (WHERE status = 'paid') AS paid_cnt, count(*) FILTER (WHERE status = 'refunded') AS refunded_cnt, sum(amount) FILTER (WHERE status = 'paid') AS paid_amount FROM orders WHERE created_at >= now() - interval '7 days';

这个写法在做报表和看板时特别划算，因为表只扫一遍。相比CASE WHEN包在聚合里，FILTER语义更清楚，执行计划也更干净。

窗口函数我日常用得最多的三个：row_number()做分组排序取 TopN，lag()/lead()做同比环比，sum() OVER做累计。取每个分类下销量前三：

SELECT * FROM ( SELECT category_id, product_id, sales, row_number() OVER (PARTITION BY category_id ORDER BY sales DESC) AS rn FROM product_stats ) t WHERE rn <= 3;

这里有个实操细节：如果sales有并列且你希望并列的都保留，就得用rank()或dense_rank()，row_number()会强行给每一行不同编号，并列的会被截掉。这个区别在排行榜类需求里非常关键，选错了业务方会来问"为什么少了一条"。

累计求和的标准写法是sum(x) OVER (PARTITION BY ... ORDER BY ... ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)，不写ROWS子句时默认是RANGE模式，遇到相同排序键会一次算完整个组，结果和逐行累加不一样。这一点我在做日活累计曲线时被坑过一次，数据对不上，最后就是补了ROWS子句解决的。

2.3 增删改与批量写入的核心套路

写入这块，PG 的INSERT ... ON CONFLICT是必会的，也就是常说的 UPSERT：

INSERT INTO user_points (user_id, points, updated_at) VALUES (1001, 10, now()) ON CONFLICT (user_id) DO UPDATE SET points = user_points.points + EXCLUDED.points, updated_at = now();

这里有两个细节值得说。第一，冲突目标必须是唯一索引或主键，写列名或者写约束名都行，但列上必须真的有唯一约束，否则会直接报错。第二，EXCLUDED指的是"这次准备插入的那行"，做累加时用它和原表列运算，语义最清楚。如果只想在冲突时什么都不做，用DO NOTHING，但要注意它会静默丢弃，业务上如果依赖插入结果判断，得配合RETURNING。

RETURNING是 PG 很好用的一个特性，INSERT、UPDATE、DELETE 都能带：

DELETE FROM sessions WHERE expire_at < now() RETURNING id, user_id;

做批量清理时直接拿到删掉的主键，省一次查询。批量插入时用它拿自增 ID 也很常见，不过要注意大批量RETURNING会占内存，几万行以上建议分批。

批量写入还有两个实操建议。一是用COPY代替多值 INSERT 做初始化导入，速度差一个数量级；二是多值 INSERT 每批控制在 500 到 1000 行，太多会撞上参数上限，太少网络往返浪费。更新时如果涉及大范围数据，记得加LIMIT或者分批按主键区间推进，避免一个事务锁住整张表。

3. 表结构管理与 DDL 常用语句

3.1 建表时的类型选择与默认值设计

建表看着简单，但类型选错了后面改起来很痛苦。主键我一般用bigint generated by default as identity，比serial更符合标准，也比 UUID 在主键索引上更省空间。UUID 适合需要客户端生成 ID 或者跨库合并的场景，但要清楚它的索引体积和随机写入带来的页分裂问题，高并发插入时影响不小。

时间字段统一用timestamptz，别用不带时区的timestamp。这个坑太经典了，前期用timestamp存本地时间，后面接海外业务或者跨时区报表，数据全乱。timestamptz内部存 UTC，展示时按会话时区转换，语义清晰。

CREATE TABLE orders ( id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, user_id bigint NOT NULL, amount numeric(12,2) NOT NULL DEFAULT 0, status text NOT NULL DEFAULT 'created', payload jsonb NOT NULL DEFAULT '{}'::jsonb, created_at timestamptz NOT NULL DEFAULT now(), updated_at timestamptz NOT NULL DEFAULT now() );

金额用numeric不用float，这是常识但每年都有人踩。状态字段用text加CHECK约束，比枚举类型灵活，改枚举值要ALTER TYPE，加删除很麻烦，text加约束改起来只是换个约束。JSON 字段用jsonb不用json，前者支持索引且写入时会做规范化，查询效率高很多。

建表时还有个小技巧：把created_at和updated_at的默认值都设上，updated_at再配一个触发器自动更新，省得每个 UPDATE 语句都手写一遍时间。触发器写法不复杂，但对写入性能有轻微影响，超高频写入的表要权衡一下。

3.2 字段变更与索引维护的实际操作

加字段是最常见的变更。PG 11 之后ADD COLUMN ... DEFAULT 常量是元数据操作，不会重写整表，秒级完成。但如果默认值是易变函数比如now()，PG 会走重写路径，大表上要小心。想让时间字段带默认值又不重写，可以先加空列，再设默认值，再分批回填。

ALTER TABLE orders ADD COLUMN channel text; ALTER TABLE orders ALTER COLUMN channel SET DEFAULT 'web'; UPDATE orders SET channel = 'web' WHERE channel IS NULL; -- 分批执行

改字段类型是最危险的操作之一，会拿ACCESS EXCLUSIVE锁并重写表。千万级以上的表，我的做法是新加一列、双写、回填、切换读、删旧列，整个过程业务无感，代价是代码要改两处。如果非要直接改，至少确认几件事：表的行数、磁盘剩余空间是否够两倍、是否有长事务在跑、变更窗口内能否接受短暂不可写。

索引维护上，CREATE INDEX CONCURRENTLY是必须记住的，它不阻塞写入，代价是执行慢、可能失败留下无效索引。失败的索引状态是invalid，用pg_index能查出来，处理方式是DROP INDEX CONCURRENTLY后重建。

-- 查无效索引 SELECT indexrelid::regclass AS index_name, indrelid::regclass AS table_name FROM pg_index WHERE NOT indisvalid; -- 并发建索引 CREATE INDEX CONCURRENTLY idx_orders_user_created ON orders (user_id, created_at DESC);

复合索引的字段顺序有讲究：等值条件放前面，范围条件放后面，排序字段跟着范围条件走。上面这个索引对WHERE user_id = ? ORDER BY created_at DESC正好，对WHERE created_at > ?单独用就不行，因为前导列没用上。这种"最左前缀"的规则和 B-tree 结构直接相关，理解了就不用死记。

4. 性能分析与慢查询排查 SQL

4.1 EXPLAIN 执行计划的正确读法

EXPLAIN是排查第一入口，但很多人只看总耗时，忽略了每行的actual time和rows。我读计划的顺序是：先看最内层节点的rows估算和实际差多少，差一个数量级说明统计信息不准；再看有没有Seq Scan出现在大表上；最后看loops是不是很高，高 loops 意味着这个节点被重复执行了很多次，常见于嵌套循环。

EXPLAIN (ANALYZE, BUFFERS, VERBOSE) SELECT * FROM orders WHERE user_id = 1001 ORDER BY created_at DESC LIMIT 10;

BUFFERS这个选项一定要加，它能告诉你读了多少块、命中多少缓存。如果read远大于hit，说明数据不在内存里，慢是正常的，加索引也未必有用，可能要考虑预热或者调整缓存。VERBOSE会展开输出列，排查投影开销时有用。

几个典型信号：出现Nested Loop且内层表很大，通常是连接字段缺索引；出现Sort且Sort Method: external merge Disk，说明排序内存不够，可以调work_mem或者优化排序字段；出现Rows Removed by Filter很大，说明索引选择性不好或者条件写法导致索引失效。

还有一点，EXPLAIN ANALYZE会真实执行，写语句要包在事务里回滚：

BEGIN; EXPLAIN ANALYZE UPDATE orders SET status = 'done' WHERE id = 1; ROLLBACK;

不这么做，测试环境的数据就被改掉了，我见过不止一次。

4.2 系统视图与锁等待排查

慢查询定位主要靠pg_stat_statements扩展，它按归一化后的 SQL 聚合统计，能看到总耗时、调用次数、平均耗时、共享块读写。开启方式是在shared_preload_libraries里加上它然后重启，之后CREATE EXTENSION pg_stat_statements。

SELECT queryid, 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;

我一般先按total_exec_time排序找"总耗时大户"，再按mean_exec_time加calls > 100找"单次慢且频繁"的语句。这两个维度结合，基本能锁定优化目标。

锁等待排查是线上事故的必修课。当前活动会话看pg_stat_activity，关注state、wait_event_type、query_start：

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

wait_event_type = 'Lock'基本就是锁等待，再去pg_locks里找阻塞链。pg_blocking_pids(pid)这个函数很好用，直接返回阻塞某个会话的进程号：

SELECT pid, pg_blocking_pids(pid) AS blockers, left(query, 100) FROM pg_stat_activity WHERE cardinality(pg_blocking_pids(pid)) > 0;

找到源头后，评估能不能pg_cancel_backend（温和取消当前语句）或者pg_terminate_backend（断连接）。处理前一定要看清楚那条 SQL 在干什么，别把正在做大事务回滚的会话杀了，可能引发更长恢复。

5. 权限、备份与日常运维 SQL

5.1 用户权限与 schema 管理

PG 的权限模型是库、schema、表三级，加上角色继承，刚接触容易绕晕。我通常按"一个应用一个角色"来建，角色只给必要的库和 schema 权限，不给超级用户。

CREATE ROLE app_user LOGIN PASSWORD 'change_me'; GRANT CONNECT ON DATABASE appdb TO app_user; GRANT USAGE ON SCHEMA public TO app_user; GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_user; GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO app_user; -- 让以后新建的表也自动带上权限 ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_user;

ALTER DEFAULT PRIVILEGES这条特别容易被漏掉，漏了之后运维新建一张表，应用就报权限不足，还得手动补。注意它只对"执行这条语句的角色"之后创建的对象生效，所以要在建表用的那个角色下执行。

查权限用information_schema或者has_table_privilege函数：

SELECT has_table_privilege('app_user', 'public.orders', 'SELECT');

排查"为什么这个用户查不了"时，先确认它属于哪些角色，再看这些角色在目标对象上有没有权限，pg_roles和pg_auth_members一查就清楚。另外别忘了search_path，同名表在不同 schema 下，权限可能不一样，SHOW search_path看一眼能省很多时间。

5.2 数据导出、备份与恢复的常用手段

逻辑备份用pg_dump，整库、单表、单 schema 都支持。生产环境我一般用自定义格式-Fc，压缩率高且恢复时能选择性还原：

pg_dump -h 127.0.0.1 -U app_user -d appdb -Fc -f appdb.dump pg_restore -h 127.0.0.1 -U app_user -d appdb_new -j 4 appdb.dump

-j并行恢复能明显加快速度，但只对-Fc或-Fd格式有效，纯 SQL 文本没法并行。恢复前记得目标库要存在，pg_restore不会自动建库。

单表数据导出导入，COPY比 INSERT 快得多：

COPY orders TO '/tmp/orders.csv' WITH (FORMAT csv, HEADER true); COPY orders FROM '/tmp/orders.csv' WITH (FORMAT csv, HEADER true);

注意服务端的COPY要求文件在数据库服务器上，且需要有相应文件权限；客户端用\copy元命令则走本地文件。这个区别在容器环境里经常搞混，报"找不到文件"时先想想文件到底在哪台机器上。

备份策略上，逻辑备份适合小库和跨版本迁移，大库还是得靠物理备份加 WAL 归档做时间点恢复。不管用哪种，恢复演练一定要定期做，我见过备份脚本跑了两年从没验证过，真出事时发现文件是空的。

6. 常见问题与排查技巧实录

6.1 高频报错速查表

下面这张表是我这些年翻得最多的几个报错，基本覆盖了日常八成问题。

这张表建议直接存到自己的清单里，遇到报错先对一遍，比从头分析快得多。表里没有的，再去查pg_stat_activity和日志。

6.2 实操心得与几条避坑清单

第一条心得是关于统计信息的。表数据变化超过一定比例后，autovacuum才会触发ANALYZE，中间这段时间执行计划可能很烂。大批量导入或者删数据后，我会手动跑一次ANALYZE 表名，几秒钟的事，能避免很多"昨天还快今天突然慢"的困惑。查上次分析时间用pg_stat_user_tables的last_analyze字段。

第二条是关于事务的。PG 里长事务的危害比很多人想象的大，它会让VACUUM无法回收死元组，表越来越大，查询越来越慢，形成恶性循环。查长事务：

SELECT pid, now() - xact_start AS xact_age, state, left(query, 100) FROM pg_stat_activity WHERE xact_start IS NOT NULL ORDER BY xact_age DESC LIMIT 10;

xact_age超过几分钟就该关注了，超过半小时基本可以判定有问题。常见的元凶是应用开启了事务但中间调了外部接口，接口超时导致事务一直挂着。

第三条是关于表膨胀的。pg_stat_user_tables里的n_dead_tup和n_live_tup能看出死元组比例，比例高说明需要VACUUM，但如果是VACUUM也压不下去的膨胀，就得考虑VACUUM FULL或者在线重建。VACUUM FULL会拿排他锁并需要等量磁盘空间，大表上一定要在维护窗口做，能用在线工具就别硬来。

第四条是关于 JSONB 查询的。jsonb字段上的->和->>区别要分清，前者返回 JSON 对象，后者返回文本。建 GIN 索引后，@>包含查询能走索引，但->>提取后再比较通常走不了，得建表达式索引。这个细节在做配置类表查询时经常决定性能好坏。

-- 能走 GIN 索引 SELECT * FROM orders WHERE payload @> '{"channel":"web"}'; -- 走不了普通索引，需要表达式索引 CREATE INDEX idx_orders_channel ON orders ((payload ->> 'channel'));

第五条算是个人习惯：所有排查用的 SQL 我都放在一个固定文件里，按"会话、锁、空间、统计"分块注释。真出事的时候人是慌的，靠临场拼语句容易漏条件或者拼错视图名。提前准备好，出事时复制粘贴改两个参数就能跑，这个习惯帮我省下的时间比学任何高级技巧都多。