cayenne-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Musall, Maik" <m...@selbstdenker.ag>
Subject ILIKE vs. upper/lower with PostgreSQL
Date Fri, 15 Dec 2017 08:44:26 GMT
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


Mime
View raw message