Am 10.05.2010 11:50 schrieb Alban Hertroys:
> On 10 May 2010, at 24:01, Christoph Zwerschke wrote:
>
>> select * from b join a on b.txt like a.txt||'%'
>>
>> 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?
How exactly? I tried this:
substr(b.txt, 1, length(a.txt)) = a.txt
but it cannot be optimized and results in a nested loop, too.
It only works with a fixed length:
substr(b.txt, 1, 3) = a.txt
So theoretically I could do something like
select * from b join a
on substr(b.txt, 1, 1) = a.txt and length(b.txt) = 1
union select * from b join a
on substr(b.txt, 1, 2) = a.txt and length(b.txt) = 2
union select * from b join a
on substr(b.txt, 1, 3) = a.txt and length(b.txt) = 3
union ...
... up to the maximum possible string length in a.txt. Not very elegant.
If the question is not finding text cols in b starting with text cols in
a, but text cols in b starting with text cols in a as their first word,
then the following join condition works very well:
split_part(b.txt, ' ', 1) = a.txt
But I'm still looking for a simple solution to the original problem.
-- Christoph
