测试设置和基准测试v2
Erwin鼓励在这个帖子中重新创建他的基准测试(https://dev59.com/Imsz5IYBdhLWcg3wsaPN#7782839)，因此我使用合成测试数据修改了他的代码，并添加了来自我的答案的hstore解决方案和json解决方案(以及Pavel在另一个帖子中找到的json解决方案)。现在，基准测试作为一个查询运行，更容易捕获结果。
DROP SCHEMA IF EXISTS x CASCADE;
CREATE SCHEMA x;
CREATE OR REPLACE FUNCTION x.setfield(anyelement, text, text)
RETURNS anyelement
LANGUAGE plpgsql
AS $function$
begin
create temp table aux as select $1.*;
execute 'update aux set ' || quote_ident($2) || ' = ' || quote_literal($3);
select into $1 * from aux;
drop table aux;
return $1;
end;
$function$;
CREATE OR REPLACE FUNCTION x.setfield2(anyelement, text, text)
RETURNS anyelement
LANGUAGE plpgsql
AS $function$
DECLARE
_name text;
_values text[];
_value text;
_attnum int;
BEGIN
FOR _name, _attnum
IN SELECT a.attname, a.attnum
FROM pg_catalog.pg_attribute a
WHERE a.attrelid = (SELECT typrelid
FROM pg_type
WHERE oid = pg_typeof($1)::oid)
LOOP
IF _name = $2 THEN
_value := $3;
ELSE
EXECUTE 'SELECT (($1).' || quote_ident(_name) || ')::text' INTO _value USING $1;
END IF;
_values[_attnum] := COALESCE('"' || replace(replace(_value, '"', '""'), '''', '''''') || '"', '');
END LOOP;
EXECUTE 'SELECT (' || pg_typeof($1)::text || '''(' || array_to_string(_values,',') || ')'').*' INTO $1;
RETURN $1;
END;
$function$;
CREATE OR REPLACE FUNCTION x.setfield3(anyelement, text, text)
RETURNS anyelement
AS $body$
DECLARE
_list text;
BEGIN
_list := (
SELECT string_agg(x.fld, ',')
FROM (
SELECT CASE WHEN a.attname = $2
THEN quote_literal($3)
ELSE quote_ident(a.attname)
END AS fld
FROM pg_catalog.pg_attribute a
WHERE a.attrelid = (SELECT typrelid
FROM pg_type
WHERE oid = pg_typeof($1)::oid)
ORDER BY a.attnum
) x
);
EXECUTE '
SELECT ' || _list || '
FROM (SELECT $1.*) x'
USING $1
INTO $1;
RETURN $1;
END;
$body$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION x.setfield4(INOUT _comp_val anyelement
, _field text, _val text)
RETURNS anyelement AS
$func$
BEGIN
EXECUTE 'SELECT ' || array_to_string(ARRAY(
SELECT CASE WHEN attname = _field
THEN '$2'
ELSE '($1).' || quote_ident(attname)
END AS fld
FROM pg_catalog.pg_attribute
WHERE attrelid = pg_typeof(_comp_val)::text::regclass
AND attnum > 0
AND attisdropped = FALSE
ORDER BY attnum
), ',')
USING _comp_val, _val
INTO _comp_val;
END
$func$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION x.setfield5(r anyelement, fn text, val text,OUT result anyelement)
RETURNS anyelement
LANGUAGE plpgsql
AS $function$
declare jo json;
begin
jo := (select json_object(array_agg(key),
array_agg(case key when fn then val
else value end))
from json_each_text(row_to_json(r)));
result := json_populate_record(r, jo);
end;
$function$;
CREATE FUNCTION x.setfield_json(in_element anyelement, key text, value text)
RETURNS anyelement AS
$BODY$
SELECT json_populate_record( in_element, ('{"'||key||'":"'||value||'"}')::json);
$BODY$ LANGUAGE sql;
CREATE TYPE x.t_f as (
id int
,company text
,sort text
,log_up timestamp
,log_upby smallint
);
DROP TABLE IF EXISTS tmp_f;
CREATE TEMP table tmp_f AS
SELECT ROW(i, 'company'||i, NULL, NULL, NULL)::x.t_f AS f
FROM generate_series(1, 5000) S(i);
DO $$ DECLARE start_time timestamptz; test_count integer; test_description TEXT; BEGIN
test_count := 200;
test_description := 'setfield, Pavel 1: temptable';
start_time := clock_timestamp();
PERFORM x.setfield (f, 'company','new-value-'||md5(random()::text)) FROM tmp_f LIMIT test_count;
RAISE NOTICE 'Test took: % ms (for % rows) Name: %', extract(MILLISECONDS FROM (clock_timestamp() - start_time))::INTEGER, test_count, test_description;
test_count := 5000;
test_description := 'setfield2, Pavel 2: reflection';
start_time := clock_timestamp();
PERFORM x.setfield2 (f, 'company','new-value-'||md5(random()::text)) FROM tmp_f LIMIT test_count;
RAISE NOTICE 'Test took: % ms (for % rows) Name: %', extract(MILLISECONDS FROM (clock_timestamp() - start_time))::INTEGER, test_count, test_description;
test_count := 5000;
test_description := 'setfield3, Erwin 1: reflection';
start_time := clock_timestamp();
PERFORM x.setfield3 (f, 'company','new-value-'||md5(random()::text)) FROM tmp_f LIMIT test_count;
RAISE NOTICE 'Test took: % ms (for % rows) Name: %', extract(MILLISECONDS FROM (clock_timestamp() - start_time))::INTEGER, test_count, test_description;
test_count := 5000;
test_description := 'setfield4, Erwin 2: reflection';
start_time := clock_timestamp();
PERFORM x.setfield4 (f, 'company','new-value-'||md5(random()::text)) FROM tmp_f LIMIT test_count;
RAISE NOTICE 'Test took: % ms (for % rows) Name: %', extract(MILLISECONDS FROM (clock_timestamp() - start_time))::INTEGER, test_count, test_description;
test_count := 5000;
test_description := 'setfield5, Pavel 3: json (PG 9.4)';
start_time := clock_timestamp();
PERFORM x.setfield5 (f, 'company','new-value-'||md5(random()::text)) FROM tmp_f LIMIT test_count;
RAISE NOTICE 'Test took: % ms (for % rows) Name: %', extract(MILLISECONDS FROM (clock_timestamp() - start_time))::INTEGER, test_count, test_description;
test_count := 5000;
test_description := 'setfield_json, Geir 1: casting (PG 9.3)';
start_time := clock_timestamp();
PERFORM x.setfield_json (f, 'company','new-value-'||md5(random()::text)) FROM tmp_f LIMIT test_count;
RAISE NOTICE 'Test took: % ms (for % rows) Name: %', extract(MILLISECONDS FROM (clock_timestamp() - start_time))::INTEGER, test_count, test_description;
test_count := 5000;
test_description := 'no function/inlined: json_object (PG 9.4)';
start_time := clock_timestamp();
PERFORM json_populate_record( f, json_object(ARRAY['company', 'new-value'||md5(random()::text)] )) FROM tmp_f LIMIT test_count;
RAISE NOTICE 'Test took: % ms (for % rows) Name: %', extract(MILLISECONDS FROM (clock_timestamp() - start_time))::INTEGER, test_count, test_description;
test_count := 5000;
test_description := 'no function/inlined: hstore (PG 9.0)';
start_time := clock_timestamp();
PERFORM f #= hstore('company', 'new-value'||md5(random()::text)) FROM tmp_f LIMIT test_count;
RAISE NOTICE 'Test took: % ms (for % rows) Name: %', extract(MILLISECONDS FROM (clock_timestamp() - start_time))::INTEGER, test_count, test_description;
END; $$;
9.4.1版本测试结果，win32平台，i5-4300U处理器
NOTICE: Test took: 1138 ms (for 200 rows) Name: setfield, Pavel 1: temptable
NOTICE: Test took: 652 ms (for 5000 rows) Name: setfield2, Pavel 2: reflection
NOTICE: Test took: 364 ms (for 5000 rows) Name: setfield3, Erwin 1: reflection
NOTICE: Test took: 275 ms (for 5000 rows) Name: setfield4, Erwin 2: reflection
NOTICE: Test took: 192 ms (for 5000 rows) Name: setfield5, Pavel 3: json (PG 9.4)
NOTICE: Test took: 23 ms (for 5000 rows) Name: setfield_json, Geir 1: casting (PG 9.3)
NOTICE: Test took: 25 ms (for 5000 rows) Name: no function/inlined: json_object (PG 9.4)
NOTICE: Test took: 14 ms (for 5000 rows) Name: no function/inlined: hstore (PG 9.0)
CREATE TYPE a as (a1 int); CREATE TYPE b as (b1 a); SELECT setfield3(null::b, 'b1', '(2)');。在这种情况下，来自@Pavel的setfield2有效，但在某些情况下，setfield2会失败，而setfield3则有效 :-? - DavidEGa.atttypid <> 0是什么意思？ - Erwin Brandstetteratttypid = 0，因此基本上与attisdropped = false是多余的。 - DavidEG