在 PostgreSQL 数据库表上执行“DELETE”操作时,我遇到了严重的性能问题。删除 15488 条记录的执行时间为 79423.768 毫秒,与“INSERT”或“SELECT”等其他操作相比非常慢。对于为什么会发生这种情况以及优化删除操作的可能方法,我将不胜感激。
背景:我使用 PostgreSQL 引擎版本 12.14 作为应用程序的后端,并且我注意到从一个表中删除记录需要花费出乎意料的长时间。涉及的表定义了索引和约束,数据库大小相对较小,预计会增长到几GB。然而,对于这个特定的表,这个问题似乎更加明显,而其他表则表现良好。
硬件是 AWS db.t2.micro 实例,具有 1 个 CPU 核心、1 (GiB) 内存和 20 (GiB) 通用 SSD 用于存储。
column_name_loading
表架构,我们尝试从中删除的表。
列名称 | 数据类型 | 描述 |
---|---|---|
ID | 文本 | 首要的关键 |
散列 | 文本 | 首要的关键 |
日期_从 | 时间戳 | 首要的关键 |
日期到 | 时间戳 | |
测量位置uuid | 通用唯一标识符 | 主键、外键 |
列名 | 文本 | 不为空 |
统计类型id | 文本 | |
被忽略 | 布尔值 | |
笔记 | 文本 | |
更新时间 | 时间戳 | |
更新者 | 通用唯一标识符 |
可以看到,上表有一个复合主键,涉及4列。有两个表具有对该column_name_loading
表的外键引用
第一桌
ALTER TABLE
logger_main_config_column_name_loading
ADD
CONSTRAINT column_name_loading_fkey FOREIGN KEY (
column_name_loading_measurement_location_uuid,
column_name_loading_id,
column_name_loading_hash,
column_name_loading_date_from
) REFERENCES column_name_loading(measurement_location_uuid, id, hash, date_from);
第二张桌子
ALTER TABLE
logger_measurement_config_column_name_loading
ADD
CONSTRAINT column_name_loading_fkey FOREIGN KEY (
column_name_loading_measurement_location_uuid,
column_name_loading_id,
column_name_loading_hash,
column_name_loading_date_from
) REFERENCES column_name_loading(measurement_location_uuid, id, hash, date_from);
上表中的measurement_location_location_uuid 外键引用column_name_loading 引用的同一个表。
删除查询
DELETE FROM column_name_loading WHERE measurement_location_uuid='7f925e5c-3d34-417e-8782-052a69692b2b'
Postgres 查询分析
"Delete on column_name_loading (cost=0.00..1232.60 rows=15476 width=6) (actual time=44.797..44.801 rows=0 loops=1)"
" Buffers: shared hit=31799 dirtied=462"
" -> Seq Scan on column_name_loading (cost=0.00..1232.60 rows=15476 width=6) (actual time=0.016..16.843 rows=15488 loops=1)"
" Filter: (measurement_location_uuid = 'ed67b48b-c48a-4727-87cd-5a5f4d27fa7a'::uuid)"
" Rows Removed by Filter: 17280"
" Buffers: shared hit=823"
"Planning Time: 0.103 ms"
"Trigger for constraint column_name_loading_fkey: time=39562.957 calls=15488"
"Trigger for constraint column_name_loading_fkey: time=39759.667 calls=15488"
"Execution Time: 79423.768 ms"
column_name_loading 表将来可以有几百万条记录,也可以有通过外键引用它的表。我们希望至少能够在几分钟内完成选择和删除操作。
我们尝试过的事情
- 放弃外键上的级联删除
measurement_location_uuid
,我们没有性能改进 - 删除引用该表的两个外键上的级联删除
column_name_loading
,我们没有任何性能改进 - 在外键上创建索引
measurement_location_uuid
,显示出一些改进,但删除这么多记录仍然会导致超时。
我们还参考了这个问题以获得一些见解,并尝试了索引和删除级联删除。 PostgreSQL 中的 DELETE 非常慢,有解决方法吗?
我真的很感激以下方面的帮助:
- 为什么 PostgreSQL 数据库中某些表的“DELETE”操作比其他操作明显慢?
- 数据库架构或配置中是否有任何因素可能导致性能下降?
- 我可以实施哪些策略或优化来提高这些表上“删除”操作的效率和速度?
请注意,我已经检查了查询执行计划,并且似乎没有任何明显的瓶颈或长时间运行的查询导致延迟。此外,我还确保所有相关索引都得到正确维护并保持最新。
如果您能提供有关如何排除和优化 PostgreSQL 数据库表上的“DELETE”操作的任何指导或建议,我将不胜感激。谢谢你!
编辑:
在尝试下面答案中提到的索引时,从column_name_loading查询中删除有一些改进。以下是结果:
"Delete on column_name_loading (cost=176.99..1313.87 rows=0 width=0) (actual time=276.497..276.498 rows=0 loops=1)"
" -> Bitmap Heap Scan on column_name_loading (cost=176.99..1313.87 rows=13510 width=6) (actual time=0.596..100.690 rows=12800 loops=1)"
" Recheck Cond: (measurement_location_uuid = '68dd4fae-c2bf-413d-ba3c-cb63b062307f'::uuid)"
" Heap Blocks: exact=334"
" -> Bitmap Index Scan on idx_column_name_loading_measurement_location_uuid (cost=0.00..173.61 rows=13510 width=0) (actual time=0.280..0.280 rows=12800 loops=1)"
" Index Cond: (measurement_location_uuid = '68dd4fae-c2bf-413d-ba3c-cb63b062307f'::uuid)"
"Planning Time: 0.066 ms"
"Trigger for constraint column_name_loading_fkey: time=256.829 calls=12800"
"Trigger for constraint column_name_loading_fkey: time=243.210 calls=12800"
"Execution Time: 778.337 ms"
尝试在 column_name_loading (102,528) 和 logger_measurement_config_column_name_loading 表中添加更多记录:
"Delete on column_name_loading (cost=317.62..3212.88 rows=0 width=0) (actual time=260.420..260.421 rows=0 loops=1)"
" -> Bitmap Heap Scan on column_name_loading (cost=317.62..3212.88 rows=25461 width=6) (actual time=2.778..11.249 rows=25600 loops=1)"
" Recheck Cond: (measurement_location_uuid = 'd6f9d978-20e0-49a1-a6fc-cad0865500d9'::uuid)"
" Heap Blocks: exact=657"
" -> Bitmap Index Scan on idx_column_name_loading_measurement_location_uuid (cost=0.00..311.25 rows=25461 width=0) (actual time=2.663..2.664 rows=25600 loops=1)"
" Index Cond: (measurement_location_uuid = 'd6f9d978-20e0-49a1-a6fc-cad0865500d9'::uuid)"
"Planning Time: 7.639 ms"
"Trigger for constraint column_name_loading_fkey: time=881.762 calls=25600"
"Trigger for constraint column_name_loading_fkey: time=659.424 calls=25600"
"Execution Time: 1806.113 ms"
"Delete on column_name_loading (cost=474.61..5140.76 rows=0 width=0) (actual time=622.069..622.071 rows=0 loops=1)"
" -> Bitmap Heap Scan on column_name_loading (cost=474.61..5140.76 rows=38492 width=6) (actual time=66.150..89.596 rows=38400 loops=1)"
" Recheck Cond: (measurement_location_uuid = '4736f9df-3d53-4896-bc72-e48a118bbfab'::uuid)"
" Heap Blocks: exact=1004"
" -> Bitmap Index Scan on idx_column_name_loading_measurement_location_uuid (cost=0.00..464.99 rows=38492 width=0) (actual time=65.912..65.913 rows=38400 loops=1)"
" Index Cond: (measurement_location_uuid = '4736f9df-3d53-4896-bc72-e48a118bbfab'::uuid)"
"Planning Time: 54.825 ms"
"Trigger for constraint column_name_loading_fkey: time=1118.807 calls=38400"
"Trigger for constraint column_name_loading_fkey: time=805.790 calls=38400"
"Execution Time: 2553.990 ms"
"Delete on column_name_loading (cost=815.01..9706.90 rows=0 width=0) (actual time=693.918..693.919 rows=0 loops=1)"
" -> Bitmap Heap Scan on column_name_loading (cost=815.01..9706.90 rows=67431 width=6) (actual time=10.708..212.115 rows=64000 loops=1)"
" Recheck Cond: (measurement_location_uuid = 'f902ce5b-baf8-4260-bad7-83df2a283a0f'::uuid)"
" Heap Blocks: exact=1614"
" -> Bitmap Index Scan on idx_column_name_loading_measurement_location_uuid (cost=0.00..798.15 rows=67431 width=0) (actual time=10.024..10.024 rows=64000 loops=1)"
" Index Cond: (measurement_location_uuid = 'f902ce5b-baf8-4260-bad7-83df2a283a0f'::uuid)"
"Planning Time: 7.719 ms"
"Trigger for constraint column_name_loading_fkey: time=1727.786 calls=64000"
"Trigger for constraint column_name_loading_fkey: time=1275.343 calls=64000"
"Execution Time: 3707.070 ms"
如果删除级联删除并将索引添加到整个外键上,则查询运行得更快。然而,从 100,000 条记录中删除几条记录仍然需要大约 2 秒的时间,并且只会随着记录数量的增加而增加。大约 4 秒即可从约 40 万条记录中删除几千条记录。这个速度正常吗?该表最多可以有 9-1000 万条记录。现在想象一下从该表中删除几千条记录需要花费多少时间。
正如您的执行计划中明确指出的,实际 DELETE 仅花费 44.801 毫秒。其余时间用于检查两个触发器是否存在
column_name_loading_fkey
其他两个表中的约束。该检查重复 15488 次,主表中每一行删除一次。删除
ON DELETE CASCADE
条件不会阻止检查,因为如果您不指定任何内容,则默认值为ON DELETE NO ACTION
,这意味着仍将检查约束,并且如果找到任何子记录,则会引发错误。您可以:
logger_measurement_config_column_name_loading
完全删除和中的外键约束logger_main_config_column_name_loading
column_name_loading_id, column_name_loading_measurement_location_uuid, column_name_loading_hash, column_name_loading_date_from
将索引添加到引用表后进行编辑:
添加索引使查询速度提高了 100 倍,对于 12,800 行,查询速度从 79 秒缩短到不到一秒。
当您增加要删除的记录数(在上一个示例中增加到 64.000 条)时,时间将增加到 3.7 秒。但是,时间增加是由于删除的行数,而不是表中的总行数。
80%的时间仍然花在检查两个链接表中的外键约束上。现在,此检查是通过索引查找完成的,索引查找与 O(log N) 成正比,这意味着即使表中总行数增加 100 倍,执行单次检查的时间也不应增加太多。
但是,仍然需要对主表中删除的每一行进行检查,因此删除 64000 行将比删除 32000 行需要两倍的检查(大约是两倍的时间)。
如果您的删除全部带有
WHERE uuid = somevalue
子句,则所有索引(主表主键和链接表外键)都应uuid
作为索引的第一列。这样,具有相同 uuid 的所有行将位于表和索引的相同页面中,从而减少磁盘 I/O 并优化内存缓存。您还可以通过将这些索引定义为 CLUSTERED,在用于外键的索引上对表进行聚集。
除此之外,我能想到的进一步加快删除查询的唯一方法是执行这样的序列,从链接表中删除,然后禁用外键检查,从邮件表中删除并重新启用检查:
请注意,如果您定义了除外键约束之外的其他约束,这将暂时禁用这些表上的所有触发器。
仅当您可以编写 WHERE 条件以仅根据外键的列删除链接表的相应行时,它才有效,例如示例中的 uuid。