performance - 为什么PostgreSQL查询性能随时间而下降,但在重新生成索引时还原

  显示原文与译文双语对照的内容
123 2

根据这个页的手册 indexes don't need to be maintained 但是,我们运行的是一个 sql table,它有一个连续的updatesdeletesinserts,随着时间的推移,我们。 如果我们 delete 并重新创建索引,查询性能将恢复。

我们正在使用盒子的设置。
我们测试中的table 目前开始空,增长到half行。 它有相当大的行( 。大量文本字段) 。

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

table 被用作单个进程的持久性存储。 使用Java客户端在 Windows 上使用 PostgresQL 。

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

我们正在考虑rearchitecting应用程序,这样数据可以通过不同的动态表传播,而不会影响应用程序。 然而,如往常一样,我们有时间来解决这个问题,我怀疑我们在配置或者使用中丢失了基本。

我们已经考虑了 forcing vacuumingrebuild to run at certain times 但是我怀疑 locking period for such an action would cause our query to block 这可能是一个选项,但存在一些实时的( 3 -5秒的Windows ) 含义,需要在代码中进行其他更改。

额外信息: table 和索引


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 这些选项中的某些可能对我们可用。

我在这个问题中关注的焦点是 understanding understanding how PostgresQL is managing the index and query over time (understand why, not just fix) 如果要完成或者重新构造,将会有很多变化。

时间: 原作者:

138 0

如果你为你想要的性能配置了自动吸尘器,它应该。

注:真空完整:这将重建表统计信息并回收磁盘空间的。 它锁住了整个桌子。

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

分析:这将重建查询计划程序统计信息。 这是由真空触发的,但可以在它自己的。

在这里找到更多的细节注释。

原作者:
90 3

对于性能而言,使用字符串来存储时间和状态信息是一个瓶颈。 首先,文本的索引非常低效,同时比较两次需要至少 11个比较( 用你用的格式),可以简单地比较。 这也影响索引的大小,大索引很难搜索,数据库将不会保存在内存中。 同样的注意事项应用于状态列。 如果表示一小组状态,应使用映射到状态的整数数字,这将减少索引的节点和索引大小。 这里外,如果在查询中没有指定实际的时间,即使使用theese构建的也是无用的。

102 3

这种气味对我来说是指数膨胀。 我把你带到这一页

http://www.postgresql.org/docs/8.3/static/routine-reindex.html

下面写着:

另外,对于b 树索引,一个新构建的索引比多次更新的索引要快得多。 ( 这里考虑目前不适用于non-B-tree索引。) 可以能值得定期重新搜索,以提高访问速度。

它与你引用的页面冲突,说明索引"不需要维护或者调整"。

你尝试过"同时创建索引"?

原作者:
63 3

是'2010-05-20 t13: 00: 00.000'值,xmlscheduledtime将它的比较为。SQL的一部分或者作为参数提供的值?

当规划如何运行查询时,字段必须小于提供的参数,但未知值不能使PostgreSQL继续运行。 它不知道这是否匹配几乎所有行,或者几乎不匹配任何行。

当想知道为什么数据库正在使用计划的原因时,阅读如何使用统计数据有助于。

如果你在complex索引中更改字段顺序或者创建一个新索引,则可以更好地选择性能。索引将显示你感兴趣的字段的索引,而索引扫描将全部排在你所希望的位置。

原作者:
...