On Tue, Jul 17, 2012 at 6:30 AM, Craig Ringer <ringerc@ringerc.id.au> wrote:
> On 07/17/2012 01:56 AM, Jon Nelson wrote:
> 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.
It seems to me that if the insertion is done as a single statement it
wouldn't be a problem to collect up all btree insertions and apply
them before completing the statement. I'm not sure how much that would
help though. If the new rows have uniform distribution you end up
reading in the whole index anyway. Because indexes are not stored in
logical order you don't get to benefit from sequential I/O.
The lazy merging approach (the paper that Claudio linked) on the other
hand seems promising but a lot trickier to implement.
Regards,
Ants Aasma
--
Cybertec Schönig & Schönig GmbH
Gröhrmühlgasse 26
A-2700 Wiener Neustadt
Web: http://www.postgresql-support.de