Re: ask: select right(column) ???

On Mon, Feb 16, 2009 at 07:10:11AM -0800, Lennin Caro wrote:
> you can use the substring function, like this
>
> select 'test123',substring('test123' from '...$')
>
> this return '123'

Note that regexps are slower than substrings; as an example, I did:

SELECT COUNT(s) FROM (
SELECT 'test'::text AS s
FROM generate_series(1,100000) n
OFFSET 0) x;

We have to put the "OFFSET 0" in to force evaluation otherwise PG is
smart enough to optimize code away and invalidate the test. I replaced
the "COUNT(s) with various exressions to see how it performed:

test expression
a COUNT(s)
b COUNT(substr(s))
c COUNT(substr(s,char_length(s)-3+1))
d COUNT(substring(s from '...$'))

Over several iterations:

test mean stddev
a 72.2 1.09
b 109.9 0.75
c 140.2 1.19
d 569.2 59.46

Not sure why I'm getting so much variance on the last run, strange.
Anyway... Also note that because PG is nice about expanding SQL
functions, test "c" is the same as calling the right() function I
defined earlier. I got a mean of 146.2 and a standard deviation of 9.04
so they're basically the same.

The basic string functions (substr and char_length) take about 0.4
microseconds to execute on my computer, and the regex function about 10
times as long at just under 0.5 microseconds.

The useful result being that substring(s from pattern) is easy to use,
and for small numbers of rows (i.e. less than a few thousand) you're
not going to notice much difference in performance. It's only when you
start dealing with a hundred thousand or so rows the difference is going
to be really noticeable.

--
Sam http://samason.me.uk/