[Bizgres-general] Re: [PERFORM] faster INSERT with possible pre-existing row?

[Luke Lonergan]

John,

On 7/26/05 9:56 AM, "John A Meinel" <john at 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