优化PG查询：一问一答

优化PG查询：一问一答

正文

Q1：是否有普罗米修斯exporter，你知道普罗米修斯监控PG的原生选项吗？

可以使用Postgres Exporter采集PG的各种指标，并将其发送给普罗米修斯。更多详细信息参考：

https://medium.com/@shevtsovav/all-databases-on-the-eyes-postgres-exporter-prometheus-grafana-d4c4f749d6aa

https://github.com/prometheus-community/postgres_exporter

Q2：能否监控预定义日期范围内来自某个IP的所有查询？我们需要找出哪个查询正在加载服务器

可以使用pg_stat_activity视图监控来自某个IP的查询：

SELECT query_start,now() AS CURRENT_TIME,query

FROM pg_stat_activity

WHERE client_addr=’your_ip_addr’

ORDER BY now()-query_start DESC;

上面的查询可以帮助我们找出来自某个IP的最长查询。然而这些文本可能不够完整。强烈推荐使用pg_stat_statements、pg_stat_kcache、pg_profile插件获取完整内容。通过这些插件可以在业务应用中找到长查询的指定部分。

Q3：Grafana仪表板上推荐显示哪些参数？是否可以提供一个？

postgres_exporter有很多有意义的指标，例如连接统计：

每秒的事务和查询数：

每个事务的WAL大小：

后台工作进程，例如autovacuum worker

锁统计：

shared_buffers使用率统计

Checkpoint统计：

查询执行的统计：

Q4：可以推荐一个开源的paid工具展示执行计划吗？

可以使用以下开源模块：

auto_explain将最长的查询计划写入日志文件

pg_store_plan采集执行计划和参数

https://explain.depesz.com/ 用于可视化执行计划和发现查询热点

Postgrespro的客户可以使用pgpro_stats模块采集查询计划，但是计划里面没有参数值。

Q5：在我们自己的数据库上有现成的playgroud用于做学习训练吗？

可以使用我们的demo数据库：https://edu.postgrespro.com/demo-big-en.zip

另外有本书可参考：https://edu.postgrespro.ru/introbook_v6_en.pdf

Q6：可以提供一些TCP测试链接吗？

所有TPC测试都是在各种客户审核期间进行，每办法发布。但可以使用JMeter工具构建自己的测试，完成后，可以获得类似内容：

Q7：哪些指标可以告诉我们服务器配置错误？

1) 可以使用前面介绍的checkpoint统计来多个检查点。这个案例中，可以调整max_wal_size和min_wal_size参数。

2) 后台worker进程统计中，展示了autovacuum worker情况，可以通过autovacuum_max_workers调整：

autovacuum_max_workers=NCores/4..2，其中NCores是CPU总核数

autovacuum_vacuum_cost_limit = 200 * (autovacuum_max_workers / 3)

3) shared_buffers使用率中，可以调整shared_buffers配置。

Q8：PG11中查询执行发现计划时间占90%，执行时间仅占10%。查询使用的分区表，此问题是否有其他解决方案，或需要迁移到主版本？

PG12或者高版本，在patition_pruning机制上有很大提升，简化了查询计划的处理以及查询时仅检查很少的分区。因此推荐升级PG版本。

Q9：EXISTS谓语和IN运算符在性能方面有什么区别？

在编写查询时，可以假设EXISTS将提供更好的结果，因为它可以使用所有逻辑和优化来连接两个表，而IN运算符将使用子计划。有趣的时，从PG10开始计划者对于这两个选项可能会产生相同结果。

然而，在考虑NOT EXISTS和NOT IN场景中，NOT IN会产生SubPlans，当处理大型数据集时造成瓶颈。NOT EXISTS子句反而会导致anti join，不会产生SubPlans。

EXISTS子句要求Planner在主连接前评估唯一行数。如果数据集来自CTE物化，则无法使用统计数据进行评估，因此可能导致不合适的执行计划。因此在这种情况下建议谨慎使用。

表列和常量列进行比较时，也可以使用IN运算符。在PG14前，有一种线性搜索，如果使用许多常量，可能会导致性能不佳。从PG14开始，将提供哈希查找。

Q10：如何监控vacuum进程？如何调优？有什么推荐

没有autovacuum的话数据库中将有很多老版本记录，造成表膨胀。例如，pg_profile可以监控某个时间段：

DML操作最多的表.

更新/删除操作最多的表.

增长最快的表.

增长最快的索引.

Vacuum操作最多的表.

analyze 操作最多的表.

