From: Andres Freund <andres@anarazel.de>
To: pgsql-performance@postgresql.org
Cc: Greg Smith <greg@2ndquadrant.com>
Cc: Rob Wultsch <wultsch@gmail.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Subject: Re: Building multiple indexes concurrently
Date: Wed, 17 Mar 2010 20:24:10 +0100
Message-ID: <201003172024.10538.andres@anarazel.de> (raw)
In-Reply-To: <4BA12328.4080803@2ndquadrant.com>
References: <2c5ef4e31003161804l5e32ceb9v6b7019118f30004d@mail.gmail.com>
<2c5ef4e31003170807x3f26dd77v57a1631094bc5270@mail.gmail.com>
<4BA12328.4080803@2ndquadrant.com>
On Wednesday 17 March 2010 19:44:56 Greg Smith wrote:
> 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).
I find it way much easier to believe such issues exist on a tables in
constrast to indexes. The likelihood to get sequential accesses on an index is
small enough on a big table to make it unlikely to matter much.
Whats your theory to make it matter much?
Andres
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: andres@anarazel.de, greg@2ndquadrant.com, wultsch@gmail.com, tgl@sss.pgh.pa.us
Subject: Re: Building multiple indexes concurrently
In-Reply-To: <201003172024.10538.andres@anarazel.de>
* 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