Message-ID: <4BA12328.4080803@2ndquadrant.com>
Date: Wed, 17 Mar 2010 14:44:56 -0400
From: Greg Smith <greg@2ndquadrant.com>
User-Agent: Thunderbird 2.0.0.23 (X11/20090817)
MIME-Version: 1.0
To: Rob Wultsch <wultsch@gmail.com>
CC: Tom Lane <tgl@sss.pgh.pa.us>, pgsql-performance@postgresql.org
Subject: Re: Building multiple indexes concurrently
References: <2c5ef4e31003161804l5e32ceb9v6b7019118f30004d@mail.gmail.com>
	<4BA0AAD1.5040508@2ndquadrant.com> <27969.1268836243@sss.pgh.pa.us>
	<2c5ef4e31003170807x3f26dd77v57a1631094bc5270@mail.gmail.com>
In-Reply-To: <2c5ef4e31003170807x3f26dd77v57a1631094bc5270@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Rob Wultsch wrote:
> On Wed, Mar 17, 2010 at 7:30 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>   
>> No, it's not optimistic in the least, at least not since we implemented
>> synchronized seqscans (in 8.3 or thereabouts).
>>     
> Where can I find details about this in the documentation?
>   

It's a behind the scenes optimization so it's not really documented on 
the user side very well as far as I know; easy to forget it's even there 
as I did this morning.  
http://j-davis.com/postgresql/syncscan/syncscan.pdf is a presentation 
covering it, and http://j-davis.com/postgresql/83v82_scans.html is also 
helpful.

While my pessimism on this part may have been overwrought, note the 
message interleaved on the list today with this discussion from Bob 
Lunney discussing the other issue I brought up:  "When using 8-way 
parallel restore against a six-disk RAID 10 group I found that table and 
index scan performance dropped by about 10x.  I/O performance was 
restored by either clustering the tables one at a time, or by dropping 
and restoring them one at a time.  The only reason I can come up with 
for this behavior is file fragmentation and increased seek times."  Now, 
Bob's situation may very well involve a heavy dose of table 
fragmentation from multiple active loading processes rather than index 
fragmentation, but this class of problem is common when trying to do too 
many things at the same time.  I'd hate to see you chase a short-term 
optimization (reduce total index built time) at the expense of long-term 
overhead (resulting indexes are not as efficient to scan).

-- 
Greg Smith  2ndQuadrant US  Baltimore, MD
PostgreSQL Training, Services and Support
greg@2ndQuadrant.com   www.2ndQuadrant.us