Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 4EE882E014E for ; Thu, 6 Mar 2008 02:48:29 -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 12736-02 for ; Thu, 6 Mar 2008 02:48:26 -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.248]) by postgresql.org (Postfix) with ESMTP id E46E82E0126 for ; Thu, 6 Mar 2008 02:48:25 -0400 (AST) Received: by ag-out-0708.google.com with SMTP id 23so2962294agd.5 for ; Wed, 05 Mar 2008 22:48:25 -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:in-reply-to:mime-version:content-type:references; bh=M9Y6FIzaGGA+dl/qmpoD4xj5QVsT5PFLRgcMx3EZ+VM=; b=UXJCBps8jL9yBzCyvje8fipytbrCML7HGxUVeYZ8ameXv5/8FHCrjhyjVNRoQViM3nhXwmqqFDWDk8363hMxjSbomM0XPeORmSnmCE+hq+bbBa4HGof2V38XD+0QRAC5zFoO6TKgP9HYMtZGQhQxDMb51TxWxRW+5uLMec0P/lw= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:to:subject:in-reply-to:mime-version:content-type:references; b=EmMG3gf7cDvNHwsK9Ne7svwg1YWSgSnBHdNYl42UX0JpLXKc2pcBhEVG6kXtEUaUf8Ihe41KEoREZ7BBT+iJurLqUvAUTCvi9CRjU+A3JMwTZ64+F/nkZZyUYiisXifWfrKD4ojUo/5oYe6rD4S85Xqx1c3v6tuYXvt4gDiUifY= Received: by 10.100.47.13 with SMTP id u13mr9305046anu.12.1204786105220; Wed, 05 Mar 2008 22:48:25 -0800 (PST) Received: by 10.100.105.20 with HTTP; Wed, 5 Mar 2008 22:48:25 -0800 (PST) Message-ID: Date: Thu, 6 Mar 2008 12:18:25 +0530 From: "sathiya psql" To: pgsql-performance@postgresql.org Subject: Fwd: count * performance issue In-Reply-To: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_3603_23209377.1204786105219" References: <20080306060829.GA21084@a-kretschmer.de> <47CF8EA6.8070503@mark.mielke.cc> <20080306063643.GB21084@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/100 X-Sequence-Number: 29054 ------=_Part_3603_23209377.1204786105219 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline ---------- Forwarded message ---------- From: sathiya psql Date: Thu, Mar 6, 2008 at 12:17 PM Subject: Re: [PERFORM] count * performance issue To: "A. Kretschmer" Cc: psql-performance@postgresql.org 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 ??? On Thu, Mar 6, 2008 at 12:06 PM, A. Kretschmer < andreas.kretschmer@schollglas.com> wrote: > am Thu, dem 06.03.2008, um 1:26:46 -0500 mailte Mark Mielke folgendes: > > > > > > There aren't a general solution. If you realy need the exact > count of > > tuples than you can play with a TRIGGER and increase/decrease > the > > tuple-count for this table in an extra table. > > > > > > Of course, this means accepting the cost of obtaining update locks on > the count > > table. > > > > The original poster should understand that they can either get a fast > estimated > > count, or they can get a slow accurate count (either slow in terms of > select > > using count(*) or slow in terms of updates using triggers and locking). > > > > Other systems have their own issues. An index scan may be faster than a > table > > scan for databases that can accurately determine counts using only the > index, > > No. The current index-implementation contains no information about the > row-visibility within the current transaction. You need to scan the > whole data-table to obtain if the current row are visible within the > current transaction. > > > > but it's still a relatively slow operation, and people don't normally > need an > > accurate count for records in the range of 100,000+? :-) > > right. > > > 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_3603_23209377.1204786105219 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline

---------- Forwarded message ----------
From: sathiya psql <sathiya.psql@gmail.com>
Date: Thu, Mar 6, 2008 at 12:17 PM
Subject: Re: [PERFORM] count * performance issue
To: "A. Kretschmer" <andreas.kretschmer@schollglas.com>
Cc: psql-performance@postgresql.org


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


On Thu, Mar 6, 2008 at 12:06 PM, A. Kretschmer <andreas.kretschmer@schollglas.com> wrote:
am  Thu, dem 06.03.2008, um  1:26:46 -0500 mailte Mark Mielke folgendes:
>
>
>         There aren't a general solution. If you realy need the exact count of
>         tuples than you can play with a TRIGGER and increase/decrease the
>         tuple-count for this table in an extra table.
>
>
> Of course, this means accepting the cost of obtaining update locks on the count
> table.
>
> The original poster should understand that they can either get a fast estimated
> count, or they can get a slow accurate count (either slow in terms of select
> using count(*) or slow in terms of updates using triggers and locking).
>
> Other systems have their own issues. An index scan may be faster than a table
> scan for databases that can accurately determine counts using only the index,

No. The current index-implementation contains no information about the
row-visibility within the current transaction. You need to scan the
whole data-table to obtain if the current row are visible within the
current transaction.


> but it's still a relatively slow operation, and people don't normally need an
> accurate count for records in the range of 100,000+? :-)

right.


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_3603_23209377.1204786105219--