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(dot)e(dot)monarchi(at)gmail(dot)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.