On Thu, Aug 30, 2012 at 1:56 AM, Nicola Cisternino <ncister@xxxxxxxxxx> wrote: > The same query using " .... LIKE <value> ...." is completed in 15 ms while > using " .... ILIKE <value> ...." the execution time is 453 ms .... Sounds to me like (pun not intended) there's an index that's being used in one case and not in the other. But taking this back a step: Do you really need case-insensitive comparisons? They become pretty much impossible once you start looking at internationalization. Sure, it's easy in ASCII. You just mask off one bit and off you go. But truly case insensitive matching gets really hairy. Can you redo things with case sensitive searches, possibly with some forcing of case in simple situations? For instance, you accept a name prefix from the user, look at it and find that it's all ASCII; lower-case it, then upper-case the first letter, then add a percent sign, and use a case-sensitive LIKE. That's going to produce correct results in most cases, and is way faster than truly case insensitive searching. ChrisA -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
