pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedInsert performance (OT?)
8+ messages / 4 participants
[nested] [flat]
* Insert performance (OT?)
@ 2005-07-18 19:29 Yves Vindevogel <yves.vindevogel@implements.be>
2005-07-19 08:35 ` Re: Insert performance (OT?) Yves Vindevogel <yves.vindevogel@implements.be>
2005-07-19 09:39 ` Re: Insert performance (OT?) Richard Huxton <dev@archonet.com>
0 siblings, 2 replies; 8+ messages in thread
From: Yves Vindevogel @ 2005-07-18 19:29 UTC (permalink / raw)
To: pgsql-performance
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)
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 cannot do this with 2 group by's. I tried this on paper and I'm not
succeeding.
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, my question ...
How can I keep the same performance, but also with the new index in
mind ???
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) download
view image
* Re: Insert performance (OT?)
2005-07-18 19:29 Insert performance (OT?) Yves Vindevogel <yves.vindevogel@implements.be>
@ 2005-07-19 08:35 ` Yves Vindevogel <yves.vindevogel@implements.be>
1 sibling, 0 replies; 8+ messages in thread
From: Yves Vindevogel @ 2005-07-19 08:35 UTC (permalink / raw)
To: pgsql-performance
nobody ?
On 18 Jul 2005, at 21:29, 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)
>
> 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 cannot do this with 2 group by's. I tried this on paper and I'm not
> succeeding.
>
> 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, my question ...
> How can I keep the same performance, but also with the new index in
> mind ???
>
>
> Met vriendelijke groeten,
> Bien à vous,
> Kind regards,
>
> Yves Vindevogel
> Implements
>
> <Pasted Graphic 2.tiff>
>
> 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.
> ---------------------------(end of
> broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>
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) download
view image
* Re: Insert performance (OT?)
2005-07-18 19:29 Insert performance (OT?) Yves Vindevogel <yves.vindevogel@implements.be>
@ 2005-07-19 09:39 ` Richard Huxton <dev@archonet.com>
2005-07-19 10:51 ` Re: Insert performance (OT?) Richard Huxton <dev@archonet.com>
1 sibling, 1 reply; 8+ messages in thread
From: Richard Huxton @ 2005-07-19 09:39 UTC (permalink / raw)
To: Yves Vindevogel <yves.vindevogel@implements.be>; +Cc: pgsql-performance
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?
> 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.
> 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.
> 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;
Are you saying that deleting these rows and then inserting takes too long?
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Insert performance (OT?)
2005-07-18 19:29 Insert performance (OT?) Yves Vindevogel <yves.vindevogel@implements.be>
2005-07-19 09:39 ` Re: Insert performance (OT?) Richard Huxton <dev@archonet.com>
@ 2005-07-19 10:51 ` Richard Huxton <dev@archonet.com>
2005-07-19 13:38 ` Re: Insert performance (OT?) Yves Vindevogel <yves.vindevogel@implements.be>
2005-08-17 20:25 ` Re: Insert performance (OT?) Manfred Koizar <mkoi-pg@aon.at>
0 siblings, 2 replies; 8+ messages in thread
From: Richard Huxton @ 2005-07-19 10:51 UTC (permalink / raw)
To: Yves Vindevogel <yves.vindevogel@implements.be>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Insert performance (OT?)
2005-07-18 19:29 Insert performance (OT?) Yves Vindevogel <yves.vindevogel@implements.be>
2005-07-19 09:39 ` Re: Insert performance (OT?) Richard Huxton <dev@archonet.com>
2005-07-19 10:51 ` Re: Insert performance (OT?) Richard Huxton <dev@archonet.com>
@ 2005-07-19 13:38 ` Yves Vindevogel <yves.vindevogel@implements.be>
1 sibling, 0 replies; 8+ messages in thread
From: Yves Vindevogel @ 2005-07-19 13:38 UTC (permalink / raw)
To: pgsql-performance
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) download
view image
* Re: Insert performance (OT?)
2005-07-18 19:29 Insert performance (OT?) Yves Vindevogel <yves.vindevogel@implements.be>
2005-07-19 09:39 ` Re: Insert performance (OT?) Richard Huxton <dev@archonet.com>
2005-07-19 10:51 ` Re: Insert performance (OT?) Richard Huxton <dev@archonet.com>
@ 2005-08-17 20:25 ` Manfred Koizar <mkoi-pg@aon.at>
2005-08-17 22:02 ` Re: Insert performance (OT?) John A Meinel <john@arbash-meinel.com>
1 sibling, 1 reply; 8+ messages in thread
From: Manfred Koizar @ 2005-08-17 20:25 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; +Cc: Yves Vindevogel <yves.vindevogel@implements.be>; pgsql-performance
On Tue, 19 Jul 2005 11:51:51 +0100, Richard Huxton <dev@archonet.com>
wrote:
>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
For the archives: This won't work. Each of the two SELECTs
eliminates rows violating one of the two constraints but includes rows
violating the other constraint. After the UNION you are back to
violating both constraints :-(
Servus
Manfred
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Insert performance (OT?)
2005-07-18 19:29 Insert performance (OT?) Yves Vindevogel <yves.vindevogel@implements.be>
2005-07-19 09:39 ` Re: Insert performance (OT?) Richard Huxton <dev@archonet.com>
2005-07-19 10:51 ` Re: Insert performance (OT?) Richard Huxton <dev@archonet.com>
2005-08-17 20:25 ` Re: Insert performance (OT?) Manfred Koizar <mkoi-pg@aon.at>
@ 2005-08-17 22:02 ` John A Meinel <john@arbash-meinel.com>
0 siblings, 0 replies; 8+ messages in thread
From: John A Meinel @ 2005-08-17 22:02 UTC (permalink / raw)
To: Manfred Koizar <mkoi-pg@aon.at>; pgsql-performance
Manfred Koizar wrote:
> On Tue, 19 Jul 2005 11:51:51 +0100, Richard Huxton <dev@archonet.com>
> wrote:
>
>>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
>
>
> For the archives: This won't work. Each of the two SELECTs
> eliminates rows violating one of the two constraints but includes rows
> violating the other constraint. After the UNION you are back to
> violating both constraints :-(
Couldn't you use "INTERSECT" then? To only get the rows that *both*
queries return?
John
=:->
>
> Servus
> Manfred
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>
Attachments:
signature.asc (application/pgp-signature, 252B) download
^ permalink raw reply [nested|flat] 8+ messages in thread
* Fwd: Insert performance (OT?)
@ 2005-07-19 10:21 Yves Vindevogel <yves.vindevogel@implements.be>
0 siblings, 0 replies; 8+ messages in thread
From: Yves Vindevogel @ 2005-07-19 10:21 UTC (permalink / raw)
To: pgsql-performance
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) download
view image
Pasted Graphic 2.tiff (image/tiff, 5.6K) download
view image
end of thread, other threads:[~2005-08-17 22:02 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-07-18 19:29 Insert performance (OT?) Yves Vindevogel <yves.vindevogel@implements.be>
2005-07-19 08:35 ` Yves Vindevogel <yves.vindevogel@implements.be>
2005-07-19 09:39 ` Richard Huxton <dev@archonet.com>
2005-07-19 10:51 ` Richard Huxton <dev@archonet.com>
2005-07-19 13:38 ` Yves Vindevogel <yves.vindevogel@implements.be>
2005-08-17 20:25 ` Manfred Koizar <mkoi-pg@aon.at>
2005-08-17 22:02 ` John A Meinel <john@arbash-meinel.com>
2005-07-19 10:21 Fwd: Insert performance (OT?) Yves Vindevogel <yves.vindevogel@implements.be>
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