From: dbyzaa@163.com <dbyzaa@163.com>
To: pgsql-hackers <pgsql-hackers@postgresql.org>
To: pgsql-performance <pgsql-performance@postgresql.org>
To: pgsql-general <pgsql-general@postgresql.org>
Subject: temporary table vs array performance
Date: Mon, 26 Sep 2016 23:39:11 +0800
Message-ID: <2016092623390977647132@163.com> (raw)
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-hackers>
test:
create type h3 as (id int,name char(10));
CREATE or replace FUNCTION proc17()
RETURNS SETOF h3 AS $$
DECLARE
v_rec h3;
BEGIN
create temp table abc(id int,name varchar) on commit drop;
insert into abc select 1,'lw';
insert into abc select 2,'lw2';
for v_rec in
select * from abc loop
return next v_rec;
end loop;
END;
$$
LANGUAGE plpgsql;
CREATE or replace FUNCTION proc16()
RETURNS SETOF h3 AS $$
DECLARE
id_array int[];
name_arr varchar[];
v_rec h3;
BEGIN
id_array =array[1,2];
name_arr=array['lw','lw2'];
for v_rec in
select unnest(id_array) ,unnest(name_arr) loop
return next v_rec;
end loop;
END;
$$
LANGUAGE plpgsql;
postgres=# select * from proc17();
id | name
----+------------
1 | lw
2 | lw2
(2 rows)
Time: 68.372 ms
postgres=# select * from proc16();
id | name
----+------------
1 | lw
2 | lw2
(2 rows)
Time: 1.357 ms
temp talbe result:
[postgres@pg95 test_sql]$ pgbench -M prepared -n -r -c 2 -j 2 -T 10 -f temporary_test_1.sql
transaction type: Custom query
scaling factor: 1
query mode: prepared
number of clients: 2
number of threads: 2
duration: 10 s
number of transactions actually processed: 5173
latency average: 3.866 ms
tps = 517.229191 (including connections establishing)
tps = 517.367956 (excluding connections establishing)
statement latencies in milliseconds:
3.863798 select * from proc17();
array result:
[postgres@pg95 test_sql]$ pgbench -M prepared -n -r -c 2 -j 2 -T 10 -f arrary_test_1.sql
transaction type: Custom query
scaling factor: 1
query mode: prepared
number of clients: 2
number of threads: 2
duration: 10 s
number of transactions actually processed: 149381
latency average: 0.134 ms
tps = 14936.875176 (including connections establishing)
tps = 14940.234960 (excluding connections establishing)
statement latencies in milliseconds:
0.132983 select * from proc16();
Array is not convenient to use in function, whether there are other methods can be replaced temp table in function
dbyzaa@163.com
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: dbyzaa@163.com, pgsql-hackers@postgresql.org, pgsql-general@postgresql.org
Subject: Re: temporary table vs array performance
In-Reply-To: <2016092623390977647132@163.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox