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

On Wed, Jan 19, 2011 at 2:58 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>> On Wed, Jan 19, 2011 at 12:10 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>>> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>>>> Yeah.  Many-times-repeated detoasting is really bad, and this is not
>>>> the only place in the backend where we have this problem.  :-(
>
>>> Yeah, there's been some discussion of a more general solution, and I
>>> think I even had a trial patch at one point (which turned out not to
>>> work terribly well, but maybe somebody will have a better idea someday).
>
>> I'm pretty doubtful that there's going to be a general solution to
>> this problem - I think it's going to require gradual refactoring of
>> problem spots.
>
> Do you remember the previous discussion?  One idea that was on the table
> was to make the TOAST code maintain a cache of detoasted values, which
> could be indexed by the toast pointer OIDs (toast rel OID + value OID),
> and then PG_DETOAST_DATUM might give back a pointer into the cache
> instead of a fresh value.  In principle that could be done in a fairly
> centralized way.  The hard part is to know when a cache entry is not
> actively referenced anymore ...

I do remember that discussion. Aside from the problem you mention, it
also seems that maintaining the hash table and doing lookups into it
would have some intrinsic cost.

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