Received: from makus.postgresql.org (makus.postgresql.org [98.129.198.125])
	by mail.postgresql.org (Postfix) with ESMTP id A2C2855C5A5
	for <pgsql-performance@postgresql.org>;
	Mon, 16 Jul 2012 15:59:26 -0300 (ADT)
Received: from mail-ee0-f46.google.com ([74.125.83.46])
	by makus.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <mthornton@optrak.com>) id 1SqqVx-0006S8-3K
	for pgsql-performance@postgresql.org; Mon, 16 Jul 2012 18:59:25 +0000
Received: by eekc13 with SMTP id c13so301466eek.19
	for <pgsql-performance@postgresql.org>;
	Mon, 16 Jul 2012 11:59:10 -0700 (PDT)
X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed;
	d=google.com; s=20120113;
	h=message-id:date:from:user-agent:mime-version:to:cc:subject
	:references:in-reply-to:content-type:x-gm-message-state;
	bh=8hgtVmMZrP7UYHTLcHFvQbZPTjKooTs7i6tMngrJelU=;
	b=MUGIkEClaQqoCrJCyo2vJjKUNvDH9AlU7TO8eK4Uwpi8dH7p5Qqm2zYPM3Erp8pM9p
	c7QE9vnxCXJBP64FR2pKEoW23FQNPvdZ0d3bkk2wRLYH3v+SLJML2RrHPQ9kS4gx/m5k
	ebEPSasLMmZmITyG15w+3mGwrV0AwIMtvzKJ8cTBxxc0zfWA+0unvwvSGorL4wk2GeF6
	ii23LTozYMy8Me5a7BKDlZKqgC6GVvbkue/wnRGSxKClebvpbPMDRVrJ1iI+hvLrNzgT
	ZBqFZdZXFMBtZyVnHBGsUKsHq/d42CHCs+DVvcedXwM8ycnIww6mnjYWopC+ncpHkBqo
	GLBw==
Received: by 10.14.175.130 with SMTP id z2mr10636307eel.0.1342465150283;
	Mon, 16 Jul 2012 11:59:10 -0700 (PDT)
Received: from [192.168.0.110]
	(cpc4-ware5-2-0-cust97.9-1.cable.virginmedia.com. [86.5.172.98])
	by mx.google.com with ESMTPS id c1sm21182839eeo.5.2012.07.16.11.59.08
	(version=SSLv3 cipher=OTHER); Mon, 16 Jul 2012 11:59:09 -0700 (PDT)
Message-ID: <5004647B.3090308@optrak.com>
Date: Mon, 16 Jul 2012 19:59:07 +0100
From: Mark Thornton <mthornton@optrak.com>
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
	rv:13.0) Gecko/20120615 Thunderbird/13.0.1
MIME-Version: 1.0
To: Jon Nelson <jnelson+pgsql@jamponi.net>
CC: pgsql-performance@postgresql.org
Subject: Re: very very slow inserts into very large table
References: 
 <CAKuK5J1wY9NoZHxOaTf+=WNhgfn1uEU19Mw5dLvZh_NWpc8+tw@mail.gmail.com>
	<50041FD1.5070002@optrak.com>
	<CAEV0TzDnzop=dZMwynDzPhukiNkksZfW7dzN4O-co43ZTWchNw@mail.gmail.com>
	<CAKuK5J2i5XF1828+fB6Z=Yfkxz1iq9OiR-AkcAAuDA+kgfQ7LQ@mail.gmail.com>
In-Reply-To: 
 <CAKuK5J2i5XF1828+fB6Z=Yfkxz1iq9OiR-AkcAAuDA+kgfQ7LQ@mail.gmail.com>
Content-Type: multipart/alternative;
	boundary="------------050205010602070204050708"
X-Gm-Message-State: 
 ALoCoQlVYm4sOFd79fxyo1Svn1L7PQAvrbNL8iC3c9cDtO66CPSesCrm2F6XWdwoZ7SRFow8ISsY
X-Pg-Spam-Score: -2.6 (--)
X-Archive-Number: 201207/198
X-Sequence-Number: 47379

This is a multi-part message in MIME format.
--------------050205010602070204050708
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

On 16/07/12 18:56, Jon Nelson wrote:
> It is not my intent to
> insult or even disparage my favorite software, but it took less time
> to*build*  the indices for 550GB of data than it would have to insert
> 1/20th as much. That doesn't seem right.
My explanation would apply to many databases, not just Postgres.

To speed up the insert there are a number of possible approaches:

1. Partition the data and then sort the temporary table into groups 
based on the partitioning. Best of all if all the new data goes into a 
single partition.

2. Drop the indexes before insert and rebuild afterwards.

3. Reduce the number of indexes. If you only have one index, you can 
sort the data to be inserted in the natural order of the index. If you 
must have more than one index you could still sort the new data in the 
order of one of them to obtain a modest improvement in locality.

4. The most efficient way for the database itself to do the updates 
would be to first insert all the data in the table, and then update each 
index in turn having first sorted the inserted keys in the appropriate 
order for that index.

Mark



--------------050205010602070204050708
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 7bit

<html>
  <head>
    <meta content="text/html; charset=UTF-8" http-equiv="Content-Type">
  </head>
  <body bgcolor="#FFFFFF" text="#000000">
    <div class="moz-cite-prefix">On 16/07/12 18:56, Jon Nelson wrote:<br>
    </div>
    <blockquote
cite="mid:CAKuK5J2i5XF1828+fB6Z=Yfkxz1iq9OiR-AkcAAuDA+kgfQ7LQ@mail.gmail.com"
      type="cite">
      <pre wrap="">It is not my intent to
insult or even disparage my favorite software, but it took less time
to <b class="moz-txt-star"><span class="moz-txt-tag">*</span>build<span class="moz-txt-tag">*</span></b> the indices for 550GB of data than it would have to insert
1/20th as much. That doesn't seem right.</pre>
    </blockquote>
    My explanation would apply to many databases, not just Postgres.<br>
    <br>
    To speed up the insert there are a number of possible approaches:<br>
    <br>
    1. Partition the data and then sort the temporary table into groups
    based on the partitioning. Best of all if all the new data goes into
    a single partition.<br>
    <br>
    2. Drop the indexes before insert and rebuild afterwards.<br>
    <br>
    3. Reduce the number of indexes. If you only have one index, you can
    sort the data to be inserted in the natural order of the index. If
    you must have more than one index you could still sort the new data
    in the order of one of them to obtain a modest improvement in
    locality.<br>
    <br>
    4. The most efficient way for the database itself to do the updates
    would be to first insert all the data in the table, and then update
    each index in turn having first sorted the inserted keys in the
    appropriate order for that index.<br>
    <br>
    Mark<br>
    <br>
    <br>
  </body>
</html>

--------------050205010602070204050708--