我遇到了表上缺少索引的问题,我收集线索的唯一方法是这样:
BEGIN TRANSACTION;
-- OVER 100 DELETES
COMMIT;
在 DBeaver 中,我仅选择了第一行和后续的删除,并执行了该操作。然后,选择提交行,执行它并等待,直到我觉得这花费了太长时间。
然后,当我取消事务时,DBeaver 给了我这条消息:
SQL Error [57014]: ERROR: canceling statement due to user request
Where: SQL statement "SELECT 1 FROM ONLY "schema"."table" x WHERE $1 OPERATOR(pg_catalog.=) "id_model" AND $2 OPERATOR(pg_catalog.=) "id_property1" AND $3 OPERATOR(pg_catalog.=) "id_property2" FOR KEY SHARE OF x"
id_model
是所有相关 DELETE 的 WHERE 子句。消息中提到的查询绝对不是我编写的代码,位于我能想到的任何函数/过程/触发器中的任何位置。
我从中得出,table
列上缺少索引id_property1
是id_property2
问题所在,显然确实如此。
当服务器正在处理事务中排队的多个语句时,是否有任何日志解释“幕后”发生的情况,我可以更轻松地找到该语句?
模型中配置了几个删除外键约束,所以我猜当索引丢失时,服务器需要一段时间才能找到正确的方法?
无法对 DBeaver 发表评论。它是一个通用客户端,未针对 Postgres 进行优化。我偶尔会在 dba.SE 和 SO 上看到有关摩擦的抱怨。
客户端有责任为每个命令显示消息(
ERROR
、WARNING
、NOTICE
、 ...)。不知道 DBeaver 如何处理这个问题。也许您必须DELETE
单独执行每个消息(仍然保留在同一事务中)才能查看所有消息。您可以调整 Postgres 设置
client_min_messages
并log_min_messages
分别调整发送到客户端和数据库日志的消息级别。但
SELECT
您显示的声明不是“消息”。这是一个简单的 SQL 命令。必须由您或您的客户签发。不常见的显式语法(OPERATOR(pg_catalog.=)
而不仅仅是=
)指示机器生成的代码。也许它是一条更大消息的一部分,显示已取消的查询一直在等待的内容。如果缺少索引导致某个特定语句占据了执行时间,那么在中断执行时就有很好的机会抓住该语句。这仍然是巧合。此外,虽然限定索引可以加快 SQL 命令速度(很多),但“缺失”索引并不是错误条件(或任何消息级别的条件)。您不会收到来自 Postgres 的消息。创建索引由用户自行决定。创建正确的数据库架构师/管理员的主要任务之一。这其中有无数的微妙之处。FK 约束的目标列按定义索引(需要 PK 或 UNIQUE 约束)。FK 约束的源列是普通 B 树索引的主要候选者。或者,正如手册所说:
监控缓慢的查询以获得最紧迫的缺失优化的指示(而非证据)。有多种方法,最重要的是:设置
log_min_duration_statement
或可能log_min_duration_sample
和朋友,然后研究生成的数据库日志。和/或使用EXPLAIN(及其所有功能)来诊断可疑的执行计划。
您看到的奇怪语句是从在 PostgreSQL 中实现外键约束的触发器运行的。
有两种方法可以获取有关这些语句的数据:
如果您设置
shared_preload_libraries = 'pg_stat_statements'
并pg_stat_statements.track = all
重新启动 PostgreSQL,pg_stat_statements也会收集有关这些“隐藏”语句的统计信息。您可以使用pg_stat_statements
同名扩展提供的视图来查看这些数据。如果你设置
shared_preload_libraries = 'auto_explain'
andauto_explain.log_nested_statements = on
并auto_explain.log_min_duration = '500ms'
重新启动 PostgreSQL,你将获得所有运行超过半秒的语句的执行计划,包括这些“隐藏”语句。