X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 7DDC29FA1B0 for ; Tue, 2 May 2006 04:43:35 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 78177-07 for ; Tue, 2 May 2006 04:43:30 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from floppy.pyrenet.fr (floppy.pyrenet.fr [194.116.145.2]) by postgresql.org (Postfix) with ESMTP id A7CC59F9DCD for ; Tue, 2 May 2006 04:43:30 -0300 (ADT) Received: by floppy.pyrenet.fr (Postfix, from userid 106) id 446C8314A3; Tue, 2 May 2006 09:43:29 +0200 (MET DST) From: Sim Zacks X-Newsgroups: pgsql.sql Subject: Re: join on a like Date: Tue, 02 May 2006 10:41:17 +0200 Organization: Hub.Org Networking Services Lines: 13 Message-ID: References: <1146483911.614478.77500@u72g2000cwu.googlegroups.com> Mime-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Complaints-To: usenet@news.hub.org User-Agent: Thunderbird 1.5 (Windows/20051201) In-Reply-To: <1146483911.614478.77500@u72g2000cwu.googlegroups.com> To: pgsql-sql@postgresql.org X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200605/30 X-Sequence-Number: 25061 I guess the real question is: what is more efficient - a join on a LIKE or a PLPGSQL function that has 2 loops, 1 for each bottom level child and one that takes the substring of that child one character at a time to get each parent? It sounds like you are saying that the join will actaully use the index and therefore it is optimal, not just possible. mlartz@gmail.com wrote: > If you use a btree index on the column, you should be able to use it in > the like: > http://www.postgresql.org/docs/8.1/interactive/indexes-types.html. >