Re: Creating multiple indexes in one table scan.

On Thu, May 24, 2012 at 11:22 AM, Stephen Frost <sfrost(at)snowman(dot)net> wrote:

> * Gurjeet Singh (singh(dot)gurjeet(at)gmail(dot)com) wrote:
> > Bruce points out the even simpler case is to build several indexes in
> > parallel over the same scan.
> >
> > I thought I had posted a patch to that effect long back, but upon
> searching
> > my emails apparently I forgot about the patch.
> >
> > Attached is the patch that I developed in Nov. 2010, so expect a lot of
> bit
> > rot. I had tried to make it elegant, but I have to admit its a hack. This
> > patch does not imply that it is using any kind of parallelism, the
> context
> > in which that above statement was made. It just helps to avoid scanning
> the
> > same relation multiple times. I performed some tests on it and AFAICR,
> this
> > did not produce a net win. But those tests may have been performed in a
> > virtual machine and not on a bare metal, I forget.
>
> I'm not too surprised that it didn't help all that much since you're
> doing everything in one backend. My guess at what Bruce was talking
> about is being able to actually have multiple CREATE INDEX's going in
> parallel in different backends. If they didn't all grab an
> AccessExclusive lock, wouldn't they be able to use the same ring buffer
> to read through the table anyway? And with CREATE INDEX CONCURRENTLY,
> isn't this supported already?
>
> I haven't tried this yet, but it sure seems like something we could
> probably already claim to support..
>

It'd be great if one of standard utilities like pg_restore supported this,
by spawning every concurrent index build in separate backends. Just a
thought.

--
Gurjeet Singh
EnterpriseDB Corporation
The Enterprise PostgreSQL Company