From: Richard Huxton <dev@archonet.com>
To: Yves Vindevogel <yves.vindevogel@implements.be>
Cc: Postgresql Performance <pgsql-performance@postgresql.org>
Subject: Re: Insert performance (OT?)
Date: Tue, 19 Jul 2005 11:51:51 +0100
Message-ID: <42DCDB47.3060009@archonet.com> (raw)
In-Reply-To: <d1bd55e89dc5018ddbf7ff09e78d9c61@implements.be>
References: <b201f08ef88897bc4ad87631fda3d877@implements.be>
<42DCCA3B.8070100@archonet.com>
<d1bd55e89dc5018ddbf7ff09e78d9c61@implements.be>
Yves Vindevogel wrote:
>>> So, I must use a function that will check against u1 and u2, and then
>>> insert if it is ok.
>>> I know that such a function is way slower that my insert query.
>>
>> So - you have a table, called something like "upload" with 20,000 rows
>> and you'd like to know whether it is safe to insert them. Well, it's
>> easy enough to identify which ones are duplicates.
>>
>> SELECT * FROM upload JOIN main_table ON u1=f1 AND u2=f2 AND u3=f3;
>> SELECT * FROM upload JOIN main_table ON u1=f1 AND u2=f2 AND u3=f4;
>>
> That is a good idea. I can delete the ones that would fail my first
> unique index this way, and then delete the ones that would fail my
> second unique index and then upload them.
> Hmm, why did I not think of that myself.
I've spent a lot of time moving data from one system to another, usually
having to clean it in the process. At 9pm on a Friday, you decide that
on the next job you'll find an efficient way to do it :-)
>> Are you saying that deleting these rows and then inserting takes too
>> long?
>>
> This goes very fast, but not with a function that checks each record one
> by one.
You could get away with one query if you converted them to left-joins:
INSERT INTO ...
SELECT * FROM upload LEFT JOIN ... WHERE f3 IS NULL
UNION
SELECT * FROM upload LEFT JOIN ... WHERE f4 IS NULL
The UNION will remove duplicates for you, but this might turn out to be
slower than two separate queries.
--
Richard Huxton
Archonet Ltd
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: dev@archonet.com, yves.vindevogel@implements.be
Subject: Re: Insert performance (OT?)
In-Reply-To: <42DCDB47.3060009@archonet.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox