Re: PostgreSQL SQL Tricks: faster urldecode

On Fri, Sep 20, 2013 at 10:26 AM, Marc Mamin <M(dot)Mamin(at)intershop(dot)de> wrote:
> Hi,
> here is a function which is about 8 x faster than the one described in the PostgreSQL SQL Tricks
> ( http://postgres.cz/wiki/PostgreSQL_SQL_Tricks#Function_for_decoding_of_url_code )
>
> The idea is to handle each encoded/not_encoded parts in bulk rather than spliting on each character.
>
> urldecode_arr:
> Seq Scan on lt_referrer (actual time=1.966..17623.979 rows=65717 loops=1)
>
> urldecode:
> Seq Scan on lt_referrer (actual time=4.846..144445.292 rows=65717 loops=1)

very nice. Basically it comes down to this: all non-trivial regex
replacements require decomposition of the string into an array because
regexp_replace() is unable to do any kind of transformation on the
string. This is a crippling limitation relative to first-class regex
languages like perl; postgres string translation functions are
invisible to the regex engine. I have no idea if this is fixable (I
dimly recall Tom explaining why it might not be).

merlin