Re: non-static LIKE patterns

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: hamann(dot)w(at)t-online(dot)de
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: non-static LIKE patterns
Date: 2012-04-12 13:55:58
Message-ID: 1397.1334238958@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

hamann(dot)w(at)t-online(dot)de writes:
> Tom Lane wrote:
> If you want it to be bulletproof, what I'd think about is something like
> WHERE second.path LIKE quote_like(first.path)||'%'

> Just out of curiosity: wouldn't that (as well as using non-static like)
> be an enormous performance problem?

Well, it won't be free, but I think you've already doomed yourself to
a not-very-bright plan by using LIKE in this way at all.

In any case, as a wise man once said, you can make it run arbitrarily
fast if it doesn't have to give the right answer. Correctness trumps
any micro-optimization questions, so if you have to have prefix matching
of this sort, it's gonna cost ya somehow.

Actually, if the only case you're worried about is prefix match, you
could do it in substring style:

WHERE second.path = substring(first.path, 1, length(second.path))

(better double-check the substring syntax, I'm too lazy to). This is
still going to completely suck on a macro level: there's still no way to
perform the join except by tediously iterating through every combination
of rows. But it'll likely outrun any LIKE-based solution by some
percentage.

regards, tom lane

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Ivan Evtuhovich 2012-04-12 14:20:20 Two entries with the same primary key
Previous Message Merlin Moncure 2012-04-12 13:55:26 Re: Searchable chess positions in a Postgress DB