From: Greg Smith <greg@2ndquadrant.com>
To: Rob Wultsch <wultsch@gmail.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-performance@postgresql.org
Subject: Re: Building multiple indexes concurrently
Date: Wed, 17 Mar 2010 14:44:56 -0400
Message-ID: <4BA12328.4080803@2ndquadrant.com> (raw)
In-Reply-To: <2c5ef4e31003170807x3f26dd77v57a1631094bc5270@mail.gmail.com>
References: <2c5ef4e31003161804l5e32ceb9v6b7019118f30004d@mail.gmail.com>
<4BA0AAD1.5040508@2ndquadrant.com>
<27969.1268836243@sss.pgh.pa.us>
<2c5ef4e31003170807x3f26dd77v57a1631094bc5270@mail.gmail.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: greg@2ndquadrant.com, wultsch@gmail.com, tgl@sss.pgh.pa.us
Subject: Re: Building multiple indexes concurrently
In-Reply-To: <4BA12328.4080803@2ndquadrant.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox