Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1boY1X-0004uM-II for pgsql-hackers@arkaria.postgresql.org; Mon, 26 Sep 2016 15:40:56 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1boY1X-0001Iv-0D for pgsql-hackers@arkaria.postgresql.org; Mon, 26 Sep 2016 15:40:55 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1boY07-0004ye-S1; Mon, 26 Sep 2016 15:39:28 +0000 Received: from m12-12.163.com ([220.181.12.12]) by makus.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1boY02-0002y2-FW; Mon, 26 Sep 2016 15:39:26 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=163.com; s=s110527; h=Date:From:Subject:Mime-Version:Message-ID; bh=aUKzb x5srV4jxZF0R8funNcfWCbeERuwUHFDXZrR9xQ=; b=QwNQd5k3Zqe9kBz6RfcAj KGFSE5/k0+aBsni1bH1gWeXfOHqMssQ6s3LguMW/XY2UQv6gb0DcIoNZkg/EbQBK 3NRG1BOl8XzNgsHjXcxlQBw/bQays0dNBuQrojPzWzuCDyLAqpdpigCr2PEo05To L+mk5s1m4VaM4tSaPcKlvE= Received: from db (unknown [182.18.73.149]) by smtp8 (Coremail) with SMTP id DMCowABHQjQfQelXn63UCw--.11950S2; Mon, 26 Sep 2016 23:39:13 +0800 (CST) Date: Mon, 26 Sep 2016 23:39:11 +0800 From: "dbyzaa@163.com" To: pgsql-hackers , pgsql-performance , pgsql-general Subject: temporary table vs array performance X-Priority: 3 X-GUID: 3115DE28-8993-47CF-B680-1C612446E43F X-Has-Attach: no X-Mailer: Foxmail 7, 2, 7, 174[cn] Mime-Version: 1.0 Message-ID: <2016092623390977647132@163.com> Content-Type: multipart/alternative; boundary="----=_001_NextPart324504147781_=----" X-CM-TRANSID: DMCowABHQjQfQelXn63UCw--.11950S2 X-Coremail-Antispam: 1Uf129KBjvJXoW7tF1kJF43JrWUWFyrZFyxKrg_yoW8ZF13pF ZakrnrGr13Gr4UKwn5Za18u3y09FZ3A3y3Jr13ta4vkr92ywn5Jr4vgF1vyFZ5GrW8Zw17 Way7Wr1DAF1UZ3DanT9S1TB71UUUUUUqnTZGkaVYY2UrUUUUjbIjqfuFe4nvWSU5nxnvy2 9KBjDUYxBIdaVFxhVjvjDU0xZFpf9x07bj4EiUUUUU= X-Originating-IP: [182.18.73.149] X-CM-SenderInfo: xge16trd6rljoofrz/xtbBZAHwOFQG6ICmXgAAsv X-Pg-Spam-Score: -3.3 (---) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-hackers Precedence: bulk Sender: pgsql-hackers-owner@postgresql.org This is a multi-part message in MIME format. ------=_001_NextPart324504147781_=---- Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: base64 dGVzdDoNCmNyZWF0ZSB0eXBlICBoMyBhcyAoaWQgaW50LG5hbWUgY2hhcigxMCkpOw0KDQpDUkVB VEUgb3IgcmVwbGFjZSBGVU5DVElPTiBwcm9jMTcoKSANClJFVFVSTlMgU0VUT0YgaDMgIEFTICQk IA0KREVDTEFSRQ0Kdl9yZWMgaDM7DQpCRUdJTiANCiAgICBjcmVhdGUgdGVtcCB0YWJsZSBhYmMo aWQgaW50LG5hbWUgdmFyY2hhcikgb24gY29tbWl0IGRyb3A7DQppbnNlcnQgaW50byBhYmMgc2Vs ZWN0IDEsJ2x3JzsNCmluc2VydCBpbnRvIGFiYyBzZWxlY3QgMiwnbHcyJzsNCmZvciB2X3JlYyBp bg0Kc2VsZWN0ICogZnJvbSBhYmMgbG9vcA0KcmV0dXJuIG5leHQgdl9yZWM7DQplbmQgbG9vcDsN CkVORDsgDQokJCANCkxBTkdVQUdFIHBscGdzcWw7DQoNCg0KQ1JFQVRFIG9yIHJlcGxhY2UgRlVO Q1RJT04gcHJvYzE2KCkgDQpSRVRVUk5TICAgU0VUT0YgaDMgQVMgJCQgDQpERUNMQVJFDQogaWRf YXJyYXkgaW50W107DQogbmFtZV9hcnIgdmFyY2hhcltdOw0KIHZfcmVjIGgzOw0KQkVHSU4gDQog ICAgaWRfYXJyYXkgPWFycmF5WzEsMl07DQogICAgbmFtZV9hcnI9YXJyYXlbJ2x3JywnbHcyJ107 DQpmb3Igdl9yZWMgaW4NCnNlbGVjdCB1bm5lc3QoaWRfYXJyYXkpICAsdW5uZXN0KG5hbWVfYXJy KSBsb29wDQpyZXR1cm4gbmV4dCB2X3JlYzsgICAgICAgICAgICAgICAgDQplbmQgbG9vcDsNCkVO RDsgDQokJCANCkxBTkdVQUdFIHBscGdzcWw7DQpwb3N0Z3Jlcz0jIHNlbGVjdCAqIGZyb20gcHJv YzE3KCk7DQogaWQgfCAgICBuYW1lICAgIA0KLS0tLSstLS0tLS0tLS0tLS0NCiAgMSB8IGx3ICAg ICAgICANCiAgMiB8IGx3MiAgICAgICANCigyIHJvd3MpDQoNClRpbWU6IDY4LjM3MiBtcw0KcG9z dGdyZXM9IyBzZWxlY3QgKiBmcm9tIHByb2MxNigpOw0KIGlkIHwgICAgbmFtZSAgICANCi0tLS0r LS0tLS0tLS0tLS0tDQogIDEgfCBsdyAgICAgICAgDQogIDIgfCBsdzIgICAgICAgDQooMiByb3dz KQ0KDQpUaW1lOiAxLjM1NyBtcw0KDQp0ZW1wIHRhbGJlIHJlc3VsdDoNCltwb3N0Z3Jlc0BwZzk1 IHRlc3Rfc3FsXSQgcGdiZW5jaCAtTSBwcmVwYXJlZCAtbiAtciAtYyAyIC1qIDIgLVQgMTAgLWYg dGVtcG9yYXJ5X3Rlc3RfMS5zcWwgDQp0cmFuc2FjdGlvbiB0eXBlOiBDdXN0b20gcXVlcnkNCnNj YWxpbmcgZmFjdG9yOiAxDQpxdWVyeSBtb2RlOiBwcmVwYXJlZA0KbnVtYmVyIG9mIGNsaWVudHM6 IDINCm51bWJlciBvZiB0aHJlYWRzOiAyDQpkdXJhdGlvbjogMTAgcw0KbnVtYmVyIG9mIHRyYW5z YWN0aW9ucyBhY3R1YWxseSBwcm9jZXNzZWQ6IDUxNzMNCmxhdGVuY3kgYXZlcmFnZTogMy44NjYg bXMNCnRwcyA9IDUxNy4yMjkxOTEgKGluY2x1ZGluZyBjb25uZWN0aW9ucyBlc3RhYmxpc2hpbmcp DQp0cHMgPSA1MTcuMzY3OTU2IChleGNsdWRpbmcgY29ubmVjdGlvbnMgZXN0YWJsaXNoaW5nKQ0K c3RhdGVtZW50IGxhdGVuY2llcyBpbiBtaWxsaXNlY29uZHM6DQozLjg2Mzc5OCBzZWxlY3QgKiBm cm9tIHByb2MxNygpOw0KDQphcnJheSByZXN1bHQ6DQpbcG9zdGdyZXNAcGc5NSB0ZXN0X3NxbF0k IHBnYmVuY2ggLU0gcHJlcGFyZWQgLW4gLXIgLWMgMiAtaiAyIC1UIDEwIC1mIGFycmFyeV90ZXN0 XzEuc3FsIA0KdHJhbnNhY3Rpb24gdHlwZTogQ3VzdG9tIHF1ZXJ5DQpzY2FsaW5nIGZhY3Rvcjog MQ0KcXVlcnkgbW9kZTogcHJlcGFyZWQNCm51bWJlciBvZiBjbGllbnRzOiAyDQpudW1iZXIgb2Yg dGhyZWFkczogMg0KZHVyYXRpb246IDEwIHMNCm51bWJlciBvZiB0cmFuc2FjdGlvbnMgYWN0dWFs bHkgcHJvY2Vzc2VkOiAxNDkzODENCmxhdGVuY3kgYXZlcmFnZTogMC4xMzQgbXMNCnRwcyA9IDE0 OTM2Ljg3NTE3NiAoaW5jbHVkaW5nIGNvbm5lY3Rpb25zIGVzdGFibGlzaGluZykNCnRwcyA9IDE0 OTQwLjIzNDk2MCAoZXhjbHVkaW5nIGNvbm5lY3Rpb25zIGVzdGFibGlzaGluZykNCnN0YXRlbWVu dCBsYXRlbmNpZXMgaW4gbWlsbGlzZWNvbmRzOg0KMC4xMzI5ODMgc2VsZWN0ICogZnJvbSBwcm9j MTYoKTsNCg0KQXJyYXkgaXMgbm90IGNvbnZlbmllbnQgdG8gdXNlIGluIGZ1bmN0aW9uLCB3aGV0 aGVyIHRoZXJlIGFyZSBvdGhlciBtZXRob2RzIGNhbiBiZSByZXBsYWNlZCB0ZW1wIHRhYmxlIGlu IGZ1bmN0aW9uDQoNCg0KDQoNCmRieXphYUAxNjMuY29tDQo= ------=_001_NextPart324504147781_=---- Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable =0A
test:
create type  h3 as (i= d int,name char(10));

