Hi!
We are currently running a Community-Site on Postgresql. This community
makes a lot of traffic on the Database. To improve Performance we already
took a lot of actions from the Database into the Java-Cache of our
Application.
Nonetheless we have some very big Tables where we store profiles, messages
and a lot more. I am talking about 50.000 new tuples every day for one
tables.
Now we get very poor results and even difficulties when trying easy selects
on those tables. When using pgAdminII we sometimes even have to wait nearly
a minute until we can edit that table because pgAdminII always does a
"select count(*)" on the table to get the amout of rows in that table. Also
the Server-Load goes very high when issuing this count(*)-Select!
We also do a delete of old data in the table, to keep it "smaller". Once a
Night we have a vacuum-analyze running over the whole database and a second
one over the big tables.
What we get as a result of the Explain on the "select count(*)" for a big
table looks awful for me:
Aggregate (cost=40407.96..40407.96 rows=1 width=0)
-> Seq Scan on userlog (cost=0.00..37029.37 rows=1351437 width=0)
Aggregate (cost=114213.24..114213.24 rows=1 width=0)
-> Seq Scan on trafficcenter (cost=0.00..109446.79 rows=1906579 width=0)
What can we do to improve the performance of big tables in our Database ?!
We are currently running postgresql 7.2.1 on Linux with a 1.8 Athlon machine
with 1 GB RAM!
Regards ... Manuel Rorarius ...