Re: Assigning NULL to a record variable

2012/9/20 Tom Lane <tgl@sss.pgh.pa.us>:
> Pavel Stehule <pavel.stehule@gmail.com> writes:
>> 2012/9/20 Tom Lane <tgl@sss.pgh.pa.us>:
>>> I'm not sure what the performance tradeoffs would be ---
>>> some things would get faster and others slower, probably, since field
>>> access would be more work but conversion to/from HeapTuple would get far
>>> cheaper.
>
>> when fields are fix length, then field's update is significantly
>> faster then when RECORD is used
>
> [ shrug... ]  Maybe not if we put a little effort into it.  It would be
> interesting to consider using a TupleTableSlot instead of a bare
> HeapTuple for instance.  In any case you're ignoring the point that
> other things will get faster.

I didn't try to optimize this. But these tests showed important impact.

postgres=# create table foo(a int, b int, c int);
CREATE TABLE
postgres=# insert into foo select 1,2,3 from generate_series(1,100000);
INSERT 0 100000
postgres=# select count(*) from foo;count
--------100000
(1 row)

postgres=# do $$ declare r record; begin for r in select * from foo
loop perform r.a + r.b + r.c; end loop; end; $$ language plpgsql;
DO
Time: 712.404 ms

postgres=# do $$ declare r foo; begin for r in select * from foo loop
perform r.a + r.b + r.c; end loop; end; $$ language plpgsql;
DO
Time: 1617.847 ms

In this case, record is faster - it was used in PL/PSM0 too

postgres=# do $$ declare r record; begin for r in select * from foo
loop r.a := r.b + r.c; end loop; end; $$ language plpgsql;
DO
Time: 170.701 ms

postgres=# do $$ declare r foo; begin for r in select * from foo loop
r.a := r.b + r.c; end loop; end; $$ language plpgsql;
DO
Time: 96.467 ms

or

postgres=# do $$ declare r record; t int; begin for r in select * from
foo loop t := r.b + r.c; end loop; end; $$ language plpgsql;
DO
Time: 127.760 ms

postgres=# do $$ declare r foo; t int; begin for r in select * from
foo loop t := r.b + r.c; end loop; end; $$ language plpgsql;
DO
Time: 87.003 ms

typed composite variable is significantly faster in simple queries (expressions)

But I invite any reduction in this area

regards, Pavel

>
>                         regards, tom lane