Received: from localhost (wm.hub.org [200.46.204.128]) by postgresql.org (Postfix) with ESMTP id 289179FB2D6 for ; Sun, 1 Oct 2006 17:47:57 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.128]) (amavisd-new, port 10024) with ESMTP id 28306-02 for ; Sun, 1 Oct 2006 20:47:52 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey- Received: from tomts5-srv.bellnexxia.net (tomts5.bellnexxia.net [209.226.175.25]) by postgresql.org (Postfix) with ESMTP id CF7599FB2CC for ; Sun, 1 Oct 2006 17:47:51 -0300 (ADT) Received: from [192.168.1.7] ([65.94.32.125]) by tomts5-srv.bellnexxia.net (InterMail vM.5.01.06.13 201-253-122-130-113-20050324) with ESMTP id <20061001204749.IUYV18394.tomts5-srv.bellnexxia.net@[192.168.1.7]> for ; Sun, 1 Oct 2006 16:47:49 -0400 Message-ID: <45202965.3000000@zijn-digital.com> Date: Sun, 01 Oct 2006 16:47:33 -0400 From: brian User-Agent: Mozilla Thunderbird 1.0.7 (X11/20050923) X-Accept-Language: en-us, en MIME-Version: 1.0 To: pgsql-general@postgresql.org Subject: Re: String handling function, substring vs. substr References: <6992E470F12A444BB787B5C937B9D4DF060E5C9B@ca-mail1.cis.local> In-Reply-To: Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests= X-Spam-Level: X-Archive-Number: 200610/23 X-Sequence-Number: 101550 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