select count(*) very slow on an already vacuumed table.
| От | Rajesh Kumar Mallah |
|---|---|
| Тема | select count(*) very slow on an already vacuumed table. |
| Дата | |
| Msg-id | 407D7A89.60508@trade-india.com обсуждение исходный текст |
| Ответы |
Re: select count(*) very slow on an already vacuumed table.
|
| Список | pgsql-performance |
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)
В списке pgsql-performance по дате отправления:
Следующее
От: Richard HuxtonДата:
Сообщение: Re: select count(*) very slow on an already vacuumed table.