Help with count(*)

From: Rajesh Kumar Mallah <mallah(at)trade-india(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Help with count(*)
Date: 2003-11-14 07:21:38
Message-ID: 200311141251.38786.mallah@trade-india.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers pgsql-performance

Hi ,

my database seems to be taking too long for a select count(*)
i think there are lot of dead rows. I do a vacuum full it improves
bu again the performance drops in a short while ,
can anyone please tell me if anything worng with my fsm settings
current fsm=55099264 (not sure how i calculated it)

Regds
Mallah

tradein_clients=# SELECT count(*) from data_bank.profiles ;

+--------+
| count |
+--------+
| 123065 |
+--------+
(1 row)

Time: 49756.969 ms
tradein_clients=#
tradein_clients=#
tradein_clients=# VACUUM full verbose analyze data_bank.profiles ;
INFO: vacuuming "data_bank.profiles"

INFO: "profiles": found 0 removable, 369195 nonremovable row versions in 43423 pages
DETAIL: 246130 dead row versions cannot be removed yet.
Nonremovable row versions range from 136 to 2036 bytes long.
There were 427579 unused item pointers.
Total free space (including removable row versions) is 178536020 bytes.
15934 pages are or will become empty, including 0 at the end of the table.
38112 pages containing 178196624 free bytes are potential move destinations.
CPU 1.51s/0.63u sec elapsed 23.52 sec.
INFO: index "profiles_pincode" now contains 369195 row versions in 3353 pages
DETAIL: 0 index row versions were removed.
379 index pages have been deleted, 379 are currently reusable.
CPU 0.20s/0.24u sec elapsed 22.73 sec.
INFO: index "profiles_city" now contains 369195 row versions in 3411 pages
DETAIL: 0 index row versions were removed.
1030 index pages have been deleted, 1030 are currently reusable.
CPU 0.17s/0.21u sec elapsed 20.67 sec.
INFO: index "profiles_branch" now contains 369195 row versions in 2209 pages
DETAIL: 0 index row versions were removed.
783 index pages have been deleted, 783 are currently reusable.
CPU 0.07s/0.14u sec elapsed 6.38 sec.
INFO: index "profiles_area_code" now contains 369195 row versions in 2606 pages
DETAIL: 0 index row versions were removed.
856 index pages have been deleted, 856 are currently reusable.
CPU 0.11s/0.17u sec elapsed 19.62 sec.
INFO: index "profiles_source" now contains 369195 row versions in 3137 pages
DETAIL: 0 index row versions were removed.
1199 index pages have been deleted, 1199 are currently reusable.
CPU 0.14s/0.12u sec elapsed 9.95 sec.
INFO: index "co_name_index_idx" now contains 368742 row versions in 3945 pages
DETAIL: 0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.19s/0.69u sec elapsed 11.56 sec.
INFO: index "address_index_idx" now contains 368898 row versions in 4828 pages
DETAIL: 0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.16s/0.61u sec elapsed 9.17 sec.
INFO: index "profiles_exp_cat" now contains 153954 row versions in 2168 pages
DETAIL: 0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.07s/0.25u sec elapsed 3.14 sec.
INFO: index "profiles_imp_cat" now contains 73476 row versions in 1030 pages
DETAIL: 0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.05s/0.11u sec elapsed 8.73 sec.
INFO: index "profiles_manu_cat" now contains 86534 row versions in 1193 pages
DETAIL: 0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.03s/0.13u sec elapsed 1.44 sec.
INFO: index "profiles_serv_cat" now contains 19256 row versions in 267 pages
DETAIL: 0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.01s/0.01u sec elapsed 0.25 sec.
INFO: index "profiles_pid" now contains 369195 row versions in 812 pages
DETAIL: 0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.03s/0.12u sec elapsed 0.41 sec.
INFO: index "profiles_pending_branch_id" now contains 0 row versions in 1 pages
DETAIL: 0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: "profiles": moved 0 row versions, truncated 43423 to 43423 pages
DETAIL: CPU 1.76s/3.01u sec elapsed 60.39 sec.
INFO: vacuuming "pg_toast.pg_toast_39873340"
INFO: "pg_toast_39873340": found 0 removable, 65 nonremovable row versions in 15 pages
DETAIL: 0 dead row versions cannot be removed yet.
Nonremovable row versions range from 47 to 2034 bytes long.
There were 0 unused item pointers.
Total free space (including removable row versions) is 17672 bytes.
0 pages are or will become empty, including 0 at the end of the table.
14 pages containing 17636 free bytes are potential move destinations.
CPU 0.00s/0.00u sec elapsed 0.77 sec.
INFO: index "pg_toast_39873340_index" now contains 65 row versions in 2 pages
DETAIL: 0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.46 sec.
INFO: "pg_toast_39873340": moved 0 row versions, truncated 15 to 15 pages
DETAIL: CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: analyzing "data_bank.profiles"
INFO: "profiles": 43423 pages, 123065 rows sampled, 123065 estimated total rows
VACUUM
Time: 246989.138 ms
tradein_clients=# SELECT count(*) from data_bank.profiles ;
+--------+
| count |
+--------+
| 123065 |
+--------+
(1 row)

Time: 4978.725 ms
tradein_clients=#

IMPORVED but still not very good.

Regds
Mallah.

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Shridhar Daithankar 2003-11-14 07:30:34 Re: Help with count(*)
Previous Message Andrew Dunstan 2003-11-14 06:57:36 Re: [HACKERS] heads up -- subtle change of behavior of new initdb

Browse pgsql-performance by date

  From Date Subject
Next Message Shridhar Daithankar 2003-11-14 07:30:34 Re: Help with count(*)
Previous Message Andrew Sullivan 2003-11-13 21:52:51 Re: strange estimate for number of rows