From: Bruno Wolff III <bruno@wolff.to>
To: George Pavlov <gpavlov@mynewplace.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: non-equi self-join optimization
Date: Tue, 24 Jan 2006 03:04:39 -0600
Message-ID: <20060124090439.GA2625@wolff.to> (raw)
In-Reply-To: <CCB89282FCE1024EA3DCE687A96A51640445D978@ehost010-6.exch010.intermedia.net>
References: <CCB89282FCE1024EA3DCE687A96A51640445D978@ehost010-6.exch010.intermedia.net>
On Tue, Jan 17, 2006 at 12:57:30 -0800,
George Pavlov <gpavlov@mynewplace.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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: bruno@wolff.to, gpavlov@mynewplace.com
Subject: Re: non-equi self-join optimization
In-Reply-To: <20060124090439.GA2625@wolff.to>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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