CREATE or replace FU= NCTION proc17() 
RETURNS SETOF h3  AS&nbs= p;$$ 
DECLARE
v_rec h3;
BEGIN 
  &nbs= p; create temp table abc(id int,name varchar= ) on commit drop;
insert into abc select 1,'lw';
insert into abc select 2,'lw= 2';
for v_rec in select * from ab= c loop
return next&= nbsp;v_rec;
end loop;END; 
$$ 
LANGUAGE plpgsql;


CREATE o= r replace FUNCTION proc16() 
RETURNS  &nb= sp;SETOF h3 AS $$ 
DECLARE
 id_array i= nt[];
 name_arr varchar[];
 v_rec h3;
BEGIN&n= bsp;
    id_array =3Darray[1,2];
 &nbs= p;  name_arr=3Darray['lw','lw2'];
for v_rec in
select unnest(id_array)  ,unnest(name_arr) loop return next v_rec;&nb= sp;            = ;   
end = loop;
END; 
$$ 
LANGUAGE plpgsql;
postgres=3D#&= nbsp;select * from proc17();
 id |  =   name    
----+------------
 &nb= sp;1 | lw        
&nb= sp; 2 | lw2       
(2=  rows)

Time: 68.372 ms
postgres=3D# select&n= bsp;* from proc16();
 id |    n= ame    
