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

概述根据手册中的这个 page,索引不需要维护.但是,我们正在运行PostgresQL表,该表具有连续的更新,删除和插入速度,随着时间的推移(几天)会出现重大的查询降级.如果我们删除并重新创建索引,则会恢复查询性能. 我们正在使用开箱即用的设置. 我们测试中的表目前开始是空的,增长到50万行. 它有一个相当大的行(很多文本字段). 我们正在搜索一个索引,而不是主键(我已经确认使用的索引,至少在正常条件 根据手册中的这个 page,索引不需要维护.但是,我们正在运行Postgresql表,该表具有连续的更新,删除和插入速度,随着时间的推移(几天)会出现重大的查询降级.如果我们删除并重新创建索引,则会恢复查询性能.

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

我们正在搜索一个索引,而不是主键(我已经确认使用的索引,至少在正常条件下)

该表被用作单个进程的持久存储.
在windows上使用Postgresql与Java客户端.

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

我们正在考虑重新构建应用程序,以便数据以各种动态表格的形式传播,从而允许我们定期删除和重建索引,而不影响应用程序.然而,一如以往,有一个时间紧缩来让这个工作,我怀疑我们在配置或使用中缺少一些基本的东西.

我们考虑过强制抽真空重建,在某些时候运行,但我怀疑这种行动的锁定时间会导致我们的查询被阻止.这可能是一个选项,但是有一些需要在我们的代码中进行其他更改的实时(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如何管理索引和查询(了解为什么,而不仅仅是修复).如果要完成或重大的重构,会有很多变化.

自动真空应该做的诀窍,只要你配置了您所需的性能.

笔记：
VACUUM FulL：这将重建表统计信息并回收磁盘空间的负载.它锁定整个桌子.

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

ANALYZE：这将重建查询计划器统计信息.这是由VACUUM触发,但可以自行运行.

更多detailed notes found here

总结

以上是内存溢出为你收集整理的为什么PostgresQL查询性能随时间而下降,但在重建索引时会被恢复全部内容，希望文章能够帮你解决为什么PostgresQL查询性能随时间而下降,但在重建索引时会被恢复所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。