Looks like you need to index those large tables.
CREATE INDEX userlog_idx ON foo_table(userlog);
---- This is what you wrote me ----
: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 ...
:
:
:
:---------------------------(end of broadcast)---------------------------
:TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
:
