Tom,
> [ raised eyebrow... ] Those numbers are the differences between two
> gettimeofday() readings. It's really really hard to believe that that's
> wrong, unless there's something seriously wrong with your machine.
Actually, you're right ... I don't know what time the run27 message was
posted. It's possible that it finished run27 at 9pm last night ...
Oh! Actually, it only *did* 27 runs. So it actually completed building
the index. I'd expected trace_sort to give me some kind of completion
message; apologies for not checking all screen windows.
So, why didn't the index build complete (after more than 36 hours) when
I ran it as part of pg_restore? I guess this goes back to being a
pg_restore problem and not an index build problem.
> FWIW, I did a test last night on the time to sort some random
> (varchar, timestamptz) data using git HEAD. I didn't have enough disk
> space to sort 1.4 billion rows, but I tested with 200 million rows and
> 1GB maintenance_work_mem, and was able to create an index in 2424
> seconds (~40 minutes) --- this on a pretty generic desktop machine.
> I don't see a reason to think the runtime for 1.4 billion would have
> been over 5 hours. The log output for my test looked like
2+ hours is then still very slow considering the machine I'm on compared
to yours. I wonder if we maybe should be using GCC instead of SunCC.
Oh, that's why: it's a SPARC processor. Slowness explained then.
> Your machine seems to be dumping a run about once every 250-300 seconds,
> which is about half the speed of mine, which is a bit odd if it's big
> iron. (I wonder whether you have a non-C locale selected ...)
See above.
So, as usual, I've completely mislocated the bug. I'll need to rerun
the pg_restore and actually diagnose *that*.
--
-- Josh Berkus
PostgreSQL Experts Inc.
http://www.pgexperts.com