X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 40B39D1E104 for ; Wed, 14 Apr 2004 14:53:09 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 31479-07 for ; Wed, 14 Apr 2004 14:53:07 -0300 (ADT) Received: from trade-india.com (ns5.trade-india.com [66.234.10.13]) by svr1.postgresql.org (Postfix) with SMTP id 2AA8DD1E17E for ; Wed, 14 Apr 2004 14:53:05 -0300 (ADT) Received: (qmail 29057 invoked from network); 14 Apr 2004 14:23:59 -0000 Received: from unknown (HELO trade-india.com) (203.145.130.142) by ns5.trade-india.com with SMTP; 14 Apr 2004 14:23:59 -0000 Message-ID: <407D7A89.60508@trade-india.com> Date: Wed, 14 Apr 2004 23:23:13 +0530 From: Rajesh Kumar Mallah User-Agent: Mozilla Thunderbird 0.5 (X11/20040208) X-Accept-Language: en-us, en MIME-Version: 1.0 To: Postgres Performance Subject: select count(*) very slow on an already vacuumed table. Content-Type: multipart/alternative; boundary="------------070309060401080600010502" X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200404/163 X-Sequence-Number: 6463 This is a multi-part message in MIME format. --------------070309060401080600010502 Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit Hi I have .5 million rows in a table. My problem is select count(*) takes ages. VACUUM FULL does not help. can anyone please tell me how to i enhance the performance of the setup. Regds mallah. postgresql.conf ---------------------- max_fsm_pages = 55099264 # min max_fsm_relations*16, 6 bytes each max_fsm_relations = 5000 tradein_clients=# explain analyze SELECT count(*) from eyp_rfi; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------- Aggregate (cost=78311.37..78311.37 rows=1 width=0) (actual time=42306.902..42306.903 rows=1 loops=1) -> Seq Scan on eyp_rfi (cost=0.00..77046.49 rows=505949 width=0) (actual time=0.032..41525.007 rows=505960 loops=1) Total runtime: 42306.995 ms (3 rows) tradein_clients=# SELECT count(*) from eyp_rfi; count -------- 505960 (1 row) tradein_clients=# VACUUM full verbose eyp_rfi; INFO: vacuuming "public.eyp_rfi" INFO: "eyp_rfi": found 0 removable, 505960 nonremovable row versions in 71987 pages DETAIL: 0 dead row versions cannot be removed yet. Nonremovable row versions range from 186 to 2036 bytes long. There were 42587 unused item pointers. Total free space (including removable row versions) is 21413836 bytes. 0 pages are or will become empty, including 0 at the end of the table. 38693 pages containing 19146684 free bytes are potential move destinations. CPU 2.62s/0.40u sec elapsed 38.45 sec. INFO: index "eyp_rfi_date" now contains 505960 row versions in 1197 pages DETAIL: 0 index row versions were removed. 4 index pages have been deleted, 4 are currently reusable. CPU 0.05s/0.29u sec elapsed 0.87 sec. INFO: index "eyp_rfi_receiver_uid" now contains 505960 row versions in 1163 pages DETAIL: 0 index row versions were removed. 1 index pages have been deleted, 1 are currently reusable. CPU 0.03s/0.42u sec elapsed 1.33 sec. INFO: index "eyp_rfi_inhouse" now contains 505960 row versions in 1208 pages DETAIL: 0 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.04s/0.21u sec elapsed 1.20 sec. INFO: index "eyp_rfi_rfi_id_key" now contains 505960 row versions in 1201 pages DETAIL: 0 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.03s/0.33u sec elapsed 0.81 sec. INFO: index "eyp_rfi_list_id_idx" now contains 505960 row versions in 1133 pages DETAIL: 0 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.02s/0.43u sec elapsed 1.12 sec. INFO: index "eyp_rfi_status" now contains 505960 row versions in 1448 pages DETAIL: 0 index row versions were removed. 4 index pages have been deleted, 4 are currently reusable. CPU 0.05s/0.22u sec elapsed 1.08 sec. INFO: index "eyp_rfi_list_id" now contains 505960 row versions in 1133 pages DETAIL: 0 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.02s/0.43u sec elapsed 1.00 sec. INFO: index "eyp_rfi_receiver_email" now contains 505960 row versions in 2801 pages DETAIL: 0 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.16s/0.52u sec elapsed 10.38 sec. INFO: index "eyp_rfi_subj" now contains 80663 row versions in 463 pages DETAIL: 0 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.03s/0.14u sec elapsed 3.20 sec. INFO: index "eyp_rfi_sender" now contains 505960 row versions in 3025 pages DETAIL: 0 index row versions were removed. 6 index pages have been deleted, 6 are currently reusable. CPU 0.10s/0.39u sec elapsed 4.99 sec. INFO: index "eyp_sender_uid_idx" now contains 505960 row versions in 1216 pages DETAIL: 0 index row versions were removed. 5 index pages have been deleted, 5 are currently reusable. CPU 0.04s/0.36u sec elapsed 2.61 sec. INFO: index "eyp_rfi_rec_uid_idx" now contains 505960 row versions in 1166 pages DETAIL: 0 index row versions were removed. 1 index pages have been deleted, 1 are currently reusable. CPU 0.05s/0.41u sec elapsed 2.04 sec. INFO: index "eyp_rfi_index" now contains 505960 row versions in 2051 pages DETAIL: 0 index row versions were removed. 7 index pages have been deleted, 7 are currently reusable. CPU 0.10s/0.28u sec elapsed 8.16 sec. INFO: "eyp_rfi": moved 0 row versions, truncated 71987 to 71987 pages DETAIL: CPU 2.03s/2.09u sec elapsed 95.24 sec. INFO: vacuuming "pg_toast.pg_toast_19609" INFO: "pg_toast_19609": found 0 removable, 105342 nonremovable row versions in 21038 pages DETAIL: 0 dead row versions cannot be removed yet. Nonremovable row versions range from 37 to 2034 bytes long. There were 145 unused item pointers. Total free space (including removable row versions) is 16551072 bytes. 0 pages are or will become empty, including 0 at the end of the table. 18789 pages containing 16512800 free bytes are potential move destinations. CPU 0.70s/0.09u sec elapsed 41.64 sec. INFO: index "pg_toast_19609_index" now contains 105342 row versions in 296 pages DETAIL: 0 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.00s/0.02u sec elapsed 0.63 sec. INFO: "pg_toast_19609": moved 0 row versions, truncated 21038 to 21038 pages DETAIL: CPU 0.01s/0.01u sec elapsed 10.03 sec. VACUUM tradein_clients=# explain analyze SELECT count(*) from eyp_rfi; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------- Aggregate (cost=78311.50..78311.50 rows=1 width=0) (actual time=50631.488..50631.489 rows=1 loops=1) -> Seq Scan on eyp_rfi (cost=0.00..77046.60 rows=505960 width=0) (actual time=0.030..49906.198 rows=505964 loops=1) Total runtime: 50631.658 ms (3 rows) --------------070309060401080600010502 Content-Type: text/html; charset=us-ascii Content-Transfer-Encoding: 7bit
Hi
I have .5 million rows in a table. My problem is select count(*) takes ages.
VACUUM FULL does not help. can anyone please tell me
how to i enhance the performance of the setup.

