From: Marcin Citowicki <marcin.citowicki@m4n.nl>
To: depesz@depesz.com
Cc: pgsql-performance@postgresql.org
Subject: Re: query performance question
Date: Tue, 03 Jun 2008 10:55:22 +0200
Message-ID: <484506FA.9000706@m4n.nl> (raw)
In-Reply-To: <20080603083146.GB17220@depesz.com>
References: <4844F95B.4040009@m4n.nl>
<20080603083146.GB17220@depesz.com>
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
>
>
Attachments:marcin_citowicki.vcf (text/x-vcard, 142B) download
begin:vcard
fn:Marcin Citowicki
n:Citowicki;Marcin
email;internet:marcin.citowicki@m4n.nl
x-mozilla-html:FALSE
version:2.1
end:vcard
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: marcin.citowicki@m4n.nl, depesz@depesz.com
Subject: Re: query performance question
In-Reply-To: <484506FA.9000706@m4n.nl>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox