"Markus Wollny" <Markus.Wollny@computec.de> writes:
> ... We do use
> EXPLAIN-output for finding out if our indexes are used or not (and it
> seems they are), but there's still so much left to guess-work, like what
> bit exactly is causing the high server-loads.
You already got lots of good advice from Alvar and Curt, but I just
wanted to add that you can learn something about which queries are
causing the load with appropriate use of logging and stats.
Logging: turn on debug_print_query and show_query_stats, run some test
cases, eyeball results in postmaster log to see which queries use the
most CPU time and I/O.
Stats: see
http://www.ca.postgresql.org/users-lounge/docs/7.2/postgres/monitoring.html
You should turn on stats_command_string and then correlate the
pg_stat_activity outputs with the PIDs that are chewing CPU according to
'top'.
regards, tom lane