Unqualified SELECT COUNT(*) FROM foo is one of the most expensive operations you can do on your system, since the visibility information has to be checked on disk for each row. Instead, try real queries on real data, and post here if some are too slow for you.
Ok, that's a bad example. I'm learning :-) Is insert ... select also really expensive then? I have a table loaded with message-id and path information of currently-existing messages. It has ~20 million rows. Trying to do "INSERT INTO Messages(path, msgid) SELECT (path, msgid) FROM tmpMessages" took a really long time before psql died with an out-of-memory error. Is there a more sane way to do a table copy, or should I have just dropped all the indices from the Message table and loaded into that? Thanks!
