Re: count * performance issue

------=_Part_3596_28872007.1204785797566
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

is there any way to explicitly force the postgres to use index scan

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_3596_28872007.1204785797566
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

is there any way to explicitly force the postgres to use index
scan<br><br><div class="gmail_quote">On Thu, Mar 6, 2008 at 12:06 PM, A.
Kretschmer &lt;<a
href="mailto:andreas.kretschmer@[EMAIL PROTECTED]
">andreas.kretschmer@[EMAIL PROTECTED]
>&gt;
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 &nbsp;Thu,
dem 06.03.2008, um &nbsp;1:26:46 -0500 mailte Mark Mielke folgendes:<br>
<div class="Ih2E3d">&gt;<br>
&gt;<br>
&gt; &nbsp; &nbsp; &nbsp; &nbsp; There aren&#39;t a general solution. If
you realy need the exact count of<br>
&gt; &nbsp; &nbsp; &nbsp; &nbsp; tuples than you can play with a TRIGGER
and increase/decrease the<br>
&gt; &nbsp; &nbsp; &nbsp; &nbsp; tuple-count for this table in an extra
table.<br>
&gt;<br>
&gt;<br>
&gt; Of course, this means accepting the cost of obtaining update locks on
the count<br>
&gt; table.<br>
&gt;<br>
&gt; The original poster should understand that they can either get a fast
estimated<br>
&gt; count, or they can get a slow accurate count (either slow in terms of
select<br>
&gt; using count(*) or slow in terms of updates using triggers and
locking).<br>
&gt;<br>
&gt; Other systems have their own issues. An index scan may be faster than
a table<br>
&gt; 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 class="Ih2E3d"><br>
<br>
&gt; but it&#39;s still a relatively slow operation, and people don&#39;t
normally need an<br>
&gt; accurate count for records in the range of 100,000+? :-)<br>
<br>
</div>right.<br>
<div><div></div><div class="Wj3C7c"><br>
<br>
Andreas<br>
--<br>
Andreas Kretschmer<br>
Kontakt: &nbsp;Heynitz: 035242/47150, &nbsp; D1: 0160/7141639 (mehr: -&gt;
Header)<br>
GnuPG-ID: &nbsp; 0x3FFF606C, privat 0x7F4584DA &nbsp; <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]
">pgsql-performance@[EMAIL PROTECTED]
>)<br>
To make changes to your subscription:<br>
<a
href="http://mail.postgresql.org/mj/mj_wwwusr?domain=postgresql.org&amp;extra=pgsql-performance"
target="_blank">http://mail.postgresql.org/mj/mj_wwwusr?domain=postgresql.org&amp;extra=pgsql-performance</a><br>
</div></div></blockquote></div><br>

------=_Part_3596_28872007.1204785797566--