Message-ID: <484506FA.9000706@m4n.nl>
Date: Tue, 03 Jun 2008 10:55:22 +0200
From: Marcin Citowicki <marcin.citowicki@m4n.nl>
User-Agent: Mozilla-Thunderbird 2.0.0.9 (X11/20080110)
MIME-Version: 1.0
To: depesz@depesz.com
CC: pgsql-performance@postgresql.org
Subject: Re: query performance question
References: <4844F95B.4040009@m4n.nl> <20080603083146.GB17220@depesz.com>
In-Reply-To: <20080603083146.GB17220@depesz.com>
Content-Type: multipart/mixed; boundary="------------040300010708050400070105"

This is a multi-part message in MIME format.
--------------040300010708050400070105
Content-Type: multipart/alternative;
	boundary="------------000008070709000105030804"


--------------000008070709000105030804
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

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

--------------000008070709000105030804
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 7bit

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
  <meta content="text/html;charset=UTF-8" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
Hello Hubert,<br>
<br>
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.<br>
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.<br>
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<br>
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.<br>
I'll need to take a look at this.<br>
Thank you for your help!<br>
<br>
Marcin<br>
<br>
<br>
hubert depesz lubaczewski wrote:
<blockquote cite="mid:20080603083146.GB17220@depesz.com" type="cite">
  <pre wrap="">On Tue, Jun 03, 2008 at 09:57:15AM +0200, Marcin Citowicki wrote:
  </pre>
  <blockquote type="cite">
    <pre wrap="">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.
    </pre>
  </blockquote>
  <pre wrap=""><!---->
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).

  </pre>
  <blockquote type="cite">
    <pre wrap="">I'd appreciate it if someone could share his/her thoughts on this. Is 
there a way to make this table/query perform better?
    </pre>
  </blockquote>
  <pre wrap=""><!---->
You can keep the count of elements in this table in separate table, and
update it with triggers.

  </pre>
  <blockquote type="cite">
    <pre wrap="">Any query I'm running that joins with transactions table takes forever 
to complete, but maybe this is normal for a table this size.
    </pre>
  </blockquote>
  <pre wrap=""><!---->
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

  </pre>
</blockquote>
</body>
</html>

--------------000008070709000105030804--

--------------040300010708050400070105
Content-Type: text/x-vcard; charset=utf-8;
 name="marcin_citowicki.vcf"
Content-Transfer-Encoding: base64
Content-Disposition: attachment;
 filename="marcin_citowicki.vcf"

YmVnaW46dmNhcmQNCmZuOk1hcmNpbiBDaXRvd2lja2kNCm46Q2l0b3dpY2tpO01hcmNpbg0K
ZW1haWw7aW50ZXJuZXQ6bWFyY2luLmNpdG93aWNraUBtNG4ubmwNCngtbW96aWxsYS1odG1s
OkZBTFNFDQp2ZXJzaW9uOjIuMQ0KZW5kOnZjYXJkDQoNCg==
--------------040300010708050400070105--