Re: query performance question

От: Marcin Citowicki
Тема: Re: query performance question
Дата: ,
Msg-id: 484506FA.9000706@m4n.nl
(см: обсуждение, исходный текст)
Ответ на: Re: query performance question  (hubert depesz lubaczewski)
Ответы: Re: query performance question  (PFC)
Список: pgsql-performance

Скрыть дерево обсуждения

query performance question  (Marcin Citowicki, )
 Re: query performance question  (hubert depesz lubaczewski, )
  Re: query performance question  (Marcin Citowicki, )
   Re: query performance question  (PFC, )
 Re: query performance question  (, )
  Re: query performance question  (Dan Harris, )
   Re: query performance question  (Kenneth Marshall, )
    Re: query performance question  (Dan Harris, )

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
 
Вложения

В списке pgsql-performance по дате сообщения:

От: tv@fuzzy.cz
Дата:
Сообщение: Re: query performance question
От: andrew klassen
Дата:
Сообщение: insert/update tps slow with indices on table > 1M rows