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

Re: very very slow inserts into very large table

From: Samuel Gendler <sgendler(at)ideasculptor(dot)com>
To: Mark Thornton <mthornton(at)optrak(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org, jnelson+pgsql(at)jamponi(dot)net
Subject: Re: very very slow inserts into very large table
Date: 2012-07-16 17:35:32
Message-ID: CAEV0TzDnzop=dZMwynDzPhukiNkksZfW7dzN4O-co43ZTWchNw@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
On Mon, Jul 16, 2012 at 7:06 AM, Mark Thornton <mthornton(at)optrak(dot)com> wrote:

>
>>  Every insert updates four indexes, so at least 3 of those will be in
> random order. The indexes don't fit in memory, so all those updates will
> involve reading most of the relevant b-tree pages from disk (or at least
> the leaf level). A total of 10ms of random read from disk (per inserted
> row) wouldn't surprise me ... which adds up to more than 10 days for your
> 93 million rows.


Which is the long way of saying that you will likely benefit from
partitioning that table into a number of smaller tables, especially if
queries on that table tend to access only a subset of the data that can be
defined to always fit into a smaller number of partitions than the total.
 At the very least, inserts will be faster because individual indexes will
be smaller.  But unless all queries can't be constrained to fit within a
subset of partitions, you'll also see improved performance on selects.

--sam

In response to

Responses

pgsql-performance by date

Next:From: Jon NelsonDate: 2012-07-16 17:56:12
Subject: Re: very very slow inserts into very large table
Previous:From: Tom LaneDate: 2012-07-16 16:57:39
Subject: Re: [PERFORM] DELETE vs TRUNCATE explanation

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