Thread: String handling function, substring vs. substr

Hi all,

I want to call one of the two functions above many times (in an aggregate
function) and it says in the manual pages that substr is the same as
substring.

Does this mean that substr calls substring internally?? Or is it the other
way around?? Or are they independent of each other??

So in short, which is faster to use??

Thanks,

Archie

arsi@aranzo.netg.se wrote:
>
> Hi all,
>
> I want to call one of the two functions above many times (in an
> aggregate function) and it says in the manual pages that substr is the
> same as substring.
>
> Does this mean that substr calls substring internally?? Or is it the
> other way around?? Or are they independent of each other??
>
> So in short, which is faster to use??
>
> Thanks,
>
> Archie
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend

I normally trim the cruft from the end of emails i'm replying to but
this time it seemed like a good idea to leave it.

test=# EXPLAIN ANALYZE SELECT substring('foobar', 2);
                                      QUERY PLAN
------------------------------------------------------------------------------------
  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.011..0.013
rows=1 loops=1)
  Total runtime: 0.042 ms
(2 rows)

test=# EXPLAIN ANALYZE SELECT substr('foobar', 2);
                                      QUERY PLAN
------------------------------------------------------------------------------------
  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.010..0.012
rows=1 loops=1)
  Total runtime: 0.043 ms
(2 rows)

test=# EXPLAIN ANALYZE SELECT substring('foobar', 2);
                                      QUERY PLAN
------------------------------------------------------------------------------------
  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.012..0.014
rows=1 loops=1)
  Total runtime: 0.044 ms
(2 rows)

test=# EXPLAIN ANALYZE SELECT substr('foobar', 2);
                                      QUERY PLAN
------------------------------------------------------------------------------------
  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.011..0.013
rows=1 loops=1)
  Total runtime: 0.043 ms
(2 rows)

Looks like they're pretty evenly matched. I'd try it with something a
bit more complex. You could also prepare a file with the same queries
repeated amny times, using different words and positions. Do a file for
each function. Have psql dump its results into 2 other files and grep
for all the running times for each. Average them out and compare both
averages.

b

brian <brian@zijn-digital.com> writes:
> arsi@aranzo.netg.se wrote:
>> Does this mean that substr calls substring internally?? Or is it the
>> other way around?? Or are they independent of each other??

> Looks like they're pretty evenly matched.

Actually, a bit of poking into the contents of pg_proc will show you
that they are both aliases for the same C function (text_substr_no_len).
So they should be *exactly* the same speed.

            regards, tom lane

Hi all,

> brian <brian@zijn-digital.com> writes:
>> arsi@aranzo.netg.se wrote:
>>> Does this mean that substr calls substring internally?? Or is it the
>>> other way around?? Or are they independent of each other??
>
>> Looks like they're pretty evenly matched.
>
> Actually, a bit of poking into the contents of pg_proc will show you
> that they are both aliases for the same C function (text_substr_no_len).
> So they should be *exactly* the same speed.
>
>             regards, tom lane
>
Thanks Brian for your answer (although that wasn't what I was looking for,
I was looking for Tom's answer).

Thanks Tom for your answer.

Archie