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 <[email protected]> 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
