On Tuesday 15 April 2003 15:14, you wrote:
> The postgreSQL database we used need to process several millions records.
> There are only six tables in the database. one of them contains several
> million records, the Others are less smaller. We need select more than 100
> thousands records from the talbe which contains several million records in
> 10 seconds. In the process of selecting, the speed of selecting is not
> stable. Sometimes it cost 2 minutes , but sometimes 20 seconds. After
> analyzing the time wasting in the process, we found the speed of function
> Count(*) is very slow. At the same time we have finished the setup of some
> parameters like max_fsm_relation, max_fsm_pages, share memory size etc, but
> the performance is not improved satisfied.
Why do you need to do select count(*) to select more than 100 thousand
records?
Postgresql being MVCC database, select count(*) is not going to be anywhere
near good, especially if you have transactions occuring on table.
As far as just selecting rows from table, that should be tad fast if there are
proper indexes, table in analyzed every now and then and there are enough
shared buffers.
If you post your queries and table schemas, that would be much helpful. Your
tweaked settings in postgresql.conf and hardware spec. would be good as well.
> Under this condition, I want get some useful suggestion from you. How to
> optimize the database? How to improve the Count(*)? Because we want to
> get the number of records in the recordset we got.
If you are using say libpq, you don't need to issue a select count(*) where
foo and select where foo, to obtain record count and the records themselves.
I believe every other interface stemming from libpq should provide any such
hooks as well. Never used any other myself (barring ecpg)
HTH
Shridhar