pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Jeff Davis <pgsql@j-davis.com>
To: Pavan Deolasee <pavan.deolasee@gmail.com>
Cc: Douglas J Hunley <doug@hunley.homeip.net>
Cc: pgsql-performance@postgresql.org
Subject: Re: 7 hrs for a pg_restore?
Date: Wed, 20 Feb 2008 10:04:47 -0800
Message-ID: <1203530687.3846.151.camel@dogma.ljc.laika.com> (raw)
In-Reply-To: <2e78013d0802200101q44dbe6edvac067c39629ed9cd@mail.gmail.com>
References: <200802191303.58840.doug@hunley.homeip.net>
	<1203445403.3846.86.camel@dogma.ljc.laika.com>
	<2e78013d0802200101q44dbe6edvac067c39629ed9cd@mail.gmail.com>

On Wed, 2008-02-20 at 14:31 +0530, Pavan Deolasee wrote:
> I think it would be interesting if we can build these indexes in parallel.
> Each index build requires a seq scan on the table. If the table does
> not fit in shared buffers, each index build would most likely result
> in lots of IO.

He's already said that his I/O usage was not the problem. For one thing,
he has 8GB of memory for a 5GB dataset.

Even when the table is much larger than memory, what percentage of the
time is spent on the table scan? A table scan is O(N), whereas an index
build is O(N logN). If you combine that with expensive comparisons, e.g.
for localized text, then I would guess that the index building itself
was much more expensive than the scans themselves.

However, building indexes in parallel would allow better CPU
utilization.

> One option would be to add this facility to the backend so that multiple
> indexes can be built with a single seq scan of the table. In theory, it
> should be possible, but might be tricky given the way index build works
> (it calls respective ambuild method to build the index which internally
> does the seq scan).

I don't think that this would be necessary, because (as you say below)
the synchronized scan facility should already handle this.

> Other option is to make pg_restore multi-threaded/processed. The
> synchronized_scans facility would then synchronize the multiple heap
> scans. ISTM that if we can make pg_restore mult-processed, then
> we can possibly add more parallelism to the restore process.

I like this approach more. I think that pg_restore is the right place to
do this, if we can make the options reasonably simple enough to use.

See:

http://archives.postgresql.org/pgsql-hackers/2008-02/msg00699.php

Regards,
	Jeff Davis




view thread (42+ messages)  latest in thread

Message-ID: <1203530687.3846.151.camel@dogma.ljc.laika.com>
Permalink:  ../1203530687.3846.151.camel@dogma.ljc.laika.com/
Also on:    postgresql.org/message-id/1203530687.3846.151.camel@dogma.ljc.laika.com

 · 

reply

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: pgsql@j-davis.com, pavan.deolasee@gmail.com, doug@hunley.homeip.net
  Subject: Re: 7 hrs for a pg_restore?
  In-Reply-To: <1203530687.3846.151.camel@dogma.ljc.laika.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