Robert James <srobertjames@xxxxxxxxx> wrote: > On 8/14/13, Kevin Grittner <kgrittn@xxxxxxxxx> wrote: >> Robert James <srobertjames@xxxxxxxxx> wrote: >> >>> I'm confused: What's the difference between >>> col LIKE 'foo%' >>> and >>> col LIKE f1 || '%' >>> ? >> >> The planner knows that 'foo%' doesn't start with a wildcard. >> >>> Either way, it's anchored to the beginning of the string. >> >> Not necessarily. > > I see, yes, f1 might include a wildcard. Exactly. > Is there a way to escape f1 so that wildcards aren't interpreted? > That's anyway the behavior I need, of course. And will that help > the planner? I don't think there is any way to get that to work in general, and in particular you are trying to have the right-hand side of the LIKE treated as having escapes; it would be a lot to ask of the planner to somehow recognize that part of the result of the (concatenation) expression should be treated as escaped and part not. > What type of index will help the planner here? Well, with the query as you have it, you might get a trigram index to be of some help, but I think you might want to give up on LIKE or regular expressions. Perhaps you could do a range test directly, rather than wrangling the wildcards: (col >= f1 AND col <= (f1 || 'zzz')) You would probably want to write a function to calculate that ending value; I'm just trying to give a rough suggestion here. The idea is to avoid the danger of wildcards in the f1 values by constructing the range without scanning for special characters and basing the test on that. You could *also* do the LIKE test if desired. -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
