Re: PostgreSQL SQL Tricks: faster urldecode

On Fri, Sep 20, 2013 at 10:26 AM, Marc Mamin <M.Mamin@intershop.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