Regds
mallah.

postgresql.conf
----------------------
max_fsm_pages = 55099264                # min max_fsm_relations*16, 6 bytes each
max_fsm_relations = 5000


tradein_clients=# explain analyze SELECT count(*) from eyp_rfi;
                                                       QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=78311.37..78311.37 rows=1 width=0) (actual time=42306.902..42306.903 rows=1 loops=1)
   ->  Seq Scan on eyp_rfi  (cost=0.00..77046.49 rows=505949 width=0) (actual time=0.032..41525.007 rows=505960 loops=1)
 Total runtime: 42306.995 ms
(3 rows)

tradein_clients=# SELECT count(*) from eyp_rfi;
 count
--------
 505960
(1 row)

tradein_clients=# VACUUM full verbose eyp_rfi;
INFO:  vacuuming "public.eyp_rfi"
INFO:  "eyp_rfi": found 0 removable, 505960 nonremovable row versions in 71987 pages
DETAIL:  0 dead row versions cannot be removed yet.
Nonremovable row versions range from 186 to 2036 bytes long.
There were 42587 unused item pointers.
Total free space (including removable row versions) is 21413836 bytes.
0 pages are or will become empty, including 0 at the end of the table.
38693 pages containing 19146684 free bytes are potential move destinations.
CPU 2.62s/0.40u sec elapsed 38.45 sec.
INFO:  index "eyp_rfi_date" now contains 505960 row versions in 1197 pages
DETAIL:  0 index row versions were removed.
4 index pages have been deleted, 4 are currently reusable.
CPU 0.05s/0.29u sec elapsed 0.87 sec.
INFO:  index "eyp_rfi_receiver_uid" now contains 505960 row versions in 1163 pages
DETAIL:  0 index row versions were removed.
1 index pages have been deleted, 1 are currently reusable.
CPU 0.03s/0.42u sec elapsed 1.33 sec.
INFO:  index "eyp_rfi_inhouse" now contains 505960 row versions in 1208 pages
DETAIL:  0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.04s/0.21u sec elapsed 1.20 sec.
INFO:  index "eyp_rfi_rfi_id_key" now contains 505960 row versions in 1201 pages
DETAIL:  0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.03s/0.33u sec elapsed 0.81 sec.
INFO:  index "eyp_rfi_list_id_idx" now contains 505960 row versions in 1133 pages
DETAIL:  0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.02s/0.43u sec elapsed 1.12 sec.
INFO:  index "eyp_rfi_status" now contains 505960 row versions in 1448 pages
DETAIL:  0 index row versions were removed.
4 index pages have been deleted, 4 are currently reusable.
CPU 0.05s/0.22u sec elapsed 1.08 sec.
INFO:  index "eyp_rfi_list_id" now contains 505960 row versions in 1133 pages
DETAIL:  0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.02s/0.43u sec elapsed 1.00 sec.
INFO:  index "eyp_rfi_receiver_email" now contains 505960 row versions in 2801 pages
DETAIL:  0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.16s/0.52u sec elapsed 10.38 sec.
INFO:  index "eyp_rfi_subj" now contains 80663 row versions in 463 pages
DETAIL:  0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.03s/0.14u sec elapsed 3.20 sec.
INFO:  index "eyp_rfi_sender" now contains 505960 row versions in 3025 pages
DETAIL:  0 index row versions were removed.
6 index pages have been deleted, 6 are currently reusable.
CPU 0.10s/0.39u sec elapsed 4.99 sec.
INFO:  index "eyp_sender_uid_idx" now contains 505960 row versions in 1216 pages
DETAIL:  0 index row versions were removed.
5 index pages have been deleted, 5 are currently reusable.
CPU 0.04s/0.36u sec elapsed 2.61 sec.
INFO:  index "eyp_rfi_rec_uid_idx" now contains 505960 row versions in 1166 pages
DETAIL:  0 index row versions were removed.
1 index pages have been deleted, 1 are currently reusable.
CPU 0.05s/0.41u sec elapsed 2.04 sec.
INFO:  index "eyp_rfi_index" now contains 505960 row versions in 2051 pages
DETAIL:  0 index row versions were removed.
7 index pages have been deleted, 7 are currently reusable.
CPU 0.10s/0.28u sec elapsed 8.16 sec.
INFO:  "eyp_rfi": moved 0 row versions, truncated 71987 to 71987 pages
DETAIL:  CPU 2.03s/2.09u sec elapsed 95.24 sec.
INFO:  vacuuming "pg_toast.pg_toast_19609"
INFO:  "pg_toast_19609": found 0 removable, 105342 nonremovable row versions in 21038 pages
DETAIL:  0 dead row versions cannot be removed yet.
Nonremovable row versions range from 37 to 2034 bytes long.
There were 145 unused item pointers.
Total free space (including removable row versions) is 16551072 bytes.
0 pages are or will become empty, including 0 at the end of the table.
18789 pages containing 16512800 free bytes are potential move destinations.
CPU 0.70s/0.09u sec elapsed 41.64 sec.
INFO:  index "pg_toast_19609_index" now contains 105342 row versions in 296 pages
DETAIL:  0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.02u sec elapsed 0.63 sec.
INFO:  "pg_toast_19609": moved 0 row versions, truncated 21038 to 21038 pages
DETAIL:  CPU 0.01s/0.01u sec elapsed 10.03 sec.
VACUUM
tradein_clients=# explain analyze SELECT count(*) from eyp_rfi;
                                                       QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=78311.50..78311.50 rows=1 width=0) (actual time=50631.488..50631.489 rows=1 loops=1)
   ->  Seq Scan on eyp_rfi  (cost=0.00..77046.60 rows=505960 width=0) (actual time=0.030..49906.198 rows=505964 loops=1)
 Total runtime: 50631.658 ms
(3 rows)

--------------070309060401080600010502--