Re: copying databases w/ indexes

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