Thank you for your reply. I don't really need to count rows in
transactions table, I just thought this was a good example to show how
slow the query was.
Usually you're more interested in the performance of the queries you need
to make rather than the ones you don't need to make ;)
But based on what you wrote it looks like count(*) is slow in general,
so this seems to be OK since the table is rather large.
Well any query that needs to scan 60 million rows will be slow...
Now understand that this is not a problem with count(*) which can be very
fast if you "select count(*) where..." and the condition in the where
produces a reasonable number of rows to count, it is just a problem of
having to scan the 60 million rows. But fortunately since it is perfectly
useless to know the rowcount of this 60 million table with a perfect
precision you never need to make this query ;)
I just ran other queries (joining transactions table) and they returned
quickly, which leads me to believe that there could be a problem not
with the database, but with the box
the db is running on. Sometimes those same queries take forever and now
they complete in no time at all, so perhaps there is a process that is
running periodically which is slowing the db down.
Then if you have specific queries that you need to optimize you will need
to run EXPLAIN ANALYZE on them and post the results, when they are fast
and when they are slow to see if there is a difference in plans. Also the
output from vmstat in times of big slowness can provide useful
information. Crosschecking with your cron jobs, etc is a good idea. Also
the usual suspects, like are your tables VACUUM'd and ANALYZE'd etc.
