Re: query performance question

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
>
>