Re: Finding rows with text columns beginning with other text columns

On 10 May 2010, at 24:01, Christoph Zwerschke wrote:

> We want to find all entries in b where txt begins with an
> existing txt entry in a:
>
> select * from b join a on b.txt like a.txt||'%'
>
> On the first glance you would expect that this is performant
> since it can use the index, but sadly it doesn't work.
> The problem seems to be that Postgres can not guarantee that
> column a.txt does not contain a '%', so it cannot optimize.
>
> I feel there should be a performat way to query these entries,
> but I can't come up with anything. Can anybody help me?

Have you tried using substring instead of like?

Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.

!DSPAM:737,4be7d6ec10411051620847!