PostgreSQL的扩展(extensions)-常用的扩展之pg_store_plans
PostgreSQL的扩展(extensions)-常用的扩展之pg_store_plans
pg_store_plans
是一个 PostgreSQL 扩展,用于捕获查询执行计划(execution plans)并将其存储在数据库中。通过存储和分析这些执行计划,数据库管理员和开发者可以更好地了解查询的性能特征,并进行优化。这个扩展非常适用于监控查询性能、诊断慢查询以及跟踪执行计划的变化。
安装 pg_store_plans
在安装 pg_store_plans
之前,需要确保已经安装了 PostgreSQL 的开发包(例如 libpq-dev
或 postgresql-server-dev-X.Y
),这样才能编译和安装扩展。
从源码安装
首先,从 pg_store_plans
的官方 GitHub 项目下载源码并进行编译安装。
-
克隆仓库:
git clone https://github.com/ossc-db/pg_store_plans.git cd pg_store_plans
-
编译和安装:
make sudo make install
配置 pg_store_plans
-
加载扩展:
在 PostgreSQL 配置文件
postgresql.conf
中添加或修改以下配置:shared_preload_libraries = 'pg_store_plans' pg_store_plans.plan_format = 'json' # 可选值: 'text', 'json'
重启 PostgreSQL 服务以加载扩展:
sudo systemctl restart postgresql
-
在数据库中创建扩展:
使用
psql
或其他 PostgreSQL 客户端连接到数据库,并执行以下 SQL 命令以创建扩展:CREATE EXTENSION pg_store_plans;
使用 pg_store_plans
pg_store_plans
会自动捕获查询执行计划并将其存储在系统的专用表中。通常,这些表位于 pg_catalog
模式下。
查看捕获的执行计划
捕获的执行计划存储在 pg_catalog.pg_store_plans
表中。你可以查询这个表来查看存储的计划。
SELECT * FROM pg_catalog.pg_store_plans;
检查执行计划
展示具体的执行计划信息,比如查询的哈希值、执行计划的 JSON 结构等。
SELECT queryid, query, plan FROM pg_catalog.pg_store_plans WHERE queryid = 'your_queryid_here';
统计和分析查询
你可以根据不同需要来统计查询的执行计划信息。例如,可以按查询 ID 汇总信息,以找到执行时间最长的查询:
SELECT queryid, min(run_total_time) as min_time,max(run_total_time) as max_time,avg(run_total_time) as avg_time,count(*) as executions
FROM pg_catalog.pg_store_plans
WHERE queryid IS NOT NULL
GROUP BY queryid
ORDER BY avg_time DESC
LIMIT 10;
示例
下面是一个完整的示例,展示如何使用 pg_store_plans
来捕获、存储并分析查询的执行计划。
-
创建一个示例表并插入数据:
CREATE TABLE employees (id SERIAL PRIMARY KEY,name VARCHAR(100),department_id INT );INSERT INTO employees (name, department_id) SELECT 'Employee ' || g, g % 10 FROM generate_series(1, 10000) g;
-
执行一个带有复杂查询的 SQL 语句,以便捕获执行计划:
EXPLAIN ANALYZE SELECT * FROM employees WHERE department_id = 1;
-
查看存储在
pg_store_plans
表中的执行计划:SELECT * FROM pg_catalog.pg_store_plans WHERE query LIKE '%SELECT * FROM employees WHERE department_id = 1%';
-
分析查询的执行计划信息:
SELECT queryid, planid, plan FROM pg_catalog.pg_store_plans WHERE queryid = 'your_queryid_here';
这将返回查询的执行计划 JSON 结构,你可以进一步解析和分析它。
注意事项
- 性能开销:启用
pg_store_plans
可能会引入一些性能开销,特别是在高负载的生产环境中。建议在测试环境中验证其影响,并适当配置捕获规则。 - 存储空间:由于捕获并存储了大量的执行计划,可能会占用较多存储空间。需要定期清理或归档旧的执行计划数据。
- 版本兼容性:确保
pg_store_plans
的版本与 PostgreSQL 服务器版本兼容。 - 安全性:存储的查询和执行计划可能包含敏感数据,确保适当的安全措施以防止未授权访问。
总结
pg_store_plans
是一个强大的 PostgreSQL 扩展,提供了捕获并存储查询执行计划的功能。通过存储和分析执行计划,数据库管理员和开发者可以深入了解查询的性能表现,并能够有针对性地优化复杂查询。通过正确的安装、配置和使用,pg_store_plans
可以显著提高数据库性能监控和优化的效率。然而,在启用前需要充分评估其性能影响,并定期维护存储的数据。