Re: copying databases w/ indexes - Mailing list pgsql-admin

From Yuji Shinozaki
Subject Re: copying databases w/ indexes
Date
Msg-id Pine.LNX.4.44.0308141536460.11973-100000@atg2000.itc.virginia.edu
Whole thread Raw
In response to Re: copying databases w/ indexes  (Tom Lane <tgl@sss.pgh.pa.us>)
List pgsql-admin
See my other reply, but the problem was that I had a foreign key
constraint between an integer and varchar. (The column was a varchar and
the foreign key was an integer field).  Seems to me postgres shouldn't
have allowed me to do this at all, but I guess it did a slow automatic
type conversion.  So, reindexing was deathly slow.  It also explains the
fact that inserts into this table were getting increasingly slow as the
table grew.

I manually changed the type in the dump and restoring takes about 20
minutes instead of 12 hours!  Phew!

yuji
----


On Thu, 14 Aug 2003, Tom Lane wrote:

> Yuji Shinozaki <ys2n@virginia.edu> writes:
> > Then the problem is that
> > reindexing takes a very long time. One particular index is taking on the
> > order of 12 hours to finish.
>
> Seems like a lot.  What is the index definition exactly, and how many
> rows in the table?  If it's a btree, have you tried increasing sort_mem
> in the restore session?
>
>             regards, tom lane
>

Yuji Shinozaki                            Computer Systems Senior Engineer
ys2n@virginia.edu            Advanced Technologies Group
(434)924-7171                Information Technology & Communication
http://www.people.virginia.edu/~ys2n    University of Virginia



pgsql-admin by date:

Previous
From: Tom Lane
Date:
Subject: Re: copying databases w/ indexes
Next
From: "jorge aleman"
Date:
Subject: ...