DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com;
	h=received:message-id:date:from:reply-to:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references;
	b=ssbNqQ1ji0BelG+bDIAq+CRiOY0LmhOT5dkoZvSLcSOgzouX4t4HebnZsjXgcnnrPuHFrYGpvgf7xkrRL0o/qqcopQMjt1RN2xDb2ypu2PsP5nkJULFyKw0bEVKdcsjNNHeCAjqFp4N5AKEu5W3uY1663DnpCMUc5T8J97RX9BQ=
Message-ID: <f3c0b40805072612357db85f2c@mail.gmail.com>
Date: Tue, 26 Jul 2005 14:35:17 -0500
From: Matthew Nuzum <mattnuzum@gmail.com>
Reply-To: newz@bearfruit.org
To: Dan Harris <fbsd@drivefaster.net>
Subject: Re: faster INSERT with possible pre-existing row?
Cc: pgsql-performance@postgresql.org
In-Reply-To: <AB60A4E4-1DF1-459E-8866-007810D68419@drivefaster.net>
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline
References: <AB60A4E4-1DF1-459E-8866-007810D68419@drivefaster.net>

On 7/26/05, Dan Harris <fbsd@drivefaster.net> wrote:
> I am working on a process that will be inserting tens of million rows
> and need this to be as quick as possible.
>=20
> 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.

I struggled with this for a while. At first I tried stored procedures
and triggers, but it took very long (over 24 hours for my dataset).
After several iterations of rewritting it, first into C# then into
Python I got the whole process down to under 30 min.

My scenario is this:
I want to normalize log data. For example, for the IP address in a log
entry, I need to look up the unique id of the IP address, or if the IP
address is new, insert it and then return the newly created entry.
Multiple processes use the data, but only one process, run daily,
actually changes it. Because this one process knows that the data is
static, it selects the tables into in-memory hash tables (C#) or
Dictionaries (Python) and then does the lookups there. It is *super*
fast, but it uses a *lot* of ram. ;-)

To limit the ram, I wrote a version of the python code that uses gdbm
files instead of Dictionaries. This requires a newer version of Python
(to allow a gdbm db to work just like a dictionary) but makes life
easier in case someone is using my software on a lower end machine.
This doubled the time of the lookups from about 15 minutes to 30,
bringing the whole process to about 45 minutes.

--=20
Matthew Nuzum
www.bearfruit.org