2010/11/18 Tom Lane <tgl@sss.pgh.pa.us>:
> Pavel Stehule <pavel.stehule@gmail.com> writes:
>> what is a slow:
>
>> a) repeated detoasting - access with subscripts - maybe detoasted
>> values can be cached?
>> b) evaluation of SRF expression - maybe call of SRF function can be
>> simple expression,
>> c) faster evaluation ro query
>
>> The most important is @a.
>
> Really? Becase AFAICS array_unnest only detoasts the source array once,
> and saves the value between calls.
>
> array_unnest doesn't currently have any smarts about fetching slices
> of an array. I'm not sure how useful that would be in practice, since
> (1) in most usages you probably run the function to the end and fetch
> all the values anyway; (2) it's hard to see how to optimize that way
> if the elements are varlena, which they most likely are in most usages
> where this could possibly be a win. But if Cedric's use-case is really
> worth optimizing, I'd sure rather see the smarts for it in the general
> purpose array_unnest function instead of buried in plpgsql's FOR logic.
My use case is the following:
I have text[] data containing around 50k field.
Executing my array_filter (which is probably not as fast as what Pavel
did) is the same thing as executing unnest, except that during the
array walk, I apply a callback function and increment an internal
counter up to the p_limit parameter when callback function success.
So that it stops walking the array as soon as p_limit counter is full,
or there are no more elements to walk to in the array.
1) At a maximum it is slow like unnest (plus callback overhead), at a
minimum it find quickly and the gain is huge. Don't have the exact
numbers right here, but (from memory) the durations are between Oms
and 45 milliseconds (20M rows, of 50k field text array, not very long
text < 100 char, 15k calls per second, depending on items to walk in
the array, linear). WIth just unnest, a minimum is 45 ms per query.
2) DETOAST_SLICE I don't know the internals here
I have a concrete usage of what Pavel did.
And I agree that this is fast and easy way to handle the real issues behind :/
>
> regards, tom lane
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>
--
Cédric Villemain 2ndQuadrant
http://2ndQuadrant.fr/ PostgreSQL : Expertise, Formation et Support