Re: Building multiple indexes concurrently - Mailing list pgsql-performance

From Greg Smith
Subject Re: Building multiple indexes concurrently
Date
Msg-id 4BA12328.4080803@2ndquadrant.com
Whole thread Raw
In response to Re: Building multiple indexes concurrently  (Rob Wultsch <wultsch@gmail.com>)
Responses Re: Building multiple indexes concurrently  (Andres Freund <andres@anarazel.de>)
List pgsql-performance
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


pgsql-performance by date:

Previous
From: Bob Lunney
Date:
Subject: Re: Block at a time ...
Next
From: Justin Pitts
Date:
Subject: Re: Testing FusionIO