Re: String handling function, substring vs. substr

От:

brian <brian@zijn-digital.com>

Дата:

1 октября 2006 г. в 17:47:59

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

Re: String handling function, substring vs. substr

От:

Tom Lane <tgl@sss.pgh.pa.us>

Дата:

1 октября 2006 г. в 19:41:57

brian  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

String handling function, substring vs. substr

От:

arsi@aranzo.netg.se

Дата:

1 октября 2006 г. в 17:32:57


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

Re: String handling function, substring vs. substr

От:

arsi@aranzo.netg.se

Дата:

2 октября 2006 г. в 13:59:15


Hi all,

> brian  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