performance - 为什么 PostgresQL 查询性能随时间下降，但在重建索引时恢复

Question

根据手册中的 page，不需要维护索引。但是，我们正在运行一个 PostgresQL 表，该表具有连续的 updates、deletes 和 inserts 随着时间的推移(几天)看到显着的查询降级。如果我们删除并重新创建索引，查询性能就会恢复。

我们使用开箱即用的设置。
我们测试中的表目前开始是空的，并增长到 50 万行。它有相当大的一行(很多文本字段)。

我们基于索引而不是主键进行搜索(我已经确认正在使用索引，至少在正常情况下是这样)

该表被用作单个进程的持久存储。在带有 Java 客户端的 Windows 上使用 PostgresQL。

我愿意放弃插入和更新性能以保持查询性能。

我们正在考虑重新构建应用程序，以便数据以一种允许我们定期删除和重建索引而不影响应用程序的方式分布在各种动态表中。然而，一如既往，要让它发挥作用需要时间，我怀疑我们在配置或使用方面缺少一些基本的东西。

我们已经考虑过强制清理和重建在特定时间运行，但我怀疑此类操作的锁定期会导致我们的查询阻塞。这可能是一个选项，但有一些实时(3-5 秒的窗口)影响需要我们的代码进行其他更改。

附加信息:表格和索引

CREATE TABLE icl_contacts
(
  id bigint NOT NULL,
  campaignfqname character varying(255) NOT NULL,
  currentstate character(16) NOT NULL,
  xmlscheduledtime character(23) NOT NULL,
...
25 or so other fields.  Most of them fixed or varying character fiel  
...
  CONSTRAINT icl_contacts_pkey PRIMARY KEY (id)
)
WITH (OIDS=FALSE);
ALTER TABLE icl_contacts OWNER TO postgres;

CREATE INDEX icl_contacts_idx
  ON icl_contacts
  USING btree
  (xmlscheduledtime, currentstate, campaignfqname);

分析:

Limit  (cost=0.00..3792.10 rows=750 width=32) (actual time=48.922..59.601 rows=750 loops=1)
  ->  Index Scan using icl_contacts_idx on icl_contacts  (cost=0.00..934580.47 rows=184841 width=32) (actual time=48.909..55.961 rows=750 loops=1)
        Index Cond: ((xmlscheduledtime < '2010-05-20T13:00:00.000'::bpchar) AND (currentstate = 'SCHEDULED'::bpchar) AND ((campaignfqname)::text = '.main.ee45692a-6113-43cb-9257-7b6bf65f0c3e'::text))

而且，是的，我知道我们可以做很多事情来规范和改进这个表的设计。我们可能可以使用其中一些选项。

我在这个问题上的重点是了解 PostgresQL 如何随着时间的推移管理索引和查询(了解原因，而不仅仅是修复)。如果要重做或进行重大重构，将会有很多变化。

Answer 1

自动吸尘器应该可以解决问题，前提是您将其配置为您想要的性能。

注意事项:VACUUM FULL:这将重建表统计信息并回收磁盘空间负载。它锁定了整个表。

VACUUM:这将重建表统计信息并回收一些磁盘空间。它可以与生产系统并行运行，但会产生大量影响性能的 IO。

ANALYZE:这将重建查询计划器统计信息。这是由 VACUUM 触发的，但可以自行运行。

更多detailed notes found here