------=_Part_3603_23209377.1204786105219
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
---------- Forwarded message ----------
From: sathiya psql <sathiya.psql@[EMAIL PROTECTED]
>
Date: Thu, Mar 6, 2008 at 12:17 PM
Subject: Re: [PERFORM] count * performance issue
To: "A. Kretschmer" <andreas.kretschmer@[EMAIL PROTECTED]
>
Cc: psql-performance@[EMAIL PROTECTED]
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@[EMAIL PROTECTED]
> 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@[EMAIL PROTECTED]
)
> 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
<br><br><div class="gmail_quote">---------- Forwarded message
----------<br>From: <b class="gmail_sendername">sathiya psql</b> <<a
href="mailto:sathiya.psql@[EMAIL PROTECTED]
">sathiya.psql@[EMAIL PROTECTED]
>><br>Date:
Thu, Mar 6, 2008 at 12:17 PM<br>
Subject: Re: [PERFORM] count * performance issue<br>To: "A.
Kretschmer" <<a
href="mailto:andreas.kretschmer@[EMAIL PROTECTED]
">andreas.kretschmer@[EMAIL PROTECTED]
>><br>Cc:
<a
href="mailto:psql-performance@[EMAIL PROTECTED]
">psql-performance@[EMAIL PROTECTED]
><br>
<br><br>TRIGGER i can use if i want the count of the whole table, but i
require for some of the rows with WHERE condition....<br><br>so how to do
that ???<div><div></div><div class="Wj3C7c"><br><br><div
class="gmail_quote">
On Thu, Mar 6, 2008 at 12:06 PM, A. Kretschmer <<a
href="mailto:andreas.kretschmer@[EMAIL PROTECTED]
"
target="_blank">andreas.kretschmer@[EMAIL PROTECTED]
>> wrote:<br>
<blockquote class="gmail_quote" style="border-left: 1px solid rgb(204,
204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">am Thu,
dem 06.03.2008, um 1:26:46 -0500 mailte Mark Mielke folgendes:<br>
<div>><br>
><br>
> There aren't a general solution. If
you realy need the exact count of<br>
> tuples than you can play with a TRIGGER
and increase/decrease the<br>
> tuple-count for this table in an extra
table.<br>
><br>
><br>
> Of course, this means accepting the cost of obtaining update locks on
the count<br>
> table.<br>
><br>
> The original poster should understand that they can either get a fast
estimated<br>
> count, or they can get a slow accurate count (either slow in terms of
select<br>
> using count(*) or slow in terms of updates using triggers and
locking).<br>
><br>
> Other systems have their own issues. An index scan may be faster than
a table<br>
> scan for databases that can accurately determine counts using only
the index,<br>
<br>
</div>No. The current index-implementation contains no information about
the<br>
row-visibility within the current transaction. You need to scan the<br>
whole data-table to obtain if the current row are visible within the<br>
current transaction.<br>
<div><br>
<br>
> but it's still a relatively slow operation, and people don't
normally need an<br>
> accurate count for records in the range of 100,000+? :-)<br>
<br>
</div>right.<br>
<div><div></div><div><br>
<br>
Andreas<br>
--<br>
Andreas Kretschmer<br>
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: ->
Header)<br>
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA <a
href="http://wwwkeys.de.pgp.net"
target="_blank">http://wwwkeys.de.pgp.net</a><br>
<br>
--<br>
Sent via pgsql-performance mailing list (<a
href="mailto:pgsql-performance@[EMAIL PROTECTED]
"
target="_blank">pgsql-performance@[EMAIL PROTECTED]
>)<br>
To make changes to your subscription:<br>
<a
href="http://mail.postgresql.org/mj/mj_wwwusr?domain=postgresql.org&extra=pgsql-performance"
target="_blank">http://mail.postgresql.org/mj/mj_wwwusr?domain=postgresql.org&extra=pgsql-performance</a><br>
</div></div></blockquote></div><br>
</div></div></div><br>
------=_Part_3603_23209377.1204786105219--
|
