Skip site navigation (1) Skip section navigation (2)

Suggestion for concurrent index creation using a single full scan operation

From: Tim Kane <tim(dot)kane(at)gmail(dot)com>
To: pgsql-hackers(at)postgresql(dot)org
Subject: Suggestion for concurrent index creation using a single full scan operation
Date: 2013-07-23 12:06:26
Message-ID: CADVWZZJ5AS=XVrDwfTQqQP_V1+_fTYcZhq=d5CbCXoALCjObbg@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-hackers
Hi all,

I haven't given this a lot of thought, but it struck me that when
rebuilding tables (be it for a restore process, or some other operational
activity) - there is more often than not a need to build an index or two,
sometimes many indexes, against the same relation.

It strikes me that in order to build just one index, we probably need to
perform a full table scan (in a lot of cases).   If we are building
multiple indexes sequentially against that same table, then we're probably
performing multiple sequential scans in succession, once for each index.

Could we architect a mechanism that allowed multiple index creation
statements to execute concurrently, with all of their inputs fed directly
from a single sequential scan against the full relation?

From a language construct point of view, this may not be trivial to
implement for raw/interactive SQL - but possibly this is a candidate for
the custom format restore?

I presume this would substantially increase the memory overhead required to
build those indexes, though the performance gains may be advantageous.

Feel free to shoot holes through this :)

Apologies in advance if this is not the correct forum for suggestions..

Responses

pgsql-hackers by date

Next:From: Robert HaasDate: 2013-07-23 12:32:08
Subject: Re: improve Chinese locale performance
Previous:From: Amit KapilaDate: 2013-07-23 12:05:13
Subject: Re: Performance Improvement by reducing WAL for Update Operation

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group