From: | Yan Cheng CHEOK <yccheok(at)yahoo(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Alternative to UPDATE (As COPY to INSERT) |
Date: | 2010-02-23 09:26:33 |
Message-ID: | 668188.61618.qm@web65706.mail.ac4.yahoo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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','Pad1 Pitch','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
From | Date | Subject | |
---|---|---|---|
Next Message | Ian Lea | 2010-02-23 09:35:51 | Re: how do I do dump and restore without bugging with constraint? |
Previous Message | John Gage | 2010-02-23 09:22:21 | Re: Sorting performance vs. MySQL |