死元组率最多的表.

更新元组率最多的表.

vacuum I/O load最多的索引.

1）autovacuum_naptime应该减小到20秒，因为1分钟太多了

2）autovacuum_max_workers通用公式：

autovacuum_max_workers = NCores/4..2，其中NCores为CPU核数

需要确保autovacuum_vacuum_cost_limit 参数也需要调整：

autovacuum_vacuum_cost_limit = 200 * (autovacuum_max_workers / 3)

3）推荐autovacuum_vacuum_scale_factor 为0.02.如果表中死元组率大于2%，那么

autovacuum会自动进行处理。

4）也推荐autovacuum_analyze_scale_factor 为0.05，如果表中更改的元组率大于5%，autovacuum worker会采集统计信息以便planner使用。

5）PG13可以调整autovacuum_vacuum_insert_scale_factor ，处理append-only表，以阻止回卷问题。

6）如果autovacuum_work_mem是-1，会使用maintenance_work_mem 值，作为起始值考虑将其增加到1GB

7）pg_stat_progress_vacuum 视图实时显示vacuum工作

8）使用ALTER TABLE tb_name SET（param_name=param_value）用于对指定表调整autovacuum_vacuum_scale_factor, autovacuum_analyze_scale_factor

9）避免长查询和长事务（包括空闲事务），因为会阻止删除旧元组。这样就会产生大量膨胀表，带来沉重的IO负载

10）Autovacuum worker从索引和对应表中清除死元组。pg_profile报告中“Top indexes by estimated vacuum IO load”可以显示索引如何影响autovacuum进程。在某些情况下，它可能会运行很长时间，因为有许多庞大的索引需要清理。如果是这种情况，考虑将表划分为较小的分区。可以参考：

https://cloud.google.com/solutions/optimizing-monitoring-troubleshooting-vacuum-operations-postgresql.pdf

Q11：是否有pg_stat_kcache的使用文档？

https://powa.readthedocs.io/en/latest/components/stats_extensions/pg_stat_kcache.html

https://github.com/powa-team/pg_stat_kcache

Q12：在列上创建索引后，仍使用顺序扫描，怎么才能绕过？

很大程度上取决于查询。也许，它从收集了75%的行，因此由于大量的随机访问开销，索引扫描没有意义。如果查询需要几个列，考虑创建INCLUDE索引，以index-only扫描使用。

核心原因可能与索引不包括过滤字段这一事实有关。即使这样，这些字段也不可能处于leading位置，因此这样的索引扫描是低效的。

如果查询使用LIKE操作符，确保使用合适的操作符类如text_pattern_ops、varchar_pattern_ops等。

Q13：在读取性能测试期间，检测到数据库中某些写入操作，原因是什么？如何预防？

可能涉及临时文件的生成。当内部后端内存不足，无法对大型数据集进行排序或无法保存CTE的查询结果时，PG开始将数据写入到磁盘的临时文件中。此外，由于不正确的终止语句，可能面临无限递归查询。您可以使用pg_profile部分“Top SQL by temp usage”来监视这些查询，并对其进行调优。

Q14：PG中如何skip scan？是否和Oracle中的skip scan匹配

PG原生不支持index skip scan，但这项工作正在进行中：

https://commitfest.postgresql.org/19/1741/

可以使用递归CTE模拟index skip scan：

https://wiki.postgresql.org/wiki/Loose_indexscan

Q15：有关于如何启用上述扩展的文档吗？

pg_stat_statements和auto_explain模块在标准PG分支中，因此可在官方手册中查看使用方法：

https://www.postgresql.org/docs/13/pgstatstatements.html

https://www.postgresql.org/docs/13/auto-explain.html

pg_stat_kcache模块由PG repositories提供：

https://download.postgresql.org/pub/repos/yum/13/redhat/rhel-8.4-x86_64/pg_stat_kcache13-2.1.3-1.rhel8.x86_64.rpm

http://apt.postgresql.org/pub/repos/apt/pool/main/p/pg-stat-kcache/

pg_wait_sampling模块也由PG repositories提供：

https://download.postgresql.org/pub/repos/yum/13/redhat/rhel-8.4-x86_64/pg_wait_sampling_13-1.1.3-1.rhel8.x86_64.rpm

http://apt.postgresql.org/pub/repos/apt/pool/main/p/pg-wait-sampling/

pg_profile模块可以在github中查看：

https://github.com/zubkov-andrei/pg_profile

原文

https://postgrespro.com/blog/company/5968040