Mailing-List: contact user-help@cayenne.apache.org; run by ezmlm
Precedence: bulk
Reply-To: user@cayenne.apache.org
From: "Musall, Maik" <maik@selbstdenker.ag>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: quoted-printable
Mime-Version: 1.0 (Mac OS X Mail 10.3 \(3273\))
Subject: Re: ILIKE vs. upper/lower with PostgreSQL
Date: Fri, 15 Dec 2017 17:00:34 +0100
References: <DC6BC0C4-6CD2-4F2F-A924-4D3EDE39B078@selbstdenker.ag>
 <CAMi+qzCOGYW0kAWCF0scN-QqAwxsjwJBhC9D7N3VAvMvc6O9ng@mail.gmail.com>
To: user@cayenne.apache.org
In-Reply-To: <CAMi+qzCOGYW0kAWCF0scN-QqAwxsjwJBhC9D7N3VAvMvc6O9ng@mail.gmail.com>
Message-Id: <B44FD316-50A6-4E54-88A3-46E57980DB34@selbstdenker.ag>
archived-at: Fri, 15 Dec 2017 16:00:56 -0000

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 =
<ntimofeev@objectstyle.com>:
>=20
> Hi,
>=20
> 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.
>=20
> I.e. you can do something like this:
>    =
ObjectSelect.query(MyTable.class).where(MyTable.MYCOLUMN.upper().like("FOO=
%"))
>=20
>=20
> On Fri, Dec 15, 2017 at 11:44 AM, Musall, Maik <maik@selbstdenker.ag> =
wrote:
>> Hi all,
>>=20
>> 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:
>>=20
>> EOF:     SELECT * FROM mytable t0 WHERE UPPER(mycolumn) LIKE =
UPPER('foo%')
>> Cayenne: SELECT * FROM mytable t0 WHERE mycolumn ILIKE 'foo%'
>>=20
>> 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.
>>=20
>> 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).
>>=20
>> 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?
>>=20
>> Thanks
>> Maik
>>=20
>=20
>=20
>=20
> --=20
> Best regards,
> Nikita Timofeev