----+------------
  1 |&n= bsp;lw        
  2&nb= sp;| lw2       
(2 rows)
Time: 1.357 ms

temp talbe result:
[postgres@pg95 test_sq= l]$ pgbench -M prepared -n -r -c 2 = ;-j 2 -T 10 -f temporary_test_1.sql 
tran= saction type: Custom query
scaling factor: 1query mode: prepared
number of clients: 2number of threads: 2
duration: 10 s
number=  of transactions actually processed: 5173
late= ncy average: 3.866 ms
tps =3D 517.229191 = (including connections establishing)
tps =3D 517.367956 (excluding&n= bsp;connections establishing)
statement latencies = ;in milliseconds:
3.8637= 98 select * from p= roc17();

=
array resu= lt:
[postgres@pg95 test_sql]$ pg= bench -M prepared -n -r -c 2 -j 2&= nbsp;-T 10 -f arrary_test_1.sql 
transaction t= ype: Custom query
scaling factor: 1
query m= ode: prepared
number of clients: 2
number o= f threads: 2
duration: 10 s
number of = transactions actually processed: 149381
latency ave= rage: 0.134 ms
tps =3D 14936.875176 (including=  connections establishing)
tps =3D 14940.234960 (excluding conn= ections establishing)
statement latencies in = ;milliseconds:
0.132983 select * from proc16();=

Array is not convenie= nt to use in function, whether there ar= e other methods can be replaced temp table in fun= ction

=0A

<= /div>
=0A
dbyzaa@163.com
=0A ------=_001_NextPart324504147781_=------