From: | Bruno Wolff III <bruno(at)wolff(dot)to> |
---|---|
To: | George Pavlov <gpavlov(at)mynewplace(dot)com> |
Cc: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: non-equi self-join optimization |
Date: | 2006-01-24 09:04:39 |
Message-ID: | 20060124090439.GA2625@wolff.to |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
On Tue, Jan 17, 2006 at 12:57:30 -0800,
George Pavlov <gpavlov(at)mynewplace(dot)com> 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.
From | Date | Subject | |
---|---|---|---|
Next Message | PFC | 2006-01-24 10:14:58 | Re: How to implement Microsoft Access boolean (YESNO) |
Previous Message | Greg Stark | 2006-01-24 05:03:48 | Re: How to implement Microsoft Access boolean (YESNO) |