From: | Heikki Linnakangas <hlinnakangas(at)vmware(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | Jeff Davis <pgsql(at)j-davis(dot)com>, <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: Allow multi-byte characters as escape in SIMILAR TO and SUBSTRING |
Date: | 2014-08-25 14:51:51 |
Message-ID: | 53FB4D87.7040507@vmware.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
On 08/25/2014 04:48 PM, Tom Lane wrote:
> Heikki Linnakangas <hlinnakangas(at)vmware(dot)com> writes:
>> 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.
>
> [ scratches head... ] Surely similar_escape is marked immutable, and
> will therefore be executed exactly once in either of these formulations,
> because the planner will fold the expression to a constant.
Yeah, just noticed that myself..
- Heikki
From | Date | Subject | |
---|---|---|---|
Next Message | Heikki Linnakangas | 2014-08-25 15:04:50 | Re: Unwanted LOG during recovery of DROP TABLESPACE REDO |
Previous Message | Heikki Linnakangas | 2014-08-25 14:41:19 | Re: Allow multi-byte characters as escape in SIMILAR TO and SUBSTRING |