Skip site navigation (1) Skip section navigation (2)

Re: faster INSERT with possible pre-existing row?

From: John A Meinel <john(at)arbash-meinel(dot)com>
To: Dan Harris <fbsd(at)drivefaster(dot)net>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: faster INSERT with possible pre-existing row?
Date: 2005-07-26 16:56:16
Message-ID: 42E66B30.3030108@arbash-meinel.com (view raw or flat)
Thread:
Lists: pgsql-performance
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
=:->

In response to

Responses

pgsql-performance by date

Next:From: Jeffrey W. BakerDate: 2005-07-26 17:42:19
Subject: Re: Cheap RAM disk?
Previous:From: Chris BrowneDate: 2005-07-26 16:51:14
Subject: Re: Cheap RAM disk?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group