Received: from localhost (unknown [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 3322F2E2F4D for ; Thu, 6 Mar 2008 03:06:52 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 17485-08 for ; Thu, 6 Mar 2008 03:06:46 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from ag-out-0708.google.com (ag-out-0708.google.com [72.14.246.246]) by postgresql.org (Postfix) with ESMTP id D27FF2E00C8 for ; Thu, 6 Mar 2008 03:06:48 -0400 (AST) Received: by ag-out-0708.google.com with SMTP id 23so2974420agd.5 for ; Wed, 05 Mar 2008 23:06:48 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; bh=kS8NyU2cGuPiDliftFfilGeXIUdtKlq3ZRIXaUYFs50=; b=NtSrBCliFkTkD6pSzTtLQdTRIyzg9w1/rw84Of+R6E85z1Um90XcLK+4rEscd4OrK9gASF+l0Sc58CMMe5Ar2OygRnuePU3wjcbdsCcB4olO9N4HZvnVapCiniKKZgMBsmRKaA6Py87ne+QHk8IVe3nVtWr18ap3IgD5zfM4oII= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=CbBWklb4VBzpBizHDbTRFIcW3kUHBEBEdWttAccsf6OdGic1k8qYiDs/CgDWWqHTj5w9H9wHr4RB5UBy0VIBQl2rf3b2p5w/tloovJ7e+5fAIV1RNapNKTNiYV3AXYS8uHm49EU1runowjQZaejgz1/R2wrVdbYhOyhKj4nb3qI= Received: by 10.100.232.13 with SMTP id e13mr2788678anh.26.1204787208131; Wed, 05 Mar 2008 23:06:48 -0800 (PST) Received: by 10.100.105.20 with HTTP; Wed, 5 Mar 2008 23:06:48 -0800 (PST) Message-ID: Date: Thu, 6 Mar 2008 12:36:48 +0530 From: "sathiya psql" To: "A. Kretschmer" Subject: Re: count * performance issue Cc: pgsql-performance@postgresql.org In-Reply-To: <20080306065750.GD21084@a-kretschmer.de> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_3616_12861576.1204787208144" References: <20080306060829.GA21084@a-kretschmer.de> <47CF8EA6.8070503@mark.mielke.cc> <20080306063643.GB21084@a-kretschmer.de> <20080306065750.GD21084@a-kretschmer.de> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.001 tagged_above=0 required=5 tests=HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 200803/104 X-Sequence-Number: 29058 ------=_Part_3616_12861576.1204787208144 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------ Aggregate (cost=205756.95..205756.95 rows=1 width=0) (actual time= 114675.042..114675.042 rows=1 loops=1) -> Seq Scan on call_log (cost=0.00..193224.16 rows=5013112 width=0) (actual time=11.754..91429.594 rows=5061619 loops=1) Filter: (call_id > 0) Total runtime: 114699.797 ms (4 rows) it is now taking 114 seconds, i think because of load in my system.... any way will you explain., what is this COST, actual time and other stuffs.... On Thu, Mar 6, 2008 at 12:27 PM, A. Kretschmer < andreas.kretschmer@schollglas.com> 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 WHERE = ... ? > > > Andreas > -- > Andreas Kretschmer > Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) > GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net > > -- > Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) > To make changes to your subscription: > > http://mail.postgresql.org/mj/mj_wwwusr?domain=postgresql.org&extra=pgsql-performance > ------=_Part_3616_12861576.1204787208144 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline
                                                          QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=205756.95..205756.95 rows=1 width=0) (actual time=114675.042..114675.042 rows=1 loops=1)
   ->  Seq Scan on call_log  (cost=0.00..193224.16 rows=5013112 width=0) (actual time=11.754..91429.594 rows=5061619 loops=1)
         Filter: (call_id > 0)
 Total runtime: 114699.797 ms
(4 rows)


it is now taking 114 seconds, i think because of load in my system.... any way will you explain., what is this COST, actual time and other stuffs....

On Thu, Mar 6, 2008 at 12:27 PM, A. Kretschmer <andreas.kretschmer@schollglas.com> 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> = ... ?


Andreas
--
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net

--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://mail.postgresql.org/mj/mj_wwwusr?domain=postgresql.org&extra=pgsql-performance

------=_Part_3616_12861576.1204787208144--