pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedpg_dump index creation order
3+ messages / 2 participants
[nested] [flat]
* pg_dump index creation order
@ 2006-05-07 01:15 Ed L. <pgsql@bluepolka.net>
0 siblings, 2 replies; 3+ messages in thread
From: Ed L. @ 2006-05-07 01:15 UTC (permalink / raw)
To: pgsql-performance
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...
Ed
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: pg_dump index creation order
@ 2006-05-07 03:09 Ed L. <pgsql@bluepolka.net>
parent: Ed L. <pgsql@bluepolka.net>
1 sibling, 0 replies; 3+ messages in thread
From: Ed L. @ 2006-05-07 03:09 UTC (permalink / raw)
To: pgsql-performance
On Saturday May 6 2006 7:15 pm, 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...
And would same idea work for ordering of constraint adding...?
Ed
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: pg_dump index creation order
@ 2006-05-07 17:08 Jim C. Nasby <jnasby@pervasive.com>
parent: Ed L. <pgsql@bluepolka.net>
1 sibling, 0 replies; 3+ messages in thread
From: Jim C. Nasby @ 2006-05-07 17:08 UTC (permalink / raw)
To: Ed L. <pgsql@bluepolka.net>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2006-05-07 17:08 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-05-07 01:15 pg_dump index creation order Ed L. <pgsql@bluepolka.net>
2006-05-07 03:09 ` Ed L. <pgsql@bluepolka.net>
2006-05-07 17:08 ` Jim C. Nasby <jnasby@pervasive.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