From: Jim C. Nasby <jnasby@pervasive.com>
To: Ed L. <pgsql@bluepolka.net>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: pg_dump index creation order
Date: Sun, 7 May 2006 12:08:10 -0500
Message-ID: <20060507170810.GL99570@pervasive.com> (raw)
In-Reply-To: <200605061915.55712.pgsql@bluepolka.net>
References: <200605061915.55712.pgsql@bluepolka.net>
On Sat, May 06, 2006 at 07:15:55PM -0600, Ed L. wrote:
> I'm watching a long, painfully slow 60GB load from pg_dump
> (8.1.2), and noticing it's jumping back and forth from different
> tables. I assume this is the index creation order showing up.
>
> Would it make more sense to have pg_dump dump indexes grouped by
> the table? That way, if a table got loaded into cache for one
> index creation, it might still be there for the immediatly
> following index creations on the same table...
It might for smaller tables that will fit in cache, but it depends on
how much memory is used for sorting. In fact, I think it would be best
to add the indexes immediately after loading the table with data.
This won't help with adding indexes on large tables though, unless
the indexes were created simultaneously, and even that might not be a
win.
It would be a win to add some constraints at the same time, but RI can't
be added until all tables are indexed.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
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-performance@postgresql.org
Cc: jnasby@pervasive.com, pgsql@bluepolka.net
Subject: Re: pg_dump index creation order
In-Reply-To: <20060507170810.GL99570@pervasive.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