Received: from localhost (unknown [200.46.204.183])
	by postgresql.org (Postfix) with ESMTP id DEA992E0205
	for <pgsql-performance-postgresql.org@postgresql.org>;
	Thu,  6 Mar 2008 09:12:58 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024)
	with ESMTP id 16503-06 for
	<pgsql-performance-postgresql.org@postgresql.org>;
	Thu,  6 Mar 2008 09:12:49 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.5
Received: from ironport2-out.pppoe.ca (ironport2-out.pppoe.ca
	[206.248.154.182])
	by postgresql.org (Postfix) with ESMTP id 81D622E0184
	for <pgsql-performance@postgresql.org>;
	Thu,  6 Mar 2008 09:12:49 -0400 (AST)
X-IronPort-Anti-Spam-Filtered: true
X-IronPort-Anti-Spam-Result: Ah4FADN8z0fO+Igp/2dsb2JhbACCODapMoEH
X-IronPort-AV: E=Sophos;i="4.25,455,1199682000"; d="scan'208,217";a="15651612"
Received: from smtp.pppoe.ca ([65.39.196.238])
	by ironport2-out.pppoe.ca with ESMTP; 06 Mar 2008 08:12:47 -0500
Received: from [206.248.136.41] ([206.248.136.41])
	by smtp.pppoe.ca (Internet Mail Server v1.0) with ESMTP id MQS56547;
	Thu, 06 Mar 2008 08:12:47 -0500
Message-ID: <47CFED4B.70806@mark.mielke.cc>
Date: Thu, 06 Mar 2008 08:10:35 -0500
From: Mark Mielke <mark@mark.mielke.cc>
User-Agent: Thunderbird 2.0.0.12 (X11/20080226)
MIME-Version: 1.0
To: "A. Kretschmer" <andreas.kretschmer@schollglas.com>
CC: pgsql-performance@postgresql.org
Subject: Re: count * performance issue
References: <f966c2ee0803052142l6a105678jfe2da92a0d9371@mail.gmail.com>
	<f966c2ee0803052143k4570d771o6c300647d3b987d@mail.gmail.com>
	<20080306060829.GA21084@a-kretschmer.de>
	<bf54be870803052215y5bb56235k5ea05ada1d701022@mail.gmail.com>
	<47CF8EA6.8070503@mark.mielke.cc>
	<20080306063643.GB21084@a-kretschmer.de>
	<f966c2ee0803052247n783dae24i9058d148bd17385e@mail.gmail.com>
	<20080306065750.GD21084@a-kretschmer.de>
In-Reply-To: <20080306065750.GD21084@a-kretschmer.de>
Content-Type: multipart/alternative;
	boundary="------------030300090908000501010503"
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200803/117
X-Sequence-Number: 29071

This is a multi-part message in MIME format.
--------------030300090908000501010503
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

A. Kretschmer wrote:
> am  Thu, dem 06.03.2008, um 12:17:55 +0530 mailte sathiya psql folgendes:
>   
>> TRIGGER i can use if i want the count of the whole table, but i require for
>> some of the rows with WHERE condition....
>>
>> so how to do that ???
>>     
>
> Okay, in this case a TRIGGER are a bad idea. You can use an INDEX on
> this row. Can you show us the output for a EXPLAIN ANALYSE SELECT
> count(*) from <your_table> WHERE <your_row> = ... ?
>   

Actually - in this case, TRIGGER can be a good idea. If your count table 
can include the where information, then you no longer require an 
effective table-wide lock for updates.

In the past I have used sequential articles numbers within a topic for 
an online community. Each topic row had an article_count. To generate a 
new article, I could update the article_count and use the number I had 
generated as the article number. To query the number of articles in a 
particular topic, article_count was available. Given thousands of 
topics, and 10s of thousands of articles, the system worked pretty good. 
Not in the millions range as the original poster, but I saw no reason 
why this wouldn't scale.

For the original poster: You might be desperate and looking for help 
from the only place you know to get it from, but some of your recent 
answers have shown that you are either not reading the helpful responses 
provided to you, or you are unwilling to do your own research. If that 
continues, I won't be posting to aid you.

Cheers,
mark

-- 
Mark Mielke <mark@mielke.cc>


--------------030300090908000501010503
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
  <meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
A. Kretschmer wrote:
<blockquote cite="mid:20080306065750.GD21084@a-kretschmer.de"
 type="cite">
  <pre wrap="">am  Thu, dem 06.03.2008, um 12:17:55 +0530 mailte sathiya psql folgendes:
  </pre>
  <blockquote type="cite">
    <pre wrap="">TRIGGER i can use if i want the count of the whole table, but i require for
some of the rows with WHERE condition....

so how to do that ???
    </pre>
  </blockquote>
  <pre wrap=""><!---->
Okay, in this case a TRIGGER are a bad idea. You can use an INDEX on
this row. Can you show us the output for a EXPLAIN ANALYSE SELECT
count(*) from &lt;your_table&gt; WHERE &lt;your_row&gt; = ... ?
  </pre>
</blockquote>
<br>
Actually - in this case, TRIGGER can be a good idea. If your count
table can include the where information, then you no longer require an
effective table-wide lock for updates.<br>
<br>
In the past I have used sequential articles numbers within a topic for
an online community. Each topic row had an article_count. To generate a
new article, I could update the article_count and use the number I had
generated as the article number. To query the number of articles in a
particular topic, article_count was available. Given thousands of
topics, and 10s of thousands of articles, the system worked pretty
good. Not in the millions range as the original poster, but I saw no
reason why this wouldn't scale.<br>
<br>
For the original poster: You might be desperate and looking for help
from the only place you know to get it from, but some of your recent
answers have shown that you are either not reading the helpful
responses provided to you, or you are unwilling to do your own
research. If that continues, I won't be posting to aid you.<br>
<br>
Cheers,<br>
mark<br>
<br>
<pre class="moz-signature" cols="72">-- 
Mark Mielke <a class="moz-txt-link-rfc2396E" href="mailto:mark@mielke.cc">&lt;mark@mielke.cc&gt;</a>
</pre>
</body>
</html>

--------------030300090908000501010503--