X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (neptune.hub.org [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id C20E4D1B449 for ; Thu, 18 Dec 2003 03:59:56 +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 50374-09 for ; Wed, 17 Dec 2003 23:59:25 -0400 (AST) Received: from snipe.mail.pas.earthlink.net (snipe.mail.pas.earthlink.net [207.217.120.62]) by svr1.postgresql.org (Postfix) with ESMTP id 75BCDD1B44D for ; Wed, 17 Dec 2003 23:59:24 -0400 (AST) Received: from hsa218.pool032.at101.earthlink.net ([216.249.101.218] helo=shadovitzcmptr) by snipe.mail.pas.earthlink.net with smtp (Exim 3.33 #1) id 1AWpJW-00001w-00; Wed, 17 Dec 2003 19:59:02 -0800 Received: by localhost with Microsoft MAPI; Wed, 17 Dec 2003 19:55:01 -0800 Message-ID: <01C3C4D7.A7F88280.david@shadovitz.com> From: David Shadovitz Reply-To: "david@shadovitz.com" To: 'Dennis Bjorklund' , "'shridhar_daithankar@persistent.co.in'" , "'neilc@samurai.com'" Cc: "pgsql-performance@postgresql.org" Subject: Re: Why is restored database faster? Date: Wed, 17 Dec 2003 19:54:45 -0800 X-Mailer: Microsoft Internet E-mail/MAPI - 8.0.0.4211 MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200312/261 X-Sequence-Number: 5121 Dennis, Shridhar, and Neil, Thanks for your input. Here are my responses: I ran VACUUM FULL on the table in question. Although that did reduce "Pages" and "UnUsed", the "SELECT *" query is still much slower on this installation than in the new, restored one. Old server: # VACUUM FULL abc; VACUUM # VACUUM VERBOSE abc; NOTICE: --Relation abc-- NOTICE: Pages 1526: Changed 0, Empty 0; Tup 91528; Vac 0, Keep 0, UnUsed 32. Total CPU 0.07s/0.52u sec elapsed 0.60 sec. VACUUM New server: # VACUUM VERBOSE abc; NOTICE: --Relation abc-- NOTICE: Pages 1526: Changed 0, Empty 0; Tup 91528; Vac 0, Keep 0, UnUsed 0. Total CPU 0.02s/0.00u sec elapsed 0.02 sec. VACUUM max_fsm_pages is at its default value, 10000. People don't have the practice of dumping and restoring just for the purpose of improving performance, do they? Neil asked how much disk space the database directory takes on each machine. What directory is of interest? The whole thing takes up about 875 MB on each machine. -David