关于PostgreSQL的性能问题

ryuken2008

最近帮朋友做科学计算，用PostgreSQL建了一个非常简单的库，其中有一个表，定义如下：
create table test (
num int4 not null default 1,
rsj int8[] not null default '{0,0,0,0,0,0,0,0}',
res float8 not null default 0.0,
constraint pkey_test primary key(num, rsj));

这个表的结构虽然简单，但是数据量非常大，目前已经有1千5百万条纪录了。执行一次select count(*) from test;操作要N长时间，即使执行一次select * from test where num=3 and rsj='{0,0,0,0,0,0,0,0}';也要1分钟左右。这样的速度显然已经不能满足要求了。

请各位高人指点一下，如何才能提高PostgreSQL的查询性能，都需要做哪些改动？我用的操作系统是FreeBSD 4.11 Release，硬件：CPU是P4 2.6G，内存1G。谢谢了。

joint

select count(*)确实花时间.

rsj int8[] not null default '{0,0,0,0,0,0,0,0}'
这个数据比较恐怖,不清楚对这列的隐含索引是怎么样的,这列做筛选耗时应该大.

我原来做测试的时候没有这种复杂列,数据超过5000万,建立索引,优化以后性能还可以的,机器比你这个差

ryuken2008

原帖由 "joint" 发表：
ll default '{0,0,0,0,0,0,0,0}'
这个数据比较恐怖,不清楚对这列的隐含索引是怎么样的,这列做筛选耗时应该大.

我原来做测试的时候没有这种复杂列,数据超过5000万,建立索引,优化以后性能还可以的,机器比你这个差

对这列的隐含索引是：primary key, btree (num. rsj)
开始的时候我是建立了8个int8型的字段（r1, r2, r3, r4, r5, r6, r7, r8），并建立了一个索引如下：
create index idx_test on test (num, r1, r2, r3, r4, r5, r6, r7 r
用这种方法在插入数据的时候比使用数组的方法慢很多。如果有什么方法能够提高查询速度，我可以把表改回到8个int8字段的样子。
谢谢。

joint

索引越多越复杂,插入耗时也时正常的,我也没有啥好办法,看模样好像只能在插入和查询中寻求平衡,哪个使用要求更高些

joint

select * from test where num=3 and rsj='{0,0,0,0,0,0,0,0}'

对列 rsj 进行分类, 再加一列区分不同类型的rsj, 对这个列再索引 ?

rsj='{0,0,0,0,0,0,0,0}'全部为零的数据,在插入的时候在内存里判断比查询的时候读硬盘快的多.

或者把复杂表拆分成几个表

ryuken2008

原帖由 "joint" 发表：
select * from test where num=3 and rsj='{0,0,0,0,0,0,0,0}'

对列 rsj 进行分类, 再加一列区分不同类型的rsj, 对这个列再索引 ?

rsj='{0,0,0,0,0,0,0,0}'全部为零的数据,在插入的时候在内存里判断比查询的?.........

我再简要描述一下我的应用。num字段存放一个很小的正数（不会超过25），首先把num=1的数据存入表中（24条），计算num=2时需要根据rsj的值检索num=1的数据，并把计算结果存入表中；计算num=3时需要根据rsj的值检索num=2的数据，并把计算结果存入表中，以此类推。目前我已经算到num=3了，2的情况有6000多条纪录，而3的情况已经有1千5百万条纪录了。在计算num=4之前我发现postgresql的查询效率太低了，而4的纪录数应该在几亿条，按照目前的速度，估计我很难算出来了。

所以检索的速度和插入的速度都很重要。可能我遇到的是一个比较极端的情况。

joint

(num, rsj)  这个组合可能重复吗?

如果不会重复,不要建组合约束,单独建索引,插入效率可能会高一些.查询还不敢说

还可以优化一下内存使用,性能应该会好有些

ryuken2008

原帖由 "joint" 发表：
(num, rsj)  这个组合可能重复吗?

如果不会重复,不要建组合约束,单独建索引,插入效率可能会高一些.查询还不敢说

还可以优化一下内存使用,性能应该会好有些

(num, rsj)这个组合本身可以保证unique，但是num和rsj都会有重复的可能。即对于每个不同的num，rsj不会重复。

crykun

老贴，小弟如今遇到了同样的难题。不知Enterprise db的解决方案怎么样了，pgsql 最新版，新服务器，又有多大性能提升呢。

renxiao2003

看看索引什么的？