From: | Michael Cochez <michaelcochez(at)yahoo(dot)com> |
---|---|
To: | David Monarchi <david(dot)e(dot)monarchi(at)gmail(dot)com>, post gresql <pgsql-novice(at)postgresql(dot)org> |
Subject: | Re: Long count(*) time |
Date: | 2007-09-27 06:56:19 |
Message-ID: | 884478.72061.qm@web33515.mail.mud.yahoo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
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 at Yahoo! Search.
From | Date | Subject | |
---|---|---|---|
Next Message | Sean Davis | 2007-09-27 11:54:25 | Re: Long count(*) time |
Previous Message | Tom Lane | 2007-09-26 19:07:27 | Re: Could not remove file messages |