PostgREST 性能调试实战借助 pg_stat_statements 定位慢查询【免费下载链接】postgrestREST API for any Postgres database项目地址: https://gitcode.com/GitHub_Trending/po/postgrestPostgREST 将 HTTP 请求编译为动态 SQL 再交给 PostgreSQL 执行因此传统的应用侧慢查询定位手段并不完全适用。本文介绍如何通过 PostgREST 内置的 PLAN 请求Accept: application/vnd.pgrst.plan拿到某次请求对应的查询标识符Query Identifier再回到pg_stat_statements中关联该标识符获取调用次数、执行耗时、返回行数等真实运行时统计从而把某个 URL 请求慢精确对应到某条 PostgreSQL 查询慢。读完本文你将掌握一套从 PostgREST 请求到 PostgreSQL 查询级的完整性能排查链路。前置条件在开始之前请确认以下两个前提同时满足否则下面的步骤无法执行PostgREST 侧必须开启db-plan-enabled配置项。它默认关闭False其作用是允许通过Accept: application/vnd.pgrst.plan请求头获取某次请求的执行计划。开启方式有三种配置文件在 PostgREST 配置文件中写入db-plan-enabled true默认配置模板中该项以注释形式存在见 Config.hs环境变量PGRST_DB_PLAN_ENABLEDtrue数据库内配置pgrst.db_plan_enabled仅当db-config开启时生效。该配置项为Boolean 类型、可热重载Reloadable: Y完整参数说明见 configuration.rst。PostgreSQL 侧版本需为 14 或更高且已安装并启用pg_stat_statements扩展。pg_stat_statements的queryid字段从 PostgreSQL 14 开始稳定可用本文的关联查询依赖它。注意db-plan-enabled只是开关真正生成计划的能力在源码中始终存在。PostgREST 内部通过EXPLAIN (FORMAT JSON ...)包装实际查询来生成计划其中FORMAT与各选项由请求中的媒体类型参数决定见 SqlFragment.hs 的explainF实现。第一步从 PostgREST 获取查询标识符开启配置并重启或热重载PostgREST 后对任意 GET 请求追加 PLAN 媒体类型即可让 PostgREST 返回该请求对应查询的执行计划而不是真实数据。请求时在Accept头中指定application/vnd.pgrst.planjson并附加optionsverbose选项curl http://localhost:3000/projects?selectid,nameorderid \ -H Accept: application/vnd.pgrst.planjson; optionsverbose这里application/vnd.pgrst.planjson表示以 JSON 格式返回计划optionsverbose对应 PostgreSQLEXPLAIN的VERBOSE选项它会让计划中包含顶层Query Identifier字段。响应大致如下[ { Plan: { Node Type: Aggregate }, Query Identifier: -432192689578025496 } ]响应中的Query Identifier字段其数值与 PostgreSQL 14 中pg_stat_statements.queryid的记录值一致这正是两套系统之间的关联键。关于options参数源码中explainF支持的选项映射如下见 SqlFragment.hsoptions 值对应 EXPLAIN 选项说明verboseVERBOSE输出更详细计划信息包含Query IdentifieranalyzeANALYZE实际执行查询并统计真实耗时与行数settingsSETTINGS输出影响该查询的参数设置buffersBUFFERS输出缓冲区命中/读取信息依赖ANALYZEwalWAL输出 WAL 写入统计依赖ANALYZE多个选项用|分隔例如optionsanalyze|verbose|settings|buffers|wal。媒体类型解析器对上述取值均有对应实现参见 MediaType.hs 中的 doctest 示例。仓库的端到端测试 PlanSpec.hs 也验证了buffers、settings等选项能正确返回对应内容。此外PostgREST 还支持application/vnd.pgrst.plantext返回文本格式的计划以及不带json/text后缀的application/vnd.pgrst.plan默认按文本处理解析逻辑见 MediaType.hs。计划功能对读取、写入POST/PATCH/DELETE与 RPC 调用同样适用——从源码结构看凡是进入dbActionPlan的动作只要媒体类型为MTVndPlan都会被标记为需要执行 EXPLAIN见 Plan.hs。第二步在 pg_stat_statements 中查询运行时统计拿到Query Identifier后直接用它过滤pg_stat_statements视图即可。继续使用上一步得到的-432192689578025496select calls, total_exec_time, mean_exec_time, rows, query from pg_stat_statements where queryid -432192689578025496;查询结果示例各列含义见下文说明callstotal_exec_timemean_exec_timerowsquery130.63558500000000010.0488911538461538513WITH pgrst_source AS (...)注意query列中记录的是 PostgreSQL 归一化normalized后的 SQL 文本以WITH pgrst_source AS (...)开头——这是 PostgREST 生成 SQL 时使用的统一源 CTE 名称sourceCTEName见 SqlFragment.hs 与 Plan.hs 中对根节点newFrom的改写逻辑多个不同的 HTTP 请求只要生成的 SQL 结构相同就会共享同一条pg_stat_statements记录。各列字段的业务含义calls该查询含相同归一化文本的所有实例被执行的总次数用于判断慢是偶发还是持续高频total_exec_time累计执行总耗时毫秒是衡量该查询总体开销的核心指标mean_exec_time平均单次执行耗时毫秒可据此评估单次请求的平均延迟贡献rows该查询实际返回的行数用于与业务预期对比判断是否因缺少过滤条件而返回了过多数据queryPostgreSQL 记录的归一化 SQL 原文可直接阅读 PostgREST 究竟为某个 URL 生成了怎样的 SQL。若需进一步定位还可以在pg_stat_statements中继续取max_exec_time、min_exec_time、stddev_exec_time等列观察执行时间波动或者结合pg_stat_statements_info检查统计重置时间判断数据是否因pg_stat_statements_reset()被清空。第三步完整排查流程示例将两步串起来一套可复用的排查流程如下对疑似慢的接口发起一次 PLAN 请求拿到Query Identifiercurl http://localhost:3000/projects?selectid,nameorderid \ -H Accept: application/vnd.pgrst.planjson; optionsverbose用Query Identifier在数据库中查询该查询的历史统计select calls, total_exec_time, mean_exec_time, rows from pg_stat_statements where queryid -432192689578025496;结合query列中的归一化 SQL 与EXPLAIN ANALYZE或再次使用optionsanalyze的 PLAN 请求对比计划估算与实际执行确认瓶颈是缺失索引、行数估算偏差还是过滤条件不足。值得说明的是返回执行计划这一能力本身就自带两种用法不带ANALYZE时只做计划不执行零副作用适合生产环境轻量排查加上optionsanalyze时则会真实执行查询并输出实际耗时、行数与缓冲区统计。测试 PlanSpec.hs 验证了普通计划请求会返回大于 0 的Total Cost而buffers选项会返回Shared Hit Blocks等缓冲区信息可作为核对输出内容是否正确的参考。常见问题与注意事项拿不到Query Identifier字段请确认请求头中确实带有optionsverbose并且服务端已开启db-plan-enabled true。未开启时 PostgREST 会直接拒绝 PLAN 类型的请求。pg_stat_statements查询为空确认 PostgreSQL 版本 ≥ 14且shared_preload_libraries中已加载pg_stat_statements修改该参数需要重启数据库实例另外请确认查询确实执行过calls 0或者统计信息刚被重置。queryid为 NULL 的条目PostgreSQL 14 对部分语句如事务控制、PREPARE 等可能不分配queryid但 PostgREST 生成的普通 SELECT/INSERT/UPDATE/DELETE 均在可统计范围内。数值为负数Query Identifier是带符号 64 位整数负数属正常现象直接原样用于WHERE queryid ...即可。与真实请求 SQL 的对应关系pg_stat_statements中的记录按归一化 SQL 聚合PostgREST 为不同 URL 生成的 SQL 只要文本模板一致就会归入同一条记录。如需精确到单次请求应结合calls与时间窗口判断或搭配 PostgREST 的请求日志一起分析。小结PostgREST 把URL → SQL的转换封装在内部而 PLAN 媒体类型Accept: application/vnd.pgrst.planjson; optionsverbose恰好暴露了这条转换链路的产物通过响应中的Query Identifier与pg_stat_statements的queryid建立一一对应关系即可把 HTTP 层的一次慢请求精确映射到 PostgreSQL 层的真实执行统计调用次数、总耗时、平均耗时、返回行数、归一化 SQL从而为索引优化、查询重写和参数调优提供可靠依据。【免费下载链接】postgrestREST API for any Postgres database项目地址: https://gitcode.com/GitHub_Trending/po/postgrest创作声明：本文部分内容由AI辅助生成（AIGC），仅供参考 PostgREST 性能调试实战：借助 pg_stat_statements 定位慢查询
PostgREST 性能调试实战借助 pg_stat_statements 定位慢查询【免费下载链接】postgrestREST API for any Postgres database项目地址: https://gitcode.com/GitHub_Trending/po/postgrestPostgREST 将 HTTP 请求编译为动态 SQL 再交给 PostgreSQL 执行因此传统的应用侧慢查询定位手段并不完全适用。本文介绍如何通过 PostgREST 内置的 PLAN 请求Accept: application/vnd.pgrst.plan拿到某次请求对应的查询标识符Query Identifier再回到pg_stat_statements中关联该标识符获取调用次数、执行耗时、返回行数等真实运行时统计从而把某个 URL 请求慢精确对应到某条 PostgreSQL 查询慢。读完本文你将掌握一套从 PostgREST 请求到 PostgreSQL 查询级的完整性能排查链路。前置条件在开始之前请确认以下两个前提同时满足否则下面的步骤无法执行PostgREST 侧必须开启db-plan-enabled配置项。它默认关闭False其作用是允许通过Accept: application/vnd.pgrst.plan请求头获取某次请求的执行计划。开启方式有三种配置文件在 PostgREST 配置文件中写入db-plan-enabled true默认配置模板中该项以注释形式存在见 Config.hs环境变量PGRST_DB_PLAN_ENABLEDtrue数据库内配置pgrst.db_plan_enabled仅当db-config开启时生效。该配置项为Boolean 类型、可热重载Reloadable: Y完整参数说明见 configuration.rst。PostgreSQL 侧版本需为 14 或更高且已安装并启用pg_stat_statements扩展。pg_stat_statements的queryid字段从 PostgreSQL 14 开始稳定可用本文的关联查询依赖它。注意db-plan-enabled只是开关真正生成计划的能力在源码中始终存在。PostgREST 内部通过EXPLAIN (FORMAT JSON ...)包装实际查询来生成计划其中FORMAT与各选项由请求中的媒体类型参数决定见 SqlFragment.hs 的explainF实现。第一步从 PostgREST 获取查询标识符开启配置并重启或热重载PostgREST 后对任意 GET 请求追加 PLAN 媒体类型即可让 PostgREST 返回该请求对应查询的执行计划而不是真实数据。请求时在Accept头中指定application/vnd.pgrst.planjson并附加optionsverbose选项curl http://localhost:3000/projects?selectid,nameorderid \ -H Accept: application/vnd.pgrst.planjson; optionsverbose这里application/vnd.pgrst.planjson表示以 JSON 格式返回计划optionsverbose对应 PostgreSQLEXPLAIN的VERBOSE选项它会让计划中包含顶层Query Identifier字段。响应大致如下[ { Plan: { Node Type: Aggregate }, Query Identifier: -432192689578025496 } ]响应中的Query Identifier字段其数值与 PostgreSQL 14 中pg_stat_statements.queryid的记录值一致这正是两套系统之间的关联键。关于options参数源码中explainF支持的选项映射如下见 SqlFragment.hsoptions 值对应 EXPLAIN 选项说明verboseVERBOSE输出更详细计划信息包含Query IdentifieranalyzeANALYZE实际执行查询并统计真实耗时与行数settingsSETTINGS输出影响该查询的参数设置buffersBUFFERS输出缓冲区命中/读取信息依赖ANALYZEwalWAL输出 WAL 写入统计依赖ANALYZE多个选项用|分隔例如optionsanalyze|verbose|settings|buffers|wal。媒体类型解析器对上述取值均有对应实现参见 MediaType.hs 中的 doctest 示例。仓库的端到端测试 PlanSpec.hs 也验证了buffers、settings等选项能正确返回对应内容。此外PostgREST 还支持application/vnd.pgrst.plantext返回文本格式的计划以及不带json/text后缀的application/vnd.pgrst.plan默认按文本处理解析逻辑见 MediaType.hs。计划功能对读取、写入POST/PATCH/DELETE与 RPC 调用同样适用——从源码结构看凡是进入dbActionPlan的动作只要媒体类型为MTVndPlan都会被标记为需要执行 EXPLAIN见 Plan.hs。第二步在 pg_stat_statements 中查询运行时统计拿到Query Identifier后直接用它过滤pg_stat_statements视图即可。继续使用上一步得到的-432192689578025496select calls, total_exec_time, mean_exec_time, rows, query from pg_stat_statements where queryid -432192689578025496;查询结果示例各列含义见下文说明callstotal_exec_timemean_exec_timerowsquery130.63558500000000010.0488911538461538513WITH pgrst_source AS (...)注意query列中记录的是 PostgreSQL 归一化normalized后的 SQL 文本以WITH pgrst_source AS (...)开头——这是 PostgREST 生成 SQL 时使用的统一源 CTE 名称sourceCTEName见 SqlFragment.hs 与 Plan.hs 中对根节点newFrom的改写逻辑多个不同的 HTTP 请求只要生成的 SQL 结构相同就会共享同一条pg_stat_statements记录。各列字段的业务含义calls该查询含相同归一化文本的所有实例被执行的总次数用于判断慢是偶发还是持续高频total_exec_time累计执行总耗时毫秒是衡量该查询总体开销的核心指标mean_exec_time平均单次执行耗时毫秒可据此评估单次请求的平均延迟贡献rows该查询实际返回的行数用于与业务预期对比判断是否因缺少过滤条件而返回了过多数据queryPostgreSQL 记录的归一化 SQL 原文可直接阅读 PostgREST 究竟为某个 URL 生成了怎样的 SQL。若需进一步定位还可以在pg_stat_statements中继续取max_exec_time、min_exec_time、stddev_exec_time等列观察执行时间波动或者结合pg_stat_statements_info检查统计重置时间判断数据是否因pg_stat_statements_reset()被清空。第三步完整排查流程示例将两步串起来一套可复用的排查流程如下对疑似慢的接口发起一次 PLAN 请求拿到Query Identifiercurl http://localhost:3000/projects?selectid,nameorderid \ -H Accept: application/vnd.pgrst.planjson; optionsverbose用Query Identifier在数据库中查询该查询的历史统计select calls, total_exec_time, mean_exec_time, rows from pg_stat_statements where queryid -432192689578025496;结合query列中的归一化 SQL 与EXPLAIN ANALYZE或再次使用optionsanalyze的 PLAN 请求对比计划估算与实际执行确认瓶颈是缺失索引、行数估算偏差还是过滤条件不足。值得说明的是返回执行计划这一能力本身就自带两种用法不带ANALYZE时只做计划不执行零副作用适合生产环境轻量排查加上optionsanalyze时则会真实执行查询并输出实际耗时、行数与缓冲区统计。测试 PlanSpec.hs 验证了普通计划请求会返回大于 0 的Total Cost而buffers选项会返回Shared Hit Blocks等缓冲区信息可作为核对输出内容是否正确的参考。常见问题与注意事项拿不到Query Identifier字段请确认请求头中确实带有optionsverbose并且服务端已开启db-plan-enabled true。未开启时 PostgREST 会直接拒绝 PLAN 类型的请求。pg_stat_statements查询为空确认 PostgreSQL 版本 ≥ 14且shared_preload_libraries中已加载pg_stat_statements修改该参数需要重启数据库实例另外请确认查询确实执行过calls 0或者统计信息刚被重置。queryid为 NULL 的条目PostgreSQL 14 对部分语句如事务控制、PREPARE 等可能不分配queryid但 PostgREST 生成的普通 SELECT/INSERT/UPDATE/DELETE 均在可统计范围内。数值为负数Query Identifier是带符号 64 位整数负数属正常现象直接原样用于WHERE queryid ...即可。与真实请求 SQL 的对应关系pg_stat_statements中的记录按归一化 SQL 聚合PostgREST 为不同 URL 生成的 SQL 只要文本模板一致就会归入同一条记录。如需精确到单次请求应结合calls与时间窗口判断或搭配 PostgREST 的请求日志一起分析。小结PostgREST 把URL → SQL的转换封装在内部而 PLAN 媒体类型Accept: application/vnd.pgrst.planjson; optionsverbose恰好暴露了这条转换链路的产物通过响应中的Query Identifier与pg_stat_statements的queryid建立一一对应关系即可把 HTTP 层的一次慢请求精确映射到 PostgreSQL 层的真实执行统计调用次数、总耗时、平均耗时、返回行数、归一化 SQL从而为索引优化、查询重写和参数调优提供可靠依据。【免费下载链接】postgrestREST API for any Postgres database项目地址: https://gitcode.com/GitHub_Trending/po/postgrest创作声明：本文部分内容由AI辅助生成（AIGC），仅供参考