Re: count * performance issue

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

On Thu, Mar 6, 2008 at 5:08 PM, A. Kretschmer <
andreas.kretschmer@[EMAIL PROTECTED]
> wrote:>

> > am having a table with nearly 50 lakh records,
> >
> > it has more than 15 columns, i want to count how many records are
there,
> it is
> > taking nearly 17 seconds to do that...
> >
> > i know that to get a approximate count we can use
> >          SELECT reltuples FROM pg_class where relname = TABLENAME;
> >
> > but this give approximate count, and i require exact count...
>
> 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.
>
>
>
Or do something like:

ANALYZE tablename;
select reltuple from pg_class where relname = 'tablename';

That will also return the total number of rows in a table and I guess
might
be much faster then doing a count(*) but yes if trigger can be an option
that can be the easiest way to do it and fastest too.

-- 
Shoaib Mir
Fujitsu Australia Software Technology
shoaibm[@[EMAIL PROTECTED]
 text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

On Thu, Mar 6, 2008 at 5:08 PM, A. Kretschmer &lt;<a
href="mailto:andreas.kretschmer@[EMAIL PROTECTED]
">andreas.kretschmer@[EMAIL PROTECTED]
>&gt;
wrote:&gt;<br><div class="gmail_quote"><blockquote class="gmail_quote"
style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt
0.8ex; padding-left: 1ex;">

&gt; am having a table with nearly 50 lakh records,<br>
&gt;<br>
&gt; it has more than 15 columns, i want to count how many records are
there, it is<br>
&gt; taking nearly 17 seconds to do that...<br>
&gt;<br>
&gt; i know that to get a approximate count we can use<br>
&gt; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;SELECT reltuples FROM pg_class
where relname = TABLENAME;<br>
&gt;<br>
&gt; but this give approximate count, and i require exact count...<br>
<br>
There aren&#39;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></blockquote></div><br>Or do something like:<br><br>ANALYZE
tablename;<br>select reltuple from pg_class where relname =
&#39;tablename&#39;;<br><br>That will also return the total number of rows
in a table and I guess might be much faster then doing a count(*) but yes
if trigger can be an option that can be the easiest way to do it and
fastest too.<br clear="all">
<br>-- <br>Shoaib Mir<br>Fujitsu Australia Software
Technology<br>shoaibm[@[EMAIL PROTECTED]