pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
select count(*)  very slow on an already vacuumed table.
7+ messages / 2 participants
[nested] [flat]

* select count(*)  very slow on an already vacuumed table.
@ 2004-04-14 17:53 Rajesh Kumar Mallah <mallah@trade-india.com>
  2004-04-14 19:08 ` Re: select count(*)  very slow on an already vacuumed table. Richard Huxton <dev@archonet.com>
  0 siblings, 1 reply; 7+ messages in thread

From: Rajesh Kumar Mallah @ 2004-04-14 17:53 UTC (permalink / raw)
  To: 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)

^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: select count(*)  very slow on an already vacuumed table.
  2004-04-14 17:53 select count(*)  very slow on an already vacuumed table. Rajesh Kumar Mallah <mallah@trade-india.com>
@ 2004-04-14 19:08 ` Richard Huxton <dev@archonet.com>
  2004-04-14 20:43   ` Re: select count(*)  very slow on an already vacuumed table. Rajesh Kumar Mallah <mallah@trade-india.com>
  2004-04-15 07:10   ` Re: select count(*)  very slow on an already vacuumed table. Rajesh Kumar Mallah <mallah@trade-india.com>
  0 siblings, 2 replies; 7+ messages in thread

From: Richard Huxton @ 2004-04-14 19:08 UTC (permalink / raw)
  To: Rajesh Kumar Mallah <mallah@trade-india.com>; pgsql-performance

On Wednesday 14 April 2004 18:53, Rajesh Kumar Mallah wrote:
> 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.

> SELECT count(*) from eyp_rfi;

If this is the actual query you're running, and you need a guaranteed accurate 
result, then you only have one option: write a trigger function to update a 
table_count table with every insert/delete to eyp_rfi.

