pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedString 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
2006-10-01 20:47 ` Re: String handling function, substring vs. substr brian <brian@zijn-digital.com>
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: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 ` Re: String handling function, substring vs. substr Tom Lane <tgl@sss.pgh.pa.us>
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 20:31 String handling function, substring vs. substr arsi@aranzo.netg.se
2006-10-01 20:47 ` Re: String handling function, substring vs. substr brian <brian@zijn-digital.com>
@ 2006-10-01 22:41 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-10-02 16:57 ` Re: String handling function, substring vs. substr arsi@aranzo.netg.se
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-01 20:31 String handling function, substring vs. substr arsi@aranzo.netg.se
2006-10-01 20:47 ` Re: String handling function, substring vs. substr brian <brian@zijn-digital.com>
2006-10-01 22:41 ` Re: String handling function, substring vs. substr Tom Lane <tgl@sss.pgh.pa.us>
@ 2006-10-02 16:57 ` arsi@aranzo.netg.se
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