cayenne-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Nikita Timofeev <ntimof...@objectstyle.com>
Subject Re: ILIKE vs. upper/lower with PostgreSQL
Date Fri, 15 Dec 2017 11:21:32 GMT
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 <maik@selbstdenker.ag> 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

Mime
View raw message