From: Craig Ringer <ringerc@ringerc.id.au>
To: Jon Nelson <jnelson+pgsql@jamponi.net>
Cc: pgsql-performance@postgresql.org
Subject: Re: very very slow inserts into very large table
Date: Tue, 17 Jul 2012 11:30:56 +0800
Message-ID: <5004DC70.7000808@ringerc.id.au> (raw)
In-Reply-To: <CAKuK5J2i5XF1828+fB6Z=Yfkxz1iq9OiR-AkcAAuDA+kgfQ7LQ@mail.gmail.com>
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>
On 07/17/2012 01:56 AM, Jon Nelson wrote:
> What is the greater lesson to take away, here? If you are working with
> data that is larger (substantially larger) than available memory, is
> the architecture and design of postgresql such that the only real
> approach is some type of data partitioning? 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.
To perform reasonably well, Pg would need to be able to defer index
updates when bulk-loading data in a single statement (or even
transaction), then apply them when the statement finished or transaction
committed. Doing this at a transaction level would mean you'd need a way
to mark indexes as 'lazily updated' and have Pg avoid using them once
they'd been dirtied within a transaction. No such support currently
exists, and it'd be non-trivial to implement, especially since people
loading huge amounts of data often want to do it with multiple
concurrent sessions. You'd need some kind of 'DISABLE INDEX' and 'ENABLE
INDEX' commands plus a transactional backing table of pending index updates.
Not simple.
Right now, Pg is trying to keep the index consistent the whole time.
That involves moving a heck of a lot of data around - repeatedly.
Setting a lower FILLFACTOR on your indexes can give Pg some breathing
room here, but only a limited amount, and at the cost of reduced scan
efficiency.
--
Craig Ringer
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: ringerc@ringerc.id.au, jnelson+pgsql@jamponi.net
Subject: Re: very very slow inserts into very large table
In-Reply-To: <5004DC70.7000808@ringerc.id.au>
* 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