英文:
Debugging PostgreSQL triggers performance
问题
我有一个删除查询很慢。我使用 EXPLAIN ANALYZE 来了解瓶颈，并看到两个触发器很慢：
用于约束 other_table_1_fid_fkey 的触发器：时间=3.644 调用=1
用于约束 other_table_2_fid_fkey 的触发器：时间=6.289 调用=1
根据 这个 讨论，我添加了索引。在 other_table_1 上的 fid 上的索引确实提高了性能。但是在 other_table_2 上为 fid 添加索引没有任何区别，并且似乎是 delete 查询的瓶颈。
我的问题是我如何调试（或 EXPLAIN ANALYZE）触发器本身。
谢谢，
附言：
为了解决我的具体问题，我暂时移除了外键约束，这提高了性能。
英文:
I have a slow delete query. I used EXPLAIN ANALYZE to understand the bottleneck and I saw two triggers that are slow:
Trigger for constraint other_table_1_fid_fkey: time=3.644 calls=1
Trigger for constraint other_table_2_fid_fkey: time=6.289 calls=1
Following this discussion I added indexes.
The index on fid in other_table_1 indeed improved the performance.
But adding an index on fid in other_table_2 didn't make any difference, and it is seems to be the bottleneck in the delete query.
My question is how can I debug (or EXPLAIN ANALYZE) the trigger itself.
Thanks,
P.S
To solve my concrete problem I temporary remove the foreign key constraint and it improved the performance.
答案1
得分: 3
设置以下参数：
shared_preload_libraries = 'auto_explain'
auto_explain.log_min_duration = 0
auto_explain.log_analyze = on
auto_explain.log_buffers = on
auto_explain.log_triggers = on
auto_explain.log_nested_statements = on
然后重新启动 PostgreSQL（以使 shared_preload_libraries 生效），然后再次执行该语句。您将在 PostgreSQL 日志中找到触发器函数中所有 SQL 语句的执行计划。这将帮助您找到并调整慢查询语句。
英文:
Set these parameters:
shared_preload_libraries = 'auto_explain'
auto_explain.log_min_duration = 0
auto_explain.log_analyze = on
auto_explain.log_buffers = on
auto_explain.log_triggers = on
auto_explain.log_nested_statements = on
Then restart PostgreSQL (so that shared_preload_libraries takes effect) and execute the statement again. You will find the execution plans of all SQL statements in the trigger functions in the PostgreSQL log. That will enable you to find and tune slow statements.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论