From: Joe Conway <mail@joeconway.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Patches (PostgreSQL) <pgsql-patches@postgresql.org>
Subject: Re: [HACKERS] 8.2 features?
Date: Mon, 31 Jul 2006 11:57:35 -0700
Message-ID: <44CE529F.2070701@joeconway.com> (raw)
In-Reply-To: <26226.1154279741@sss.pgh.pa.us>
References: <200607281947.k6SJlOI02533@momjian.us>
<473.1154116455@sss.pgh.pa.us>
<44CA76A9.8070101@joeconway.com>
<44CB051D.6090609@joeconway.com>
<21320.1154222840@sss.pgh.pa.us>
<26226.1154279741@sss.pgh.pa.us>
Tom Lane wrote:
> As far as avoiding overhead goes, here's what I'm thinking:
>
> * The Values RTE node should contain a list of lists of bare
> expressions, without TargetEntry decoration (you probably do not
> need ResTarget in the raw parse tree for VALUES, either).
>
> * The ValuesScan plan node will just reference this list-of-lists
> (avoiding making a copy). It will need to contain a targetlist
> because all plan nodes do, but the base version of that will just
> be a trivial "Var 1", "Var 2", etc. (The planner might replace that
> with a nontrivial targetlist in cases such as the example above.)
I wanted to post an updated patch even though there are still things not
working again after conversion to bare expressions. Note that I hacked
enough of the executor stuff so I could test my changes on the parser
area. The basic "INSERT ... VALUES (...), (...), ..." does work, but
without DEFAULT again :-(.
The good news is that from a memory and perfomance standpoint, my simple
test now shows us outperforming mysql:
$loopcount = 1000000;
Postgres:
multi-INSERT-at-once Elapsed time is 12 seconds
~420MB
MySQL:
multi-INSERT-at-once Elapsed time is 17 seconds
~600MB
$loopcount = 2000000;
Postgres:
multi-INSERT-at-once Elapsed time is 29 seconds
~730MB
MySQL:
multi-INSERT-at-once Elapsed time is 37 seconds
~1.2GB (this one is from memory -- I didn't write it in my notes)
Joe
Attachments:
multi-insert-r18.diff.gz (application/x-gzip, 20.3K) download
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-docs@postgresql.org
Cc: mail@joeconway.com, tgl@sss.pgh.pa.us, pgsql-patches@postgresql.org
Subject: Re: [HACKERS] 8.2 features?
In-Reply-To: <44CE529F.2070701@joeconway.com>
* 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