Alternative to UPDATE (As COPY to INSERT)

Поиск
Список
Период
Сортировка
От Yan Cheng CHEOK
Тема Alternative to UPDATE (As COPY to INSERT)
Дата
Msg-id 668188.61618.qm@web65706.mail.ac4.yahoo.com
обсуждение исходный текст
Ответы Re: Alternative to UPDATE (As COPY to INSERT)  (Alban Hertroys <dalroi@solfertje.student.utwente.nl>)
Re: Alternative to UPDATE (As COPY to INSERT)  (Richard Huxton <dev@archonet.com>)
Список pgsql-general
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.

Thanks!

I am using update in the following case :

CREATE OR REPLACE FUNCTION update_or_insert_statistic(integer, text[], text[], double precision[])
  RETURNS void AS
$BODY$DECLARE
  _lotID ALIAS FOR $1;
  _measurementTypes ALIAS FOR $2;
  _statisticTypes ALIAS FOR $3;
  _values ALIAS FOR $4;
  _row_count int;
  i int;
BEGIN
    -- Parameters validation.
    IF array_upper(_measurementTypes, 1) != array_upper(_statisticTypes, 1) OR array_upper(_measurementTypes, 1) !=
array_upper(_values,1) THEN 
        RAISE EXCEPTION 'Inconsistency in array size';
    END IF;

    FOR i IN SELECT generate_subscripts(_measurementTypes, 1)
    LOOP
        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];

        GET DIAGNOSTICS _row_count = ROW_COUNT;

        IF _row_count = 0 THEN
            EXECUTE 'INSERT INTO statistic(fk_lot_id, "value", measurement_type, statistic_type) VALUES ($1, $2, $3,
$4)'
            USING _lotID, _value, _measurementType, _statisticType;
        END IF;
    END LOOP;
END;$BODY$

I use the following "minimal" version

SELECT * FROM update_or_insert_statistic(1,array['Pad1 Area','Pad1 Area','Pad1 Area','Pad1 Area','Pad1 Area','Pad1
Area','Pad1 
Area','Pad1 Area','Pad1 Area','Pad1 Area','Pad1 Area','Pad1 Area','Pad1 Area','Pad1 Pitch','Pad1 Pitch','Pad1
Pitch','Pad1Pitch','Pad1  
Pitch','Pad1 Pitch','Pad1 Pitch','Pad1 Pitch','Pad1 Pitch','Pad1 Pitch','Pad1 Pitch','Pad1 Pitch','Pad1 Pitch'],

array['LSL','USL','Nominal','Average','StdDev','StdDev3','CPK','Min','Max','Total','Valid','Invalid','Variance','LSL','USL','Nominal','Av
erage','StdDev','StdDev3','CPK','Min','Max','Total','Valid','Invalid','Variance'],

array[0,0,0,4.94422589800714,3.16063453753607,0,0,1.01620532853175,9.98406933805353,20,20,0,9.98961067986587,0,0,0,6.56297341837825,2.512
73949943937,0,0,1.69188512833033,9.56794946134831,20,20,0,6.31385979204282])

It takes around 20ms :(

I am expecting < 1ms

Or shall I just go back to plain text in this case?

Thanks and Regards
Yan Cheng CHEOK





В списке pgsql-general по дате отправления:

Предыдущее
От: John Gage
Дата:
Сообщение: Re: Sorting performance vs. MySQL
Следующее
От: Oliver Kohll - Mailing Lists
Дата:
Сообщение: pg_dump new version