Re: Long count(*) time

If you vacuum regularly, you can parse the output of:

explain select count(*) from url_list_url;

The rowcount will be pretty close.  If you simply need "close", this is
an instantaneous query.

Sean


Michael Cochez wrote:
> I'm a newbie in databases but maybe this is useful :
> "Triggers can be defined to execute either before or after any INSERT,
> UPDATE, or DELETE operation, either once per modified row, or once per
> SQL statement. If a trigger event occurs, the trigger's function is
> called at the appropriate time to handle the event."
> (from : http://www.postgresql.org/docs/8.1/interactive/triggers.html)
> If you run this query a lot of times, it might be useful to write such a
> procedure that on every insert increases the number and on every delete
> decreases the number which you can store in a separate table. Of course
> usability depends on the number of insert/delete queries performed
> because the count query will go to milliseconds but every insert/delete
> will take (no idea how much) longer.
>
> Michael
>
>
>
> */David Monarchi <david.e.monarchi@gmail.com>/* wrote:
>
>     Hello -
>
>     I'm running PG 8.2 on an 8-processor 16G Unix machine.  The machine
>     is dedicated to the db, and only 5 threads/processors are busy.  The
>     following query takes 70 seconds to execute.
>          select count(*) from url_list_url;
>     There are 64,219,173 rows in the table.  The table consists of an
>     integer field and a text field.  The average length of the text
>     field is 50 characters.  There are btree indexes on both fields.
>     The integer field is the key.
>
>     70 seconds seems to be a long time for this kind of query.  Is this
>     normal?
>
>     Thanks.
>
>     David
>
>
> ------------------------------------------------------------------------
> Luggage? GPS? Comic books?
> Check out fitting gifts for grads
> <http://us.rd.yahoo.com/evt=48249/*http://search.yahoo.com/search?fr=oni_on_mail&p=graduation+gifts&cs=bz>
> at Yahoo! Search.