Re: Unaccent performance

On 2013-06-21 22:52:04 +0100, Thom Brown wrote:
> > CREATE OR REPLACE FUNCTION public.myunaccent(sometext text)
> > RETURNS text
> > LANGUAGE sql
> > IMMUTABLE
> > AS $function$
> > SELECT
> > replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(sometext,'ą','a'),'Ą','A'),'ă','a'),'Ă','A'),'ā','a'),'Ā','A'),'æ','a'),'å','a'),'ä','a'),'ã','a'),'â','a'),'á','a'),'à','a'),'Æ','A'),'Å','A'),'Ä','A'),'Ã','A'),'Â','A'),'Á','A'),'À','A')
> > ;
> > $function$
> >

> Another test passing in a string of 100000 characters gives the following
> timings:
>
> unaccent: 240619.395 ms
> myunaccent: 785.505 ms

The reason for that is that unaccent is 'stable' while your function is
'immutable', so the planner recognizes that and computes it only once
since you're always passing the same text string to it.

> Another test inserting long text strings into a text column of a table
> 100,000 times, then updating another column to have that unaccented value
> using both methods:
>
> unaccent: 3867.306 ms
> myunaccent: 43611.732 ms

Whereas it cannot recognize that in this case.

Greetings,

Andres Freund

--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services