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