性能优化- Postgres

Question

我的任务是改进一个运行缓慢的进程的性能，该进程更新Solaris8.3数据库中的一些数据(在PostGres上运行，更新是由Perl5.8脚本通过SOAP驱动的)。大约50%的时间我几乎无法控制，所以调整我的50%是非常重要的。

表中通常有大约4,500,000行，尽管我已经看到它膨胀到大约7,000,000行。更新要查询的id (不是主要的或唯一的)有9000个不同的值，并且每个id的出现次数分布很接近1(中值为20，最大值为7000)。

在这个id上有一个索引，但由于数据如此稀疏，我想知道是否有更好的方法来做事情。我还在考虑稍微去规格化一些东西(数据库无论如何都不是超规格化的)&把数据拉到一个单独的表中(可能是由触发器控制/维护的)，以帮助提高速度。

到目前为止，我已经做了一些非常基本的调整(不是每隔n秒with一次数据库以查看它是否还活着，不设置不必要的会话变量等)，这是有帮助的，但我真的觉得我在数据中遗漏了一些东西……

即使有人说把相关数据放到一个单独的表中是一个很好/很糟糕的想法，但这真的会很有帮助！任何其他想法(或更多需要澄清的问题)非常感谢！

查询：

UPDATE tab1 SET client = 'abcd', invoice = 999 
    WHERE id = 'A1000062' and releasetime < '02-11-09'::DATE 
    AND charge IS NOT NULL AND invoice IS NULL AND client IS NULL;

我意识到'is not null‘远不是理想的。Id就像发票和客户一样被编入索引(btree，所以我知道PostGres将/应该/可以在那里使用索引)。这是一个相当微不足道的查询。

查询计划(explain with analyze)：

Bitmap Heap Scan on tab1 (cost=17.42..1760.21 rows=133 width=670) (actual time=0.603..0.603 rows=0 loops=1)
  Recheck Cond: (((id)::text = 'A1000062'::text) AND (invoice IS NULL))
  Filter: ((charge IS NOT NULL) AND (client IS NULL) AND (releasetime < '2009-11-02'::date))
  ->  Bitmap Index Scan on cdr_snapshot_2007_09_12_snbs_invoice  (cost=0.00..17.39 rows=450 width=0) (actual time=0.089..0.089 rows=63 loops=1)
    Index Cond: (((snbs)::text = 'A1000062'::text) AND (invoice IS NULL))
Total runtime: 0.674 ms

我相信，自动吸尘器是启用的。没有外键约束，但谢谢你的提示，因为我不知道这一点。

我真的很喜欢增加统计值的想法--我马上就会尝试一下。

Answer 1

您确实需要获取一些查询计划，并编辑您的问题以包括它们。除了帮助找出做事情的更好的方法外，它们还可以用来轻松地测量改进。

您可以通过更改SQL或调整用于确定查询计划的索引和统计信息来影响性能。

一种可能是您的外键约束没有支持索引。创建外键约束时，PostgreSQL不会自动添加它们。如果引用的表中有一行已删除(或引用字段已更新)，则需要完全扫描引用表，以级联删除，或确保没有引用已删除行的行。

如果id字段的分布非常不均匀，增加该列的统计信息可能会有所帮助。

如果统计数据设置为100，那么将记录100个最常见的ids (来自样本)以及它们的频率。假设覆盖了大约50%的表，剩下200到350万行，PostgreSQL假设这些行平均分布在其他8900个ids中，或者说每个ids大约250到400次。

如果统计数据增加到1000个，并且前1000个ids覆盖了95%的行，PostgreSQL将假定不在1000个最常见的列表中的ids每个都会出现大约30到40次。

估计值的更改可能会影响所选的查询计划。如果查询模式更多地选择较少被发现的If，则PostgreSQL将高估这些If被发现的次数。

存储这么多最频繁的值是有性能代价的，因此您确实需要支持查询计划分析，以确定您是否获得了净收益。