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

Re: performance for high-volume log insertion

From: Glenn Maynard <glennfmaynard(at)gmail(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Re: performance for high-volume log insertion
Date: 2009-04-22 19:33:21
Message-ID: bd36f99e0904221233n191d05abwada64ab8438fc5ae@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
On Wed, Apr 22, 2009 at 8:19 AM, Stephen Frost <sfrost(at)snowman(dot)net> wrote:
> Yes, as I beleive was mentioned already, planning time for inserts is
> really small.  Parsing time for inserts when there's little parsing that
> has to happen also isn't all *that* expensive and the same goes for
> conversions from textual representations of data to binary.
>
> We're starting to re-hash things, in my view.  The low-hanging fruit is
> doing multiple things in a single transaction, either by using COPY,
> multi-value INSERTs, or just multiple INSERTs in a single transaction.
> That's absolutely step one.

This is all well-known, covered information, but perhaps some numbers
will help drive this home.  40000 inserts into a single-column,
unindexed table; with predictable results:

separate inserts, no transaction: 21.21s
separate inserts, same transaction: 1.89s
40 inserts, 100 rows/insert: 0.18s
one 40000-value insert: 0.16s
40 prepared inserts, 100 rows/insert: 0.15s
COPY (text): 0.10s
COPY (binary): 0.10s

Of course, real workloads will change the weights, but this is more or
less the magnitude of difference I always see--batch your inserts into
single statements, and if that's not enough, skip to COPY.

-- 
Glenn Maynard

In response to

Responses

pgsql-performance by date

Next:From: davidDate: 2009-04-22 20:07:34
Subject: Re: performance for high-volume log insertion
Previous:From: Matthew WakelingDate: 2009-04-22 16:06:25
Subject: Re: GiST index performance

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