Message-ID: <4D40795A.8000103@agliodbs.com>
Date: Wed, 26 Jan 2011 11:43:22 -0800
From: Josh Berkus <josh@agliodbs.com>
User-Agent: Mozilla/5.0 (Macintosh; U; Intel Mac OS X 10.5; en-US;
	rv:1.9.1b3pre) Gecko/20090223 Thunderbird/3.0b2
MIME-Version: 1.0
To: Tom Lane <tgl@sss.pgh.pa.us>
CC: pgsql-bugs@postgresql.org
Subject: Re: Multicolun index creation never completes on 9.0.1/solaris
References: <4D3F2D10.1020905@agliodbs.com> <21735.1295990515@sss.pgh.pa.us>
	<4D3F86D9.9080200@agliodbs.com> <14452.1296010153@sss.pgh.pa.us>
	<4D4069EF.6040603@agliodbs.com> <23139.1296069017@sss.pgh.pa.us>
In-Reply-To: <23139.1296069017@sss.pgh.pa.us>
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 7bit

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