Re: Good way to insert/update when you're not sure of duplicates?

On Thursday 25 September 2003 18:32, Curtis Stanford wrote:
> On September 25, 2003 11:27 am, Richard Huxton wrote:
[snip]
> > You might find it quickest to halve batch-size while having problems then
> > doubling while it works. The balance is going to depend on how many
> > insert vs update rows you have.
>
> Hey thanks! I actually got the time down to around 1000 rows/sec. I was
> passing in one of the int arguments in the update where clause as a double.
> I don't know why, but changing it to an int as it should be drastically
> reduced the time. Your solution is very interesting. I'll probably try it
> and see which is faster.

That'll be the infamous PG typecast issue with indexes. PG is quite flexible
about you defining your own types, operators etc. To balance this it's also
quite careful about typecasting stuff without being told to. You tend to
notice this with function-calls and indexes not being used when they should.

If the index had been on a double and you'd passed an int, it might have
figured it out, but I tend to be very strict about my types in any case.
Actually, that's probably a good thing most of the time.

--
Richard Huxton
Archonet Ltd