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(at)virginia(dot)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(at)virginia(dot)edu Advanced Technologies Group
(434)924-7171 Information Technology & Communication
http://www.people.virginia.edu/~ys2n University of Virginia