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
