postgresql postgres 截断很慢
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19936204/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
postgres truncate is slow
提问by toanong
In postgres 9.2 (CentOS), TRUNCATE TABLE command occasionally took a really long time to run. One time, it took more than 1.5 hours to truncate a table with 100K records, even longer in other cases. This problem also happened when I used pgAdmin to truncate table. What is the possible cause? and how to improve the truncation performance?
在 postgres 9.2 (CentOS) 中，TRUNCATE TABLE 命令有时需要很长时间才能运行。有一次，截断一个包含 10 万条记录的表需要 1.5 多个小时，在其他情况下甚至更长。当我使用 pgAdmin 截断表时也发生了这个问题。可能的原因是什么？以及如何提高截断性能？
There is 16GB of memory on the server and shared_buffers = 1536MB
服务器上有 16GB 内存，shared_buffers = 1536MB
回答by Craig Ringer
TRUNCATEhas to flush shared_buffersfor the table being truncated, and it has to unlink the old file, which can be slow on file systems with slow deletion like ext3.
TRUNCATE必须为shared_buffers被截断的表刷新，并且必须取消旧文件的链接，这在具有缓慢删除的文件系统上可能会很慢，例如ext3.
1.5 hours is pretty extreme though, as we're usually talking seconds at most. It is highly likely that you have other sessions holding locks on the table that prevent the TRUNCATEfrom proceeding. See pg_catalog.pg_locksand pg_catalog.pg_stat_activity.
不过 1.5 小时是相当极端的，因为我们通常最多只讨论几秒钟。很可能您有其他会话持有表上的锁，以防止TRUNCATE继续进行。见pg_catalog.pg_locks和pg_catalog.pg_stat_activity。
The PostgreSQL wiki article on lock monitoringshould be useful.
关于锁监控的 PostgreSQL wiki 文章应该很有用。
See also: Postgresql Truncation speed
另请参阅：Postgresql 截断速度
回答by Steven Shi
Check if the truncatewas blocked by any query
检查是否truncate被任何查询阻止
SELECT
activity.pid,
activity.usename,
activity.query,
blocking.pid AS blocking_id,
blocking.query AS blocking_query
FROM pg_stat_activity AS activity
JOIN pg_stat_activity AS blocking ON blocking.pid = ANY(pg_blocking_pids(activity.pid));
If necessary terminate it
SELECT pg_terminate_backend(PID);
如有必要终止它
SELECT pg_terminate_backend(PID);
回答by jturi
Try to disconnect all other connections to the database. It took me forever to truncate 58000 records.
尝试断开与数据库的所有其他连接。我花了很长时间才截断 58000 条记录。
After I disconnected my postgres database from PyCharm DB Navigator, dev server etc took 118 msec total.
在我从 PyCharm DB Navigator 断开我的 postgres 数据库之后，开发服务器等总共花费了 118 毫秒。
