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 <[EMAIL PROTECTED]> 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 [EMAIL PROTECTED] Advanced Technologies Group (434)924-7171 Information Technology & Communication http://www.people.virginia.edu/~ys2n University of Virginia ---------------------------(end of broadcast)--------------------------- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match