X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id A4D84D1D84D for ; Wed, 19 Nov 2003 01:37:40 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 60988-05 for ; Tue, 18 Nov 2003 21:37:12 -0400 (AST) Received: from trade-india.com (ns5.trade-india.com [66.234.10.13]) by svr1.postgresql.org (Postfix) with SMTP id A6370D1D630 for ; Tue, 18 Nov 2003 21:36:58 -0400 (AST) Received: (qmail 18951 invoked from network); 19 Nov 2003 01:38:05 -0000 Received: from unknown (HELO trade-india.com) (203.145.130.142) by ns5.trade-india.com with SMTP; 19 Nov 2003 01:38:05 -0000 Message-ID: <3FBC1ADA.4040709@trade-india.com> Date: Thu, 20 Nov 2003 07:07:30 +0530 From: Rajesh Kumar Mallah User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.4) Gecko/20030630 X-Accept-Language: en-us, en MIME-Version: 1.0 To: pgsql-performance@postgresql.org Subject: problem with select count(*) .. Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200311/148 X-Sequence-Number: 4708 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