pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
String handling function, substring vs. substr
4+ messages / 3 participants
[nested] [flat]

* String handling function, substring vs. substr
@ 2006-10-01 20:31  arsi@aranzo.netg.se
  0 siblings, 1 reply; 4+ messages in thread

From: arsi@aranzo.netg.se @ 2006-10-01 20:31 UTC (permalink / raw)
  To: pgsql-general@postgresql.org


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



^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: String handling function, substring vs. substr
@ 2006-10-01 20:47  brian <brian@zijn-digital.com>
  parent: arsi@aranzo.netg.se
  0 siblings, 1 reply; 4+ messages in thread

From: brian @ 2006-10-01 20:47 UTC (permalink / raw)
  To: pgsql-general@postgresql.org

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



^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: String handling function, substring vs. substr
@ 2006-10-01 22:41  Tom Lane <tgl@sss.pgh.pa.us>
  parent: brian <brian@zijn-digital.com>
  0 siblings, 1 reply; 4+ messages in thread

From: Tom Lane @ 2006-10-01 22:41 UTC (permalink / raw)
  To: brian <brian@zijn-digital.com>; +Cc: pgsql-general@postgresql.org

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



^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: String handling function, substring vs. substr
@ 2006-10-02 16:57  arsi@aranzo.netg.se
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 4+ messages in thread

From: arsi@aranzo.netg.se @ 2006-10-02 16:57 UTC (permalink / raw)
  To: pgsql-general@postgresql.org


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



^ permalink  raw  reply  [nested|flat] 4+ messages in thread


end of thread, other threads:[~2006-10-02 16:57 UTC | newest]

Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-10-01 20:31 String handling function, substring vs. substr arsi@aranzo.netg.se
2006-10-01 20:47 ` brian <brian@zijn-digital.com>
2006-10-01 22:41   ` Tom Lane <tgl@sss.pgh.pa.us>
2006-10-02 16:57     ` arsi@aranzo.netg.se

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox