From: Yves Vindevogel <yves.vindevogel@implements.be>
To: pgsql-performance@postgresql.org
Subject: Fwd: Insert performance (OT?)
Date: Tue, 19 Jul 2005 12:21:08 +0200
Message-ID: <940bb98041b7df475b53f98e35c31ce2@implements.be> (raw)
BTW: thank you for the idea
Begin forwarded message:
> From: Yves Vindevogel <yves.vindevogel@implements.be>
> Date: Tue 19 Jul 2005 12:20:34 CEST
> To: Richard Huxton <dev@archonet.com>
> Subject: Re: [PERFORM] Insert performance (OT?)
>
>
> On 19 Jul 2005, at 11:39, Richard Huxton wrote:
>
>> Yves Vindevogel wrote:
>>> Hi,
>>> Suppose I have a table with 4 fields (f1, f2, f3, f4)
>>> I define 2 unique indexes u1 (f1, f2, f3) and u2 (f1, f2, f4)
>>> I have 3 records
>>> A, B, C, D (this will be inserted)
>>> A, B, C, E (this will pass u2, but not u1, thus not inserted)
>>> A, B, F, D (this will pass u1, but not u2, thus not inserted)
>>
>> Are you saying you want to know whether they will be inserted before
>> you try to do so?
>>
> No, that is not an issue. Problem is that when I use a big query with
> "insert into .. select" and one record is wrong (like above) the
> complete insert query is abandonned.
> Therefore, I must do it another way. Or I must be able to say, insert
> them and dump the rest.
>
>>> Now, for performance ...
>>> I have tables like this with 500.000 records where there's a new
>>> upload of approx. 20.000 records.
>>> It is only now that we say index u2 to be necessary. So, until now,
>>> I did something like insert into ... select f1, f2, f2, max(f4)
>>> group by f1, f2, f3
>>> That is ok ... and also logically ok because of the data definition
>>
>> I'm confused here - assuming you meant "select f1,f2,f3", then I
>> don't see how you guarantee the row doesn't alredy exist.
>>
> No, I meant it with max(f4) because my table has 4 fields. And no, I
> can't guarantee that, that is exactly my problem.
> But with the unique indexes, I'm certain that it will not get into my
> database
>
>>> I cannot do this with 2 group by's. I tried this on paper and I'm
>>> not succeeding.
>>
>> I don't see how you can have two group-by's, or what that would mean
>> if you did.
>>
> select from ( select from group by) as foo group by
>
>>> 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.
>
>> 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.
>
>> --
>> Richard Huxton
>> Archonet Ltd
>>
>> ---------------------------(end of
>> broadcast)---------------------------
>> TIP 6: explain analyze is your friend
>>
>>
> 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.
>
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 imagePasted 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: Fwd: Insert performance (OT?)
In-Reply-To: <940bb98041b7df475b53f98e35c31ce2@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