From: Luke Lonergan <llonergan@greenplum.com>
To: John A Meinel <john@arbash-meinel.com>
To: Dan Harris <fbsd@drivefaster.net>
Cc: pgsql-performance@postgresql.org, "bizgres-general" <bizgres-general@pgfoundry.org>
Subject: Re: faster INSERT with possible pre-existing row?
Date: Tue, 26 Jul 2005 11:46:33 -0700
Message-ID: <BF0BD319.9FE1%llonergan@greenplum.com> (raw)
In-Reply-To: <42E66B30.3030108@arbash-meinel.com>
John,
On 7/26/05 9:56 AM, "John A Meinel" <john@arbash-meinel.com> wrote:
> You could insert all of your data into a temporary table, and then do:
>
> INSERT INTO final_table SELECT * FROM temp_table WHERE NOT EXISTS
> (SELECT info FROM final_table WHERE id=id, path=path, y=y);
>
> Or you could load it into the temporary table, and then:
> DELETE FROM temp_table WHERE EXISTS (SELECT FROM final_table WHERE id...);
>
> And then do a plain INSERT INTO.
>
> I can't say what the specific performance increases would be, but
> temp_table could certainly be an actual TEMP table (meaning it only
> exists during the connection), and you could easily do a COPY into that
> table to load it up quickly, without having to check any constraints.
Yah - that's a typical approach, and it would be excellent if the COPY
bypassed WAL for the temp table load. This is something we discussed in
bizgres development a while back. I think we should do this for sure -
would nearly double the temp table load rate, and the subsequent temp table
delete *should* be fast enough (?) Any performance tests you've done on
that delete/subselect operation?
- Luke
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: llonergan@greenplum.com, john@arbash-meinel.com, fbsd@drivefaster.net, bizgres-general@pgfoundry.org
Subject: Re: faster INSERT with possible pre-existing row?
In-Reply-To: <BF0BD319.9FE1%llonergan@greenplum.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