PostgreSQL / 系统课时
建立综合场景性能基线
建立综合场景性能基线
本课把稳定关系型字段与 JSONB 元数据放进同一套可复测工作负载中,固定数据规模、查询参数与采样口径,记录执行计划、缓冲区访问、统计信息和空间占用,为后续索引与 SQL 改造建立可比较的优化前证据。
学习目标
- 建立覆盖执行时间、扫描规模、缓冲区访问和空间占用的性能基线
- 区分关系型过滤条件与 JSONB 条件在混合查询中的职责
- 用固定 SQL 与采样口径保存可重复比较的优化前记录
固定表结构与代表性工作负载
假设 event_store 用普通列保存租户、事件类型和时间等稳定字段,用 metadata 保存来源、国家和标签等动态属性。基线首先要固定数据分布和典型查询,否则后续很容易把偶发的缓存命中或参数差异误判成优化收益。
下面创建 100000 行确定性数据,并以一条混合查询作为主样本:先用 tenant_id、event_type 过滤稳定字段,再用 JSONB 包含运算符 @> 判断来源,最后按时间倒序取最近 50 条。
DROP TABLE IF EXISTS event_store;
CREATE TABLE event_store (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
tenant_id bigint NOT NULL,
event_type text NOT NULL,
occurred_at timestamptz NOT NULL,
metadata jsonb NOT NULL
);
INSERT INTO event_store (tenant_id, event_type, occurred_at, metadata)
SELECT
(g % 100) + 1,
CASE WHEN g % 5 = 0 THEN 'purchase' ELSE 'view' END,
timestamptz '2026-01-01 00:00:00+00' + g * interval '1 minute',
jsonb_build_object(
'source', CASE WHEN g % 3 = 0 THEN 'mobile' ELSE 'web' END,
'country', CASE WHEN g % 4 = 0 THEN 'US' ELSE 'CA' END,
'tags', jsonb_build_array('campaign-' || (g % 20))
)
FROM generate_series(1, 100000) AS g;
ANALYZE event_store;
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT id, occurred_at, metadata
FROM event_store
WHERE tenant_id = 1
AND event_type = 'purchase'
AND metadata @> '{"source":"mobile"}'
ORDER BY occurred_at DESC
LIMIT 50;预期结果
DROP TABLE
CREATE TABLE
INSERT 0 100000
ANALYZE
Limit (actual time=<TIME>..<TIME> rows=50 loops=1)
-> Sort (actual time=<TIME>..<TIME> rows=<VALUE> loops=1)
Sort Key: occurred_at DESC
-> Seq Scan on event_store (actual time=<TIME>..<TIME> rows=<VALUE> loops=1)
Filter: ((tenant_id = 1) AND (event_type = 'purchase'::text) AND (metadata @> '{"source": "mobile"}'::jsonb))
Rows Removed by Filter: <VALUE>
Buffers: shared hit=<VALUE> read=<VALUE>
Planning Time: <TIME> ms
Execution Time: <TIME> ms实际节点、耗时、缓冲区读写和行数会随 PostgreSQL 版本、硬件、缓存状态与参数变化,甚至可能出现并行计划。基线要固定记录的是扫描方式、实际行数、过滤规模、缓冲区访问和最终耗时,而不是追求与示例相同的毫秒值。
采集空间与索引基线
执行计划回答“查询怎么跑”,空间统计回答“当前结构付出了多少存储成本”。新增索引前先记录表大小、索引总大小以及现有索引的累计访问次数,后续才能判断查询收益是否伴随了明显的存储与维护成本。
SELECT
pg_size_pretty(pg_relation_size('event_store')) AS table_size,
pg_size_pretty(pg_indexes_size('event_store')) AS indexes_size,
pg_size_pretty(pg_total_relation_size('event_store')) AS total_size;
SELECT
indexrelname,
idx_scan,
idx_tup_read,
idx_tup_fetch
FROM pg_stat_user_indexes
WHERE relname = 'event_store'
ORDER BY indexrelname;预期结果
table_size | indexes_size | total_size
------------+--------------+------------
<VALUE> | <VALUE> | <VALUE>
indexrelname | idx_scan | idx_tup_read | idx_tup_fetch
------------------+----------+--------------+---------------
event_store_pkey | <VALUE> | <VALUE> | <VALUE>pg_stat_user_indexes 是累计统计,可能包含本轮实验之前的访问。正式治理应同时记录采样时间;独立测试环境若需要隔离工作负载,可以按测试流程重置统计后再复测,但不要为了单次实验在生产环境随意清空统计。
补充持续负载证据
单次 EXPLAIN 适合解释访问路径,却不能说明一条 SQL 在持续流量中的调用频率和累计成本。环境已启用 pg_stat_statements 时,应把同一采样窗口内的 calls、total_exec_time、mean_exec_time 和 rows 纳入基线;未启用时则使用现有慢查询日志,不要为了本次演练临时改生产参数。
先确认扩展是否可用:
SELECT CASE
WHEN EXISTS (
SELECT 1
FROM pg_extension
WHERE extname = 'pg_stat_statements'
) THEN 'enabled'
ELSE 'not_enabled'
END AS pg_stat_statements_status;预期结果
pg_stat_statements_status
---------------------------
<VALUE>当结果为 enabled 时,再按当前 PostgreSQL 版本对应的 pg_stat_statements 字段采集高成本语句;不同大版本的统计字段可能存在差异,因此通用基线脚本应先确认版本与扩展定义,而不是直接假定字段集合完全一致。
固化可比较的基线记录
一次有效基线至少要把查询指纹、代表性参数、执行计划摘要、实际执行时间、实际返回行数、扫描规模、共享缓冲区命中与读取、表大小、索引大小和采样时间放在同一份记录中。关系型条件与 JSONB 条件也要原样保留,后续才能判断收益来自普通列索引、JSONB 索引还是 SQL 改写。
后续每次新增索引、调整表达式或改写 SQL,都应使用同一组查询、近似数据量和相同采样方法复测。只有优化前后的测量口径一致,性能提升才是可验证的基线差异,而不是一次偶然的“跑得更快”。