From: Yves Vindevogel <yves.vindevogel@implements.be>
To: pgsql-performance@postgresql.org
Subject: Re: Insert performance (OT?)
Date: Tue, 19 Jul 2005 15:38:36 +0200
Message-ID: <646c7b05563f9d76b5492de416f7432a@implements.be> (raw)
In-Reply-To: <42DCDB47.3060009@archonet.com>
References: <b201f08ef88897bc4ad87631fda3d877@implements.be>
<42DCCA3B.8070100@archonet.com>
<d1bd55e89dc5018ddbf7ff09e78d9c61@implements.be>
<42DCDB47.3060009@archonet.com>
I will use 2 queries. They run within a function fnUpload(), so I'm
going to keep it simple.
On 19 Jul 2005, at 12:51, Richard Huxton wrote:
> 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
>
>
Met vriendelijke groeten,
Bien à vous,
Kind regards,
Yves Vindevogel
Implements
Mail: yves.vindevogel@implements.be - Mobile: +32 (478) 80 82 91
Kempische Steenweg 206 - 3500 Hasselt - Tel-Fax: +32 (11) 43 55 76
Web: http://www.implements.be
First they ignore you. Then they laugh at you. Then they fight you.
Then you win.
Mahatma Ghandi.
Attachments:Pasted Graphic 2.tiff (image/tiff, 5.6K) downloadview image
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: yves.vindevogel@implements.be
Subject: Re: Insert performance (OT?)
In-Reply-To: <646c7b05563f9d76b5492de416f7432a@implements.be>
* 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