From: Rajesh Kumar Mallah <mallah@trade-india.com>
To: Postgres Performance <pgsql-performance@postgresql.org>
Subject: select count(*) very slow on an already vacuumed table.
Date: Wed, 14 Apr 2004 23:23:13 +0530
Message-ID: <407D7A89.60508@trade-india.com> (raw)
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)
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: mallah@trade-india.com
Subject: Re: select count(*) very slow on an already vacuumed table.
In-Reply-To: <407D7A89.60508@trade-india.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox