Received: from localhost (unknown [200.46.204.183]) by developer.postgresql.org (Postfix) with ESMTP id C67822E00C1 for ; Wed, 4 Jun 2008 06:49:03 -0300 (ADT) Received: from developer.postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 49226-10 for ; Wed, 4 Jun 2008 06:48:57 -0300 (ADT) X-Greylist: delayed 00:05:32.96214 by SQLgrey-1.7.6 Received: from enron.intra.logicunited.com (ext0.logicunited.com [82.135.98.170]) by developer.postgresql.org (Postfix) with ESMTP id 65EC22E00BA for ; Wed, 4 Jun 2008 06:49:00 -0300 (ADT) Received: from [192.168.140.17] (phonoline.intra.logicunited.com [192.168.140.17]) (using TLSv1 with cipher AES128-SHA (128/128 bits)) (No client certificate requested) by enron.intra.logicunited.com (Postfix) with ESMTP id 0767A1C01A18; Wed, 4 Jun 2008 11:42:31 +0200 (CEST) Mime-Version: 1.0 (Apple Message framework v753.1) Content-Type: text/plain; charset=US-ASCII; delsp=yes; format=flowed Message-Id: <7EB14E30-23D1-49FF-AB57-E53A8CD3AE72@logicunited.com> Cc: Rudolf van der Leeden Content-Transfer-Encoding: 7bit From: Rudolf van der Leeden Subject: Create Index Performance Issue Date: Wed, 4 Jun 2008 11:43:24 +0200 To: pgsql-admin@postgresql.org X-Mailer: Apple Mail (2.753.1) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200806/23 X-Sequence-Number: 29361 Hi, I'm currently testing a 25G Postgres 8.3.0 database on Apple Xserve/ Intel with Mac OSX 10.5 (Leopard). There are two identical machines with identical configurations (hw and sw). Running a pg_restore of a binary backup file (3.8 GB) on both machines gives the following results: Machine A : 90 minutes Machine B: 60 minutes The postgres server logfiles show the cause of the time difference: The SQL query CREATE INDEX login_session_identifier ON login_session USING btree (identifier) consumes very different execution times: Machine A : 1905 seconds Machine B: 292 seconds The difference of 27 minutes explains the pg_restore behaviour. The same effect can be demonstrated easily by just running the SQL query manually. Some database infos: Table login_session : 33,996,225 rows, 10.6 GB on disk Column identifier : character varying (255) Index login_session_identifier : 1.3 GB on disk Postgres configuration file is of course the same on both machines. The maintenance_work_mem is set to 128MB . I played with this parameter setting it up to 2 GB. This didn't change the execution times essentially. With large values the times went a little up. I would appreciate any advices on how to investigate this problem any further. Ideas are very welcome. Thanks in advance, Rudolf VanderLeeden Logic United GmbH, Germany vanderleeden@logicunited.com