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?
Maik
> Am 15.12.2017 um 12:21 schrieb Nikita Timofeev <ntimof...@objectstyle.com>:
>
> 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 <m...@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
