pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedtemporary table vs array performance
4+ messages / 3 participants
[nested] [flat]
* temporary table vs array performance
@ 2016-09-26 15:39 dbyzaa@163.com <dbyzaa@163.com>
2016-09-26 15:49 ` Re: [HACKERS] temporary table vs array performance David G. Johnston <david.g.johnston@gmail.com>
2016-09-26 16:16 ` Re: [HACKERS] temporary table vs array performance Pavel Stehule <pavel.stehule@gmail.com>
0 siblings, 2 replies; 4+ messages in thread
From: dbyzaa@163.com @ 2016-09-26 15:39 UTC (permalink / raw)
To: pgsql-hackers <pgsql-hackers@postgresql.org>; pgsql-performance; pgsql-general <pgsql-general@postgresql.org>
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: [HACKERS] temporary table vs array performance
2016-09-26 15:39 temporary table vs array performance dbyzaa@163.com <dbyzaa@163.com>
@ 2016-09-26 15:49 ` David G. Johnston <david.g.johnston@gmail.com>
2016-09-26 16:23 ` Re: [HACKERS] temporary table vs array performance David G. Johnston <david.g.johnston@gmail.com>
1 sibling, 1 reply; 4+ messages in thread
From: David G. Johnston @ 2016-09-26 15:49 UTC (permalink / raw)
To: dbyzaa@163.com <dbyzaa@163.com>; +Cc: pgsql-hackers <pgsql-hackers@postgresql.org>; pgsql-performance; pgsql-general <pgsql-general@postgresql.org>
Its considered bad form to post to multiple lists. Please pick the most
relevant one - in this case I'd suggest -general.
On Mon, Sep 26, 2016 at 8:39 AM, dbyzaa@163.com <dbyzaa@163.com> wrote:
>
> Array is not convenient to use in function, whether
> there are other methods can be replaced temp table in function
>
>
I have no difficulty using arrays in functions.
As for "other methods" - you can use CTE (WITH) to create a truly local
table - updating the catalogs by using a temp table is indeed quite
expensive.
WITH vals AS ( VALUES (1, 'lw'), (2, 'lw2') )
SELECT * FROM vals;
David J.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: [HACKERS] temporary table vs array performance
2016-09-26 15:39 temporary table vs array performance dbyzaa@163.com <dbyzaa@163.com>
2016-09-26 15:49 ` Re: [HACKERS] temporary table vs array performance David G. Johnston <david.g.johnston@gmail.com>
@ 2016-09-26 16:23 ` David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 0 replies; 4+ messages in thread
From: David G. Johnston @ 2016-09-26 16:23 UTC (permalink / raw)
To: 邓彪 <dbyzaa@163.com>; pgsql-general@postgresql.org <pgsql-general@postgresql.org>
On Mon, Sep 26, 2016 at 9:18 AM, 邓彪 <dbyzaa@163.com> wrote:
> we have to do dml in temp table,the CTE is not fit
>
>
Moving this to -general only...
Please direct all replies to the list.
You are asking for help but not providing any context for what your
requirements are. You are not likely to get good help.
Best case, supply a working function (self contained test case) that does
exactly what you need it to do but uses a temporary table and performs
badly. Lacking that at least attempt to describe your problem and not just
point out that creating temporary tables is expensive.
David J.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: [HACKERS] temporary table vs array performance
2016-09-26 15:39 temporary table vs array performance dbyzaa@163.com <dbyzaa@163.com>
@ 2016-09-26 16:16 ` Pavel Stehule <pavel.stehule@gmail.com>
1 sibling, 0 replies; 4+ messages in thread
From: Pavel Stehule @ 2016-09-26 16:16 UTC (permalink / raw)
To: dbyzaa@163.com <dbyzaa@163.com>; +Cc: pgsql-hackers <pgsql-hackers@postgresql.org>; pgsql-performance; pgsql-general <pgsql-general@postgresql.org>
2016-09-26 17:39 GMT+02:00 dbyzaa@163.com <dbyzaa@163.com>:
> 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
>
>
Temporary tables are pretty expensive - from more reasons, and horrible
when you use fresh table for two rows only. More if you recreate it every
transaction.
More often pattern is create first and delete repeatedly. Better don't use
temp tables when it is necessary. It is one reason why PostgreSQL supports
a arrays. Partially - PostgreSQL arrays are analogy to T-SQL memory tables.
Regards
Pavel
>
> ------------------------------
> dbyzaa@163.com
>
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2016-09-26 16:23 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2016-09-26 15:39 temporary table vs array performance dbyzaa@163.com <dbyzaa@163.com>
2016-09-26 15:49 ` David G. Johnston <david.g.johnston@gmail.com>
2016-09-26 16:23 ` David G. Johnston <david.g.johnston@gmail.com>
2016-09-26 16:16 ` Pavel Stehule <pavel.stehule@gmail.com>
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