Re: Alternative to UPDATE (As COPY to INSERT)

On 23/02/10 09:26, Yan Cheng CHEOK wrote:
> I realize update operation speed in PostgreSQL doesn't meet my speed expectation.
>
> Is there any fast alternative to UPDATE? as using fast COPY to INSERT operation.

No. But you haven't said where the limit is on your operation.
> EXECUTE 'UPDATE statistic SET value = $1 WHERE fk_lot_id = $2 AND measurement_type = $3 AND statistic_type = $4'
> USING _values[i], _lotID, _measurementTypes[i], _statisticTypes[i];

There's no need to do EXECUTE ... USING here - just do the update
UPDATE statistic SET value = _values[i] WHERE fk_log_id = _lotID ...

> It takes around 20ms :(
>
> I am expecting< 1ms

This might be impractical, depending on exactly what you hope to achieve.

If you wish to have individual transactions take no more than 1ms and be
safely on disk, then you will need a disk controller with battery-backed
write cache. Disks just don't spin fast enough.

You posted a few questions, but I don't see anything saying exactly what
you are trying to achieve and what sort of server you have to do it
with. Perhaps some background would be useful.

--
Richard Huxton
Archonet Ltd