There is loads of info on this (and why it isn't as simple as you might think) 
in the archives. First though:
1. Is this the actual query, or just a representation?
2. Do you need an accurate figure or just something "near enough"?

-- 
  Richard Huxton
  Archonet Ltd



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: select count(*)  very slow on an already vacuumed table.
  2004-04-14 17:53 select count(*)  very slow on an already vacuumed table. Rajesh Kumar Mallah <mallah@trade-india.com>
  2004-04-14 19:08 ` Re: select count(*)  very slow on an already vacuumed table. Richard Huxton <dev@archonet.com>
@ 2004-04-14 20:43   ` Rajesh Kumar Mallah <mallah@trade-india.com>
  1 sibling, 0 replies; 7+ messages in thread

From: Rajesh Kumar Mallah @ 2004-04-14 20:43 UTC (permalink / raw)
  To: Richard Huxton <dev@archonet.com>; +Cc: pgsql-performance

Richard Huxton wrote:

>On Wednesday 14 April 2004 18:53, Rajesh Kumar Mallah wrote:
>  
>
>>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.
>>    
>>
>
>  
>
>>SELECT count(*) from eyp_rfi;
>>    
>>
>
>If this is the actual query you're running, and you need a guaranteed accurate 
>result, then you only have one option: write a trigger function to update a 
>table_count table with every insert/delete to eyp_rfi.
>  
>

it is just an example. in general all the queries that involves eyp_rfi
become slow. reloading the table makes the query faster.

mallah.

>There is loads of info on this (and why it isn't as simple as you might think) 
>in the archives. First though:
>1. Is this the actual query, or just a representation?
>2. Do you need an accurate figure or just something "near enough"?
>
>  
>




^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: select count(*)  very slow on an already vacuumed table.
  2004-04-14 17:53 select count(*)  very slow on an already vacuumed table. Rajesh Kumar Mallah <mallah@trade-india.com>
  2004-04-14 19:08 ` Re: select count(*)  very slow on an already vacuumed table. Richard Huxton <dev@archonet.com>
@ 2004-04-15 07:10   ` Rajesh Kumar Mallah <mallah@trade-india.com>
  2004-04-15 07:34     ` Re: select count(*)  very slow on an already vacuumed table. Rajesh Kumar Mallah <mallah@trade-india.com>
  2004-04-15 07:53     ` Re: select count(*)  very slow on an already vacuumed table. Richard Huxton <dev@archonet.com>
  1 sibling, 2 replies; 7+ messages in thread

From: Rajesh Kumar Mallah @ 2004-04-15 07:10 UTC (permalink / raw)
  To: Richard Huxton <dev@archonet.com>; +Cc: pgsql-performance



The problem is that i want to know if i need a Hardware upgrade
at the moment.

Eg i have another table rfis which contains ~ .6 million records.


SELECT count(*) from rfis where sender_uid > 0;
+--------+
| count  |
+--------+
| 564870 |
+--------+
Time: 117560.635 ms

Which is approximate 4804 records per second. Is it an acceptable
performance  on the hardware below:

RAM: 2 GB
DISKS: ultra160 , 10 K  , 18 GB
Processor: 2* 2.0 Ghz Xeon

What kind of upgrades shoud be put on the server for it to become
reasonable fast.


Regds
mallah.




Richard Huxton wrote:

>On Wednesday 14 April 2004 18:53, Rajesh Kumar Mallah wrote:
>  
>
>>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.
>>    
>>
>
>  
>
>>SELECT count(*) from eyp_rfi;
>>    
>>
>
>If this is the actual query you're running, and you need a guaranteed accurate 
>result, then you only have one option: write a trigger function to update a 
>table_count table with every insert/delete to eyp_rfi.
>
>There is loads of info on this (and why it isn't as simple as you might think) 
>in the archives. First though:
>1. Is this the actual query, or just a representation?
>2. Do you need an accurate figure or just something "near enough"?
>
>  
>




^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: select count(*)  very slow on an already vacuumed table.
  2004-04-14 17:53 select count(*)  very slow on an already vacuumed table. Rajesh Kumar Mallah <mallah@trade-india.com>
  2004-04-14 19:08 ` Re: select count(*)  very slow on an already vacuumed table. Richard Huxton <dev@archonet.com>
  2004-04-15 07:10   ` Re: select count(*)  very slow on an already vacuumed table. Rajesh Kumar Mallah <mallah@trade-india.com>
@ 2004-04-15 07:34     ` Rajesh Kumar Mallah <mallah@trade-india.com>
  1 sibling, 0 replies; 7+ messages in thread

From: Rajesh Kumar Mallah @ 2004-04-15 07:34 UTC (permalink / raw)
  To: Richard Huxton <dev@archonet.com>; +Cc: pgsql-performance


The relation size for this table is  1.7 GB

tradein_clients=# SELECT public.relation_size ('general.rfis');
+------------------+
| relation_size    |
+------------------+
|    1,762,639,872 |
+------------------+
(1 row)

Regds
mallah.

Rajesh Kumar Mallah wrote:

>
>
> The problem is that i want to know if i need a Hardware upgrade
> at the moment.
>
> Eg i have another table rfis which contains ~ .6 million records.
>
>
> SELECT count(*) from rfis where sender_uid > 0;
> +--------+
> | count  |
> +--------+
> | 564870 |
> +--------+
> Time: 117560.635 ms
>
> Which is approximate 4804 records per second. Is it an acceptable
> performance  on the hardware below:
>
> RAM: 2 GB
> DISKS: ultra160 , 10 K  , 18 GB
> Processor: 2* 2.0 Ghz Xeon
>
> What kind of upgrades shoud be put on the server for it to become
> reasonable fast.
>
>
> Regds
> mallah.
>
>
>
>
> Richard Huxton wrote:
>
>> On Wednesday 14 April 2004 18:53, Rajesh Kumar Mallah wrote:
>>  
>>
>>> 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.
>>>   
>>
>>
>>  
>>
>>> SELECT count(*) from eyp_rfi;
>>>   
>>
>>
>> If this is the actual query you're running, and you need a guaranteed 
>> accurate result, then you only have one option: write a trigger 
>> function to update a table_count table with every insert/delete to 
>> eyp_rfi.
>>
>> There is loads of info on this (and why it isn't as simple as you 
>> might think) in the archives. First though:
>> 1. Is this the actual query, or just a representation?
>> 2. Do you need an accurate figure or just something "near enough"?
>>
>>  
>>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>    (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>




^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: select count(*)  very slow on an already vacuumed table.
  2004-04-14 17:53 select count(*)  very slow on an already vacuumed table. Rajesh Kumar Mallah <mallah@trade-india.com>
  2004-04-14 19:08 ` Re: select count(*)  very slow on an already vacuumed table. Richard Huxton <dev@archonet.com>
  2004-04-15 07:10   ` Re: select count(*)  very slow on an already vacuumed table. Rajesh Kumar Mallah <mallah@trade-india.com>
@ 2004-04-15 07:53     ` Richard Huxton <dev@archonet.com>
  2004-04-15 10:31       ` Re: select count(*)  very slow on an already vacuumed table. Rajesh Kumar Mallah <mallah@trade-india.com>
  1 sibling, 1 reply; 7+ messages in thread

From: Richard Huxton @ 2004-04-15 07:53 UTC (permalink / raw)
  To: Rajesh Kumar Mallah <mallah@trade-india.com>; +Cc: pgsql-performance

On Thursday 15 April 2004 08:10, Rajesh Kumar Mallah wrote:
> The problem is that i want to know if i need a Hardware upgrade
> at the moment.
>
> Eg i have another table rfis which contains ~ .6 million records.

> SELECT count(*) from rfis where sender_uid > 0;

> Time: 117560.635 ms
>
> Which is approximate 4804 records per second. Is it an acceptable
> performance  on the hardware below:
>
> RAM: 2 GB
> DISKS: ultra160 , 10 K  , 18 GB
> Processor: 2* 2.0 Ghz Xeon

Hmm - doesn't seem good, does it? If you run it again, is it much faster 
(since the data should be cached then)? What does "vmstat 10" show while 
you're running the query?

One thing you should have done is read the performance tuning guide at:
  http://www.varlena.com/varlena/GeneralBits/Tidbits/index.php
The default values are very conservative, and you will need to change them.

> What kind of upgrades shoud be put on the server for it to become
> reasonable fast.

If you've only got one disk, then a second disk for OS/logging. Difficult to 
say more without knowing numbers of users/activity etc.

-- 
  Richard Huxton
  Archonet Ltd



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: select count(*)  very slow on an already vacuumed table.
  2004-04-14 17:53 select count(*)  very slow on an already vacuumed table. Rajesh Kumar Mallah <mallah@trade-india.com>
  2004-04-14 19:08 ` Re: select count(*)  very slow on an already vacuumed table. Richard Huxton <dev@archonet.com>
  2004-04-15 07:10   ` Re: select count(*)  very slow on an already vacuumed table. Rajesh Kumar Mallah <mallah@trade-india.com>
  2004-04-15 07:53     ` Re: select count(*)  very slow on an already vacuumed table. Richard Huxton <dev@archonet.com>
@ 2004-04-15 10:31       ` Rajesh Kumar Mallah <mallah@trade-india.com>
  0 siblings, 0 replies; 7+ messages in thread

From: Rajesh Kumar Mallah @ 2004-04-15 10:31 UTC (permalink / raw)
  To: Richard Huxton <dev@archonet.com>; +Cc: pgsql-performance

Richard Huxton wrote:

>On Thursday 15 April 2004 08:10, Rajesh Kumar Mallah wrote:
>  
>
>>The problem is that i want to know if i need a Hardware upgrade
>>at the moment.
>>
>>Eg i have another table rfis which contains ~ .6 million records.
>>    
>>
>
>  
>
>>SELECT count(*) from rfis where sender_uid > 0;
>>    
>>
>
>  
>
>>Time: 117560.635 ms
>>
>>Which is approximate 4804 records per second. Is it an acceptable
>>performance  on the hardware below:
>>
>>RAM: 2 GB
>>DISKS: ultra160 , 10 K  , 18 GB
>>Processor: 2* 2.0 Ghz Xeon
>>    
>>
>
>Hmm - doesn't seem good, does it? If you run it again, is it much faster 
>(since the data should be cached then)? What does "vmstat 10" show while 
>you're running the query?
>
>One thing you should have done is read the performance tuning guide at:
>  http://www.varlena.com/varlena/GeneralBits/Tidbits/index.php
>The default values are very conservative, and you will need to change them.
>  
>
Hi,

Thanks for the interest . my config are not the default ones.
i was running iostat while running the query. Looks like one
of the disks doesnt' go past a read performance of 20 ,000 KBytes/sec

while the other disk  it goes as high as 40,000 . What i am ding 
currently is
loading the table in both the disks and compare the table scan speeds.

The performance is definitely better in the newly loaded table in the other
disk . the load in server is 13 because i am simultaneously re-loading 
the data
in other table.


rt2=# SELECT count(*) from rfis where sender_uid > 0;
+--------+
| count  |
+--------+
| 564870 |
+--------+
(1 row)

Time: 10288.359 ms

rt2=#

shall post the comparitive details under normal load soon


regds
mallah.







>  
>
>>What kind of upgrades shoud be put on the server for it to become
>>reasonable fast.
>>    
>>
>
>If you've only got one disk, then a second disk for OS/logging. Difficult to 
>say more without knowing numbers of users/activity etc.
>
>  
>




^ permalink  raw  reply  [nested|flat] 7+ messages in thread


end of thread, other threads:[~2004-04-15 10:31 UTC | newest]

Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-04-14 17:53 select count(*)  very slow on an already vacuumed table. Rajesh Kumar Mallah <mallah@trade-india.com>
2004-04-14 19:08 ` Richard Huxton <dev@archonet.com>
2004-04-14 20:43   ` Rajesh Kumar Mallah <mallah@trade-india.com>
2004-04-15 07:10   ` Rajesh Kumar Mallah <mallah@trade-india.com>
2004-04-15 07:34     ` Rajesh Kumar Mallah <mallah@trade-india.com>
2004-04-15 07:53     ` Richard Huxton <dev@archonet.com>
2004-04-15 10:31       ` Rajesh Kumar Mallah <mallah@trade-india.com>

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