X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 7FBE49DC816 for ; Tue, 24 Jan 2006 04:57:04 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 05934-08 for ; Tue, 24 Jan 2006 04:57:05 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from wolff.to (wolff.to [66.93.197.194]) by postgresql.org (Postfix) with SMTP id 17F609DCA4E for ; Tue, 24 Jan 2006 04:57:01 -0400 (AST) Received: (qmail 5606 invoked by uid 500); 24 Jan 2006 09:04:39 -0000 Date: Tue, 24 Jan 2006 03:04:39 -0600 From: Bruno Wolff III To: George Pavlov Cc: pgsql-sql@postgresql.org Subject: Re: non-equi self-join optimization Message-ID: <20060124090439.GA2625@wolff.to> Mail-Followup-To: Bruno Wolff III , George Pavlov , pgsql-sql@postgresql.org References: Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: User-Agent: Mutt/1.4.2.1i X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.07 required=5 tests=[AWL=0.070] X-Spam-Score: 0.07 X-Spam-Level: X-Archive-Number: 200601/151 X-Sequence-Number: 23940 On Tue, Jan 17, 2006 at 12:57:30 -0800, George Pavlov wrote: > I have a table of names with two subsets of entities. I want to find > those names from set 1 that are substrings of names from set 2 from the > same table. Basically the pared down query I want is something like > this: > > select t1.myname, t2.myname > from mytable t1 > inner join mytable t2 > on position (t1.myname in t2.myname) > 0 > where t1.flag = 1 > and t2.flag = 2 > ; > > I have gone through a few variations on the theme, but none perform too > well. Any advice on the best way to optimize a query like this would be > appreciated. I wouldn't expect this to be fast. You would need some sort of index on which substrings are in which names in table 2 to be able to use an index scan. You could build a table for this, but this might be worse for you than what you are doing now.