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