From: Rudolf van der Leeden <vanderleeden@logicunited.com>
To: pgsql-admin@postgresql.org
Cc: Rudolf van der Leeden <vanderleeden@logicunited.com>
Subject: Create Index Performance Issue
Date: Wed, 4 Jun 2008 11:43:24 +0200
Message-ID: <7EB14E30-23D1-49FF-AB57-E53A8CD3AE72@logicunited.com> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: vanderleeden@logicunited.com
Subject: Re: Create Index Performance Issue
In-Reply-To: <7EB14E30-23D1-49FF-AB57-E53A8CD3AE72@logicunited.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox