Assume we have a table "a" with a text column "txt"
and an index on that column.
A query like the following will then be very perfomant
since it can use the index:
select * from a where txt like 'a%'
(Assume also that the server is using the C locale or the index
is set up with text_pattern_ops, so that this really works.)
Now take a second, similar table "b" (can be the same table).
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?
Thanks,
-- Christoph