pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feednon-equi self-join optimization
2+ messages / 2 participants
[nested] [flat]
* non-equi self-join optimization
@ 2006-01-17 20:57 George Pavlov <gpavlov@mynewplace.com>
2006-01-24 09:04 ` Re: non-equi self-join optimization Bruno Wolff III <bruno@wolff.to>
0 siblings, 1 reply; 2+ messages in thread
From: George Pavlov @ 2006-01-17 20:57 UTC (permalink / raw)
To: pgsql-sql
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.
Thanks!
George
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: non-equi self-join optimization
2006-01-17 20:57 non-equi self-join optimization George Pavlov <gpavlov@mynewplace.com>
@ 2006-01-24 09:04 ` Bruno Wolff III <bruno@wolff.to>
0 siblings, 0 replies; 2+ messages in thread
From: Bruno Wolff III @ 2006-01-24 09:04 UTC (permalink / raw)
To: George Pavlov <gpavlov@mynewplace.com>; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2006-01-24 09:04 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-01-17 20:57 non-equi self-join optimization George Pavlov <gpavlov@mynewplace.com>
2006-01-24 09:04 ` Bruno Wolff III <bruno@wolff.to>
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