Finding rows with text columns beginning with other text columns

From: Christoph Zwerschke <cito(at)online(dot)de>
To: pgsql-general(at)postgresql(dot)org
Subject: Finding rows with text columns beginning with other text columns
Date: 2010-05-09 22:01:43
Message-ID: 4BE730C7.7090700@online.de
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Jonathan Vanasco 2010-05-10 00:09:58 question about unique indexes
Previous Message Simon Riggs 2010-05-09 20:42:47 List traffic