cayenne-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Musall, Maik" <>
Subject Re: ILIKE vs. upper/lower with PostgreSQL
Date Fri, 15 Dec 2017 16:00:34 GMT
Hi Nikita,

thanks for the reponse. However, changing hundreds of expressions to something less elegant
isn't really a compelling solution to me.

So Hugi (who works on this project with me) solved it by creating a custom alternative to
PostgresQualifierTranslator which just omits the ILIKE-related code. That way I get the regular
SQL using UPPER(), which is easily indexable.

ILIKE is certainly useful for full text searches with CLOB/TEXT values and in combination
with GiST/GIN indexes, but IMHO not for regular case-insensitive
queries using b-tree indexes. So I'm not sure that PostgresQualifierTranslator does the right
thing here.

How is everyone else indexing varchar columns for case-insensitive queries on PostgreSQL?


> Am 15.12.2017 um 12:21 schrieb Nikita Timofeev <>:
> Hi,
> I don't think it will be easy to change Cayenne translator behavior
> (but still should be possible if necessary).
> It may be easier to use upper().like() functions instead of
> likeIgnoreCase() in your case.
> I.e. you can do something like this:
>    ObjectSelect.query(MyTable.class).where(MyTable.MYCOLUMN.upper().like("FOO%"))
> On Fri, Dec 15, 2017 at 11:44 AM, Musall, Maik <> wrote:
>> Hi all,
>> after migrating an EOF application to Cayenne, I noticed many queries running much
slower than before and with more load on the database. Turns out that Cayenne generates queries
using ILIKE where EOF used to generate UPPER() comparisons. Example:
>> EOF:     SELECT * FROM mytable t0 WHERE UPPER(mycolumn) LIKE UPPER('foo%')
>> Cayenne: SELECT * FROM mytable t0 WHERE mycolumn ILIKE 'foo%'
>> The database is PostgreSQL 9.5, and I used to cover the UPPER() queries with function-based
indexes on UPPER(column), which used to work very well.
>> ILIKE is not as easy to index with PostgreSQL, because it's semantically different,
especially with languages that don't have a simple alphabet. There are GiST and GIN index
types in PostgreSQL, but those have other drawbacks (too many hits for short columns, needing
additional table accesses, no sorting by index, expensive updates in the case of GiST, and
so on).
>> So, my question is: can I change what Cayenne generates here and generate UPPER()
or LOWER() comparisons so that I can continue using the existing indexes, and what would be
the recommended way to do that?
>> Thanks
>> Maik
> -- 
> Best regards,
> Nikita Timofeev

View raw message