Skip site navigation (1) Skip section navigation (2)

problem with select count(*) ..

From: Rajesh Kumar Mallah <mallah(at)trade-india(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: problem with select count(*) ..
Date: 2003-11-20 01:37:30
Message-ID: 3FBC1ADA.4040709@trade-india.com (view raw or flat)
Thread:
Lists: pgsql-performance
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



Responses

pgsql-performance by date

Next:From: Tom LaneDate: 2003-11-20 04:39:47
Subject: Re: Backup/restore of pg_statistics
Previous:From: Bruce MomjianDate: 2003-11-19 19:42:20
Subject: Re: duration logging setting in 7.4

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group