为什么postgresql查询性能会随时间下降,但在重建索引时会恢复

根据这个 page 在手册中, indexes don't need to be maintained . 但是,我们运行的postgresql表的连续速率为 updates , deletes 和 inserts 随着时间的推移(几天),查询性能会显著下降。如果删除并重新创建索引,查询性能将恢复。

我们正在使用现成的设置。
我们的测试中的表当前开始是空的,并增长到50万行。 它有一个相当大的行(很多文本字段)。

我们是 searching based of an index, not the primary key (我已经确认索引正在使用,至少在正常情况下)

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

我愿意放弃 insert and update performance 以保持查询性能。

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

我们考虑过 forcing vacuuming 和 rebuild to run at certain times ,但我怀疑 locking period for such an action would cause our query to block . 这可能是一个选项,但是有些实时(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))

是的,我知道有很多事情 we could do to normalize and improve the design of this table . 我们可以选择其中的一些。

我在这个问题上的重点是 关于理解 how PostgresQL is managing the index and query over time (understand why, not just fix) . 如果要对其进行彻底的重构或显著的重构,将会有很多更改。