Re: Insert performance (OT?) - Mailing list pgsql-performance

nobody ?


On 18 Jul 2005, at 21:29, Yves Vindevogel wrote:


<excerpt>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,


<bold>Yves Vindevogel</bold>

<bold>Implements</bold>

<smaller>

</smaller><<Pasted Graphic 2.tiff><smaller>


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

<italic><x-tad-smaller>

First they ignore you.  Then they laugh at you.  Then they fight you.
Then you win.

Mahatma Ghandi.</x-tad-smaller></italic></smaller></excerpt><excerpt>

---------------------------(end of
broadcast)---------------------------

TIP 5: don't forget to increase your free space map settings


</excerpt>Met vriendelijke groeten,

Bien à vous,

Kind regards,


<bold>Yves Vindevogel</bold>

<bold>Implements</bold>

<smaller>

</smaller>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

<smaller>


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

<italic><x-tad-smaller>

First they ignore you.  Then they laugh at you.  Then they fight you.
Then you win.

Mahatma Ghandi.</x-tad-smaller></italic></smaller>



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.