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
