From: | "Luke Lonergan" <llonergan(at)greenplum(dot)com> |
---|---|
To: | "John A Meinel" <john(at)arbash-meinel(dot)com>, "Dan Harris" <fbsd(at)drivefaster(dot)net> |
Cc: | pgsql-performance(at)postgresql(dot)org, "bizgres-general" <bizgres-general(at)pgfoundry(dot)org> |
Subject: | Re: faster INSERT with possible pre-existing row? |
Date: | 2005-07-26 18:46:33 |
Message-ID: | BF0BD319.9FE1%llonergan@greenplum.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
John,
On 7/26/05 9:56 AM, "John A Meinel" <john(at)arbash-meinel(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Sven Willenberger | 2005-07-26 19:03:17 | Re: faster INSERT with possible pre-existing row? |
Previous Message | Michael Stone | 2005-07-26 18:33:43 | Re: Cheap RAM disk? |