Hello Hubert,
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.
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.
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.
I'll need to take a look at this.
Thank you for your help!
Marcin
hubert depesz lubaczewski wrote:
On Tue, Jun 03, 2008 at 09:57:15AM +0200, Marcin Citowicki wrote:
I'm not a dba so I'm not sure if the time it takes to execute this query
is OK or not, it just seems a bit long to me.
This is perfectly OK. count(*) from table is generally slow. There are
some ways to make it faster (depending if you need exact count, or some
estimate).
I'd appreciate it if someone could share his/her thoughts on this. Is
there a way to make this table/query perform better?
You can keep the count of elements in this table in separate table, and
update it with triggers.
Any query I'm running that joins with transactions table takes forever
to complete, but maybe this is normal for a table this size.
As for other queries - show them, and their explain analyze.
Performance of count(*) is dependent basically only on size of table. In
case of other queries - it might be simple to optimize them. Or
impossible - without knowing the queries it's impossible to tell.
Do you really care about count(*) from 60m+ record table? How often do
you count the records?
Best regards,
depesz