pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedCreate Index Performance Issue
4+ messages / 3 participants
[nested] [flat]
* Create Index Performance Issue
@ 2008-06-04 09:43 Rudolf van der Leeden <vanderleeden@logicunited.com>
2008-06-04 09:58 ` Re: Create Index Performance Issue Tino Schwarze <postgresql@tisc.de>
2008-06-04 14:45 ` Re: Create Index Performance Issue Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 4+ messages in thread
From: Rudolf van der Leeden @ 2008-06-04 09:43 UTC (permalink / raw)
To: pgsql-admin; +Cc: Rudolf van der Leeden <vanderleeden@logicunited.com>
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Create Index Performance Issue
2008-06-04 09:43 Create Index Performance Issue Rudolf van der Leeden <vanderleeden@logicunited.com>
@ 2008-06-04 09:58 ` Tino Schwarze <postgresql@tisc.de>
1 sibling, 0 replies; 4+ messages in thread
From: Tino Schwarze @ 2008-06-04 09:58 UTC (permalink / raw)
To: pgsql-admin
On Wed, Jun 04, 2008 at 11:43:24AM +0200, Rudolf van der Leeden wrote:
> 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
Maybe there are some background processes running?
Tino.
--
"What we resist, persists." (Zen saying)
www.craniosacralzentrum.de
www.forteego.de
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Create Index Performance Issue
2008-06-04 09:43 Create Index Performance Issue Rudolf van der Leeden <vanderleeden@logicunited.com>
@ 2008-06-04 14:45 ` Tom Lane <tgl@sss.pgh.pa.us>
2008-06-06 19:54 ` Re: Create Index Performance Issue Rudolf van der Leeden <vanderleeden@logicunited.com>
1 sibling, 1 reply; 4+ messages in thread
From: Tom Lane @ 2008-06-04 14:45 UTC (permalink / raw)
To: Rudolf van der Leeden <vanderleeden@logicunited.com>; +Cc: pgsql-admin
Rudolf van der Leeden <vanderleeden@logicunited.com> writes:
> [ different times to build a varchar index on allegedly identical
> installations ]
Perhaps one is using C locale and the other is not? strcmp() vs
strcoll() is a pretty big hit. In general it'd be worth dumping
out the whole contents of pg_settings and diffing those two files
to see if you missed any configuration differences.
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Create Index Performance Issue
2008-06-04 09:43 Create Index Performance Issue Rudolf van der Leeden <vanderleeden@logicunited.com>
2008-06-04 14:45 ` Re: Create Index Performance Issue Tom Lane <tgl@sss.pgh.pa.us>
@ 2008-06-06 19:54 ` Rudolf van der Leeden <vanderleeden@logicunited.com>
0 siblings, 0 replies; 4+ messages in thread
From: Rudolf van der Leeden @ 2008-06-06 19:54 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin
Tom,
your hint was 100% correct. BINGO!
The setting of lc_locale and lc_ctype was 'en_us' instead of 'C' as
on the other machine.
Now it works perfectly OK and with identical execution times on both
machines.
Thanks for your help and best regards,
Rudolf VanderLeeden
Am 04.06.2008 um 16:45 schrieb Tom Lane:
> Perhaps one is using C locale and the other is not? strcmp() vs
> strcoll() is a pretty big hit. In general it'd be worth dumping
> out the whole contents of pg_settings and diffing those two files
> to see if you missed any configuration differences.
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2008-06-06 19:54 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-06-04 09:43 Create Index Performance Issue Rudolf van der Leeden <vanderleeden@logicunited.com>
2008-06-04 09:58 ` Tino Schwarze <postgresql@tisc.de>
2008-06-04 14:45 ` Tom Lane <tgl@sss.pgh.pa.us>
2008-06-06 19:54 ` Rudolf van der Leeden <vanderleeden@logicunited.com>
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