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

On Sun, Feb 6, 2011 at 8:47 AM, Noah Misch <noah@leadboat.com> wrote:
> On Sun, Feb 06, 2011 at 08:15:30AM -0500, Robert Haas wrote:
>> On Sun, Feb 6, 2011 at 5:52 AM, Noah Misch <noah@leadboat.com> wrote:
>> > 1. Add PLpgSQL_var.should_be_detoasted; check it in plpgsql_param_fetch().
>> > Essentially Pavel's original patch, only with the check logic moved up from
>> > exec_eval_datum() to plpgsql_param_fetch() to avoid bothering a couple other
>> > callers that would not benefit. ?Tom and Robert objected to the new bookkeeping.
>>
>> I don't understand why it's necessary.  It seems to me that the case
>> we're concerned about is when someone is referencing a variable that
>> is toasted which they might later want to reference again.  We're
>> going to have to notice that the value is toasted and detoast it
>> anyway before we can really do anything with it.  So why can't we
>> arrange to overwrite the *source* of the data we're fetching with the
>> detoasted version?
>>
>> I know this is probably a stupid question, but i don't understand the
>> code well enough to see why this can't work.
>
> The detoast currently happens well after PL/pgSQL has handed off the datum.
> Consider this function, my original benchmark when reviewing this patch:
>
>  CREATE OR REPLACE FUNCTION f(runs int) RETURNS void LANGUAGE plpgsql AS $$
>  DECLARE
>        foo     text;
>  BEGIN
>        SELECT c INTO foo FROM t;
>        FOR n IN 1 .. runs LOOP
>                PERFORM foo < 'x';
>        END LOOP;
>  END
>  $$;
>
> Suppose "foo" is toasted.  As the code stands in master, it gets detoasted in
> text_lt().  Certainly we won't overwrite the source back in PL/pgSQL from the
> detoast point in text_lt().

Right, that much seems obvious...

> Pavel's optimization requires that we identify the
> need to detoast the datum earlier and do so preemptively.

I guess I need to look at the patch more.  I'm failing to understand
why that can't be done within one or two functions, without passing
around a new piece of state.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company