Message-ID: <42E66B30.3030108@arbash-meinel.com>
Date: Tue, 26 Jul 2005 11:56:16 -0500
From: John A Meinel <john@arbash-meinel.com>
User-Agent: Mozilla Thunderbird 1.0.2 (Windows/20050317)
MIME-Version: 1.0
To: Dan Harris <fbsd@drivefaster.net>
Cc: pgsql-performance@postgresql.org
Subject: Re: faster INSERT with possible pre-existing row?
References: <AB60A4E4-1DF1-459E-8866-007810D68419@drivefaster.net>
In-Reply-To: <AB60A4E4-1DF1-459E-8866-007810D68419@drivefaster.net>
Content-Type: multipart/signed; micalg=pgp-sha1;
	protocol="application/pgp-signature";
	boundary="------------enig9C21B405ABDA157CADDE32ED"

This is an OpenPGP/MIME signed message (RFC 2440 and 3156)
--------------enig9C21B405ABDA157CADDE32ED
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Dan Harris wrote:
> I am working on a process that will be inserting tens of million rows  
> and need this to be as quick as possible.
> 
> The catch is that for each row I could potentially insert, I need to  
> look and see if the relationship is already there  to prevent  multiple 
> entries.  Currently I am doing a SELECT before doing the  INSERT, but I 
> recognize the speed penalty in doing to operations.  I  wonder if there 
> is some way I can say "insert this record, only if it  doesn't exist 
> already".  To see if it exists, I would need to compare  3 fields 
> instead of just enforcing a primary key.
> 
> Even if this could be a small increase per record, even a few percent  
> faster compounded over the whole load could be a significant reduction.
> 
> Thanks for any ideas you might have.
> 
> -Dan
> 

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.

Just a thought,
John
=:->


--------------enig9C21B405ABDA157CADDE32ED
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: OpenPGP digital signature
Content-Disposition: attachment; filename="signature.asc"

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.1 (Cygwin)
Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org

iD8DBQFC5mswJdeBCYSNAAMRAupDAKCrP6dp0q0HGvri2TlUZfeg/zZENwCcDUoS
OuTezkceW29aUAbY/2rAe9k=
=21Zo
-----END PGP SIGNATURE-----

--------------enig9C21B405ABDA157CADDE32ED--