Re: [PERFORM] problem with select count(*) ..

[Bruno Wolff III]

On Thu, Nov 20, 2003 at 07:07:30 +0530,
  Rajesh Kumar Mallah [EMAIL PROTECTED] wrote:
 
 If i dump and reload the performance improves and it takes  1 sec. This
 is what i have been doing since the upgrade. But its not a solution.
 
 The Vacuum full is at the end of a loading batch SQL file which makes lot of
 insert , deletes and updates.

If a dump and reload fixes your problem, most likely you have a lot of
dead tuples in the table. You might need to run vacuum more often.
You might have an open transaction that is preventing vacuum full
from cleaning up the table.

---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

[PERFORM] problem with select count(*) ..

[Rajesh Kumar Mallah]

Ever Since i upgraded to 7.4RC2 i am facing problem 
with select count(*) . In 7.3 the problem was not there
select count(*) from data_bank.profiles used to return almost
instantly , but in 7.4

explain analyze SELECT count(*) from data_bank.profiles;
 QUERY PLAN
---
Aggregate  (cost=48361.30..48361.30 rows=1 width=0) (actual time=23456.870..23456.871 
rows=1 loops=1)
  -  Seq Scan on profiles  (cost=0.00..47431.84 rows=371784 width=0) (actual 
time=12174.999..23262.823 rows=123928 loops=1)
Total runtime: 23458.460 ms
(3 rows)
tradein_clients=#

If i dump and reload the performance improves and it takes  1 sec. This
is what i have been doing since the upgrade. But its not a solution.
The Vacuum full is at the end of a loading batch SQL file which makes lot of
insert , deletes and updates.
Regds
Mallah.




VACUUM FULL VERBOSE ANALYZE data_bank.profiles;
 INFO:  vacuuming data_bank.profiles
 INFO:  profiles: found 430524 removable, 371784 nonremovable row versions in 43714 
pages
 INFO:  index profiles_pincode now contains 371784 row versions in 3419 pages
 INFO:  index profiles_city now contains 371784 row versions in 3471 pages
 INFO:  index profiles_branch now contains 371784 row versions in 2237 pages
 INFO:  index profiles_area_code now contains 371784 row versions in 2611 pages
 INFO:  index profiles_source now contains 371784 row versions in 3165 pages
 INFO:  index co_name_index_idx now contains 371325 row versions in 3933 pages
 INFO:  index address_index_idx now contains 371490 row versions in 4883 pages
 INFO:  index profiles_exp_cat now contains 154836 row versions in 2181 pages
 INFO:  index profiles_imp_cat now contains 73678 row versions in 1043 pages
 INFO:  index profiles_manu_cat now contains 87124 row versions in 1201 pages
 INFO:  index profiles_serv_cat now contains 19340 row versions in 269 pages
 INFO:  index profiles_pid now contains 371784 row versions in 817 pages
 INFO:  index profiles_pending_branch_id now contains 0 row versions in 1 pages
 INFO:  profiles: moved 0 row versions, truncated 43714 to 43714 pages
 INFO:  vacuuming pg_toast.pg_toast_67748379
 INFO:  pg_toast_67748379: found 0 removable, 74 nonremovable row versions in 17 
pages
 INFO:  index pg_toast_67748379_index now contains 74 row versions in 2 pages
 INFO:  pg_toast_67748379: moved 1 row versions, truncated 17 to 17 pages
 INFO:  index pg_toast_67748379_index now contains 74 row versions in 2 pages
 INFO:  analyzing data_bank.profiles
 INFO:  profiles: 43714 pages, 3000 rows sampled, 3634 estimated total rows
VACUUM
Time: 1001525.19 ms


---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
 subscribe-nomail command to [EMAIL PROTECTED] so that your
 message can get through to the mailing list cleanly