>>I am using Postgres 7.4 on Fedora 3.0. It is running with the default
>>configuration.
....
> Probably this is not the right list for this.
> Did you run vacuum after inserting the records?
> Check your statement with explain analyze, it will show you why is it
> slow.
also, check serverlog in the data directory (/var/pgsql/data is the
Redhat/Fedora default, I believe). If you're logging lots of errors, that file
will grow, and they don't manage it.
also, the defaults in $PGDATA/postgresql.conf in 7.4 are *VERY* inefficient.
there are a few key parameters that can make a significant difference in
overall performance.
and, crontab (from the postgres user account) a vacuumdb every hour or so if
you're doing lots of activity. once a day or week, do a vacuumdb -z to
reindex stuff.
and, shut postgres down, mv the $PGDATA directory to a different dedicated
physical disk drive, and symlink it, then restart postgres.
I've found that 7.x pgsql databases periodically need to be pg_dump'd, dropdb,
then reloaded from the dump to continue to perform well... on a heavy use web
server database which includes a busy user forum, I find doing this about 2-3
times a year keeps things smooth. there may be a better way to clean them up,
but I've not found it.