On Wed, Apr 22, 2009 at 8:19 AM, Stephen Frost <sfrost@xxxxxxxxxxx> 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 -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
