Re: Allow multi-byte characters as escape in SIMILAR TO and SUBSTRING

Поиск
Список
Период
Сортировка
От Heikki Linnakangas
Тема Re: Allow multi-byte characters as escape in SIMILAR TO and SUBSTRING
Дата
Msg-id 53FB3399.2070802@vmware.com
обсуждение исходный текст
Ответ на Re: Allow multi-byte characters as escape in SIMILAR TO and SUBSTRING  (Jeff Davis <pgsql@j-davis.com>)
Ответы Re: Allow multi-byte characters as escape in SIMILAR TO and SUBSTRING
Список pgsql-hackers
On 07/12/2014 05:16 AM, Jeff Davis wrote:
> I was able to see about a 2% increase in runtime when using the
> similar_escape function directly. I made a 10M tuple table and did:
>
>      explain analyze
>        select
> similar_escape('ΣΣΣΣΣΣΣΣΣΣΣΣΣΣΣΣΣΣΣΣΣΣΣΣΣΣΣΣΣΣΣΣΣΣΣΣΣΣΣΣΣΣΣΣΣΣΣΣΣΣΣΣΣΣΣΣΣΣΣΣΣΣΣΣ','#') from t;
>
> which was the worst reasonable case I could think of. (It appears that
> selecting from a table is faster than from generate_series. I'm curious
> what you use when testing the performance of an individual function at
> the SQL level.)

A large table like that is what I usually do. A large generate_series() 
spends a lot of time building the tuplestore, especially if it doesn't 
fit in work_mem and spills to disk. Sometimes I use this to avoid it:

explain analyze      select
similar_escape('ΣΣΣΣΣΣΣΣΣΣΣΣΣΣΣΣΣΣΣΣΣΣΣΣΣΣΣΣΣΣΣΣΣΣΣΣΣΣΣΣΣΣΣΣΣΣΣΣΣΣΣΣΣΣΣΣΣΣΣΣΣΣΣΣ','#') 
from generate_series(1, 10000) a, generate_series(1,1000);

although in my experience it still has somewhat more overhead than a 
straight seqscan because.

- Heikki




В списке pgsql-hackers по дате отправления:

Предыдущее
От: Albe Laurenz
Дата:
Сообщение: Re: Optimization for updating foreign tables in Postgres FDW
Следующее
От: Kevin Grittner
Дата:
Сообщение: Re: LIMIT for UPDATE and DELETE