PostgreSQL - 为什么对大型数据集的一些查询如此缓慢

Question

我经常在两个大型数据集上运行两种类型的查询。它们的运行速度比我预期的要慢得多。

第一种类型是顺序扫描更新所有记录:

Update rcra_sites Set street = regexp_replace(street,'/','','i')

rcra_sites 有 700,000 条记录。从 pgAdmin 需要 22 分钟!我编写了一个 vb.net 函数，循环遍历每条记录并为每条记录发送一个更新查询(是的，700,000 个更新查询!)，它运行时间不到一半。嗯....

第二种是简单的关系更新，然后是顺序扫描:

Update rcra_sites as sites 
Set violations='No' 
From narcra_monitoring as v 
Where sites.agencyid=v.agencyid and v.found_violation_flag='N'

narcra_monitoring 有 1,700,000 条记录。这需要 8 分钟。查询规划器拒绝使用我的索引。如果我从 set enable_seqscan = false; 开始，查询运行得更快。我希望查询计划器能够完成它的工作。

我有适当的索引，我已经清理和分析了。我优化了我的 shared_buffers 和 effective_cache_size 我知道使用更多内存因为我有 4GB。我的硬件非常好。我在 Windows 7 上运行 v8.4。

PostgreSQL 就这么慢吗？还是我还遗漏了什么？

Answer 1

与 seq_page_cost 相比，可能尝试减少 random_page_cost(默认值:4):这将通过使索引驱动的随机访问更具吸引力来减少规划器对 seq 扫描的偏好。

另一件要记住的事情是，MVCC 意味着更新一行的成本相当高。特别是，更新表中的每一行需要将表的存储量加倍，直到它可以被清空。因此，在您的第一个查询中，您可能希望限定您的更新:

UPDATE rcra_sites Set street = regexp_replace(street,'/','','i')
                  where street ~ '/'

(afaik postgresql 不会自动抑制更新，如果它看起来像你实际上没有更新任何东西。Istr 在 8.4(？)中添加了一个标准的触发函数来允许你这样做，但也许更好在客户端解决)