Re: Re: patch: fix performance problems with repated decomprimation of varlena values in plpgsql

2011/1/25 Tom Lane <tgl@sss.pgh.pa.us>:
> Robert Haas <robertmhaas@gmail.com> writes:
>> On Tue, Jan 25, 2011 at 10:03 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>>> The arguments that were made against this were about maintenance costs
>>> and code footprint.  Claims about performance are not really relevant,
>>> especially when they're entirely unsupported by evidence.
>
>> How much evidence do you need to the effect that detoasting a value
>> that's never used will hurt performance?
>
> I agree that at some microscopic level it will cost something.  What's
> not been shown is that there's any significant cost in any real-world
> use pattern.  As Pavel said upthread, the main thing here is to get rid
> of cases where there are many many repeated detoastings.  Adding an
> occasional detoast that wouldn't have happened before is a good tradeoff
> for that.  To convince me that we should contort the code to go further,
> you need to show that that's more than a negligible cost.

I did a few tests:

create table a1(a int);
create table a2(a int)

insert into a1 select array_fill(1, array[100]) from generate_series(1,10000);
insert into a2 select array_fill(1, array[10000]) from generate_series(1,10000);

create or replace function s(int[]) returns int as $$
declare s int = 0; i int;
begin for i in array_lower($1,1) .. array_upper($1,1) loop   s := s + $1[i]; end loop; return s;
end;
$$ language plpgsql immutable;

next I tested queries

1, select sum(s(a)) from a1;
2, select sum(s(a)) from a2;

variant a) my first patch - detoast on first usage with avoiding to
useless detoast checking
variant b) my first patch - detoast on first usage without avoiding to
useless detoast checking

time for 1 - about 300 ms, a is bout 1.5% faster than b
time for 2 - about 30000 ms, a is about 3% faster than b

Regards

Pavel Stehule