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 可以显著提高数据库性能监控和优化的效率。然而，在启用前需要充分评估其性能影响，并定期维护存储的数据。