X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 8207D8B9F5D for ; Wed, 9 Feb 2005 17:02:05 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 18840-01 for ; Wed, 9 Feb 2005 17:01:45 +0000 (GMT) Received: from smtp.lewczuk.com (118.224.149.195.tld.pl [195.149.224.118]) by svr1.postgresql.org (Postfix) with SMTP id D181D8BA066 for ; Wed, 9 Feb 2005 17:01:43 +0000 (GMT) Received: (qmail 31120 invoked by uid 700); 9 Feb 2005 17:01:39 -0000 Received: from linux.samar.pl (HELO ?192.168.1.17?) (marek@lewczuk.com@62.233.139.198) by 118.224.149.195.tld.pl with SMTP; 9 Feb 2005 17:01:38 -0000 Message-ID: <420A41F1.5070007@lewczuk.com> Date: Wed, 09 Feb 2005 18:01:37 +0100 From: Marek Lewczuk User-Agent: Mozilla Thunderbird 1.0 (Windows/20041206) X-Accept-Language: pl, en-us, en MIME-Version: 1.0 To: Shashi Gireddy Cc: pgsql-admin@postgresql.org Subject: Re: too slow References: <6B18E29D62790047B57F5B602D4B096EA1CE1A@hermes.cs.ua.edu> In-Reply-To: <6B18E29D62790047B57F5B602D4B096EA1CE1A@hermes.cs.ua.edu> Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.074 tagged_above=0 required=5 tests=FORGED_RCVD_HELO, RCVD_BY_IP X-Spam-Level: X-Archive-Number: 200502/72 X-Sequence-Number: 16240 Shashi Gireddy napisaƂ(a): > I recently migrated from MySql, The database size in mysql was 1.4GB (It is a static database). It generated a dump file (.sql) of size 8GB), It took 2days to import the whole thing into postgres. After all the response from postgres is a disaster. It took 40sec's to run a select count(logrecno) from sf10001; which generated a value 197569. And It took for ever time to display the table. How to optimize the database so that I can expect faster access to data. > > each table has 70 colsX197569 rows (static data), like that I have 40 tables, Everything static. > > system configuration: p4 2.8ghz 512mb ram os: xp postgres version: 8.0 First of all you should make VACUUM FULL ANALYZE for the all tables (http://www.postgresql.org/docs/8.0/interactive/sql-vacuum.html) - this should solve the problem. However you should also think about changing table structure, because PostgreSQL needs different indexes than MySQL. A few months ago I had the same problem - but after vacuuming, making proper indexes everything is working like a charm. Believe me that you can achieve the same speed - it is only a matter of good db structure and environment settings (http://www.postgresql.org/docs/8.0/interactive/runtime.html). Regards, ML