Date: Thu, 25 Mar 2021 06:09:49 -0700 (MST)
From: Jim Finnerty <jfinnert@amazon.com>
To: pgsql-hackers@postgresql.org
Message-ID: <1616677789675-0.post@n3.nabble.com>
In-Reply-To: <54ef5bfe-043e-4328-9d70-5818789838f4@manitou-mail.org>
References: <cdd0ed2e-1d98-6fc4-07f1-23f19f53b201@2ndquadrant.com>
 <5c7f51b0-6788-4ffd-b9c4-23b710373140@manitou-mail.org>
 <fd54ad77-fb1a-c77d-1371-d7850562b080@2ndquadrant.com>
 <440ec733-d068-46d3-9756-f5f692aba7c8@manitou-mail.org>
 <99818c53-d10f-eb0b-bcb1-fd5d60f6dd96@2ndquadrant.com>
 <f2b3b8e8-12c4-ceb7-6803-b4af6eb2cb71@2ndquadrant.com>
 <693ad06d-db9a-4e59-8131-f823483c5893@manitou-mail.org>
 <1616528764665-0.post@n3.nabble.com> <1616618498263-0.post@n3.nabble.com>
 <54ef5bfe-043e-4328-9d70-5818789838f4@manitou-mail.org>
Subject: Re: insensitive collations
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable
Archived-At: 
 <https://www.postgresql.org/message-id/1616677789675-0.post%40n3.nabble.com>
Precedence: bulk

The two ideas can be combined to create a workaround for accent-sensitive
nondeterministic collations that enables an ordinary btree to be exploited
if available, and also provides the full LIKE logic in either case-sensitiv=
e
or case-insensitive collations:

SELECT * FROM locations WHERE location LIKE 'midi-Pyr=C3=A9n%=C3=A9es';

becomes:=20

SELECT * FROM locations=20
WHERE=20
    location COLLATE "C" ILIKE 'midi-Pyr=C3=A9n%=C3=A9es'  AND             =
         =20
-- For CI collations only
    location BETWEEN 'midi-Pyr=C3=A9n' AND 'midi-Pyr=C3=A9n' || E'\uFFFF'; =
        --
exploitable by ordinary btree

LIKE would also be valid for a case sensitive collation, but then the
collation would be CS_AS, so it would be deterministic and no transform
would be needed.

The expression above produces a good plan, but EXPLAIN complains if the
concatenated expression is not a valid character for the current
client_encoding, which I had set to WIN1252 to display the accented
characters properly on the client:

babel=3D# SELECT * FROM locations=20
babel-# WHERE=20
babel-#     location COLLATE "C" ILIKE 'midi-Pyr=C3=A9n%=C3=A9es'  AND     =
                 =20
-- For CI collations only
babel-#     location BETWEEN 'midi-Pyr=C3=A9n' AND 'midi-Pyr=C3=A9n' || E'\=
uFFFF';       =20
-- exploitable by ordinary btree
    location    =20
-----------------
 Midi-Pyr=C3=A9n=C3=A9es
 midi-Pyr=C3=A9n=C3=A9es
(2 rows)

babel=3D# EXPLAIN VERBOSE SELECT * FROM locations=20
babel-# WHERE=20
babel-#     location COLLATE "C" ILIKE 'midi-Pyr=C3=A9n%=C3=A9es'  AND     =
                 =20
-- For CI collations only
babel-#     location BETWEEN 'midi-Pyr=C3=A9n' AND 'midi-Pyr=C3=A9n' || E'\=
uFFFF';       =20
-- exploitable by ordinary btree
ERROR:  character with byte sequence 0xef 0xbf 0xbf in encoding "UTF8" has
no equivalent in encoding "WIN1252"

reset client_encoding;

babel=3D# EXPLAIN VERBOSE SELECT * FROM locations=20
WHERE=20
    location COLLATE "C" ILIKE 'midi-Pyr=C3=A9n%=C3=A9es'  AND             =
         =20
-- For CI collations only
    location BETWEEN 'midi-Pyr=C3=A9n' AND 'midi-Pyr=C3=A9n' || E'\uFFFF'; =
        --
exploitable by ordinary btree
                                                 QUERY PLAN                =
                                =20
---------------------------------------------------------------------------=
---------------------------------
 Index Only Scan using *location_index* on public.locations=20
(cost=3D0.13..8.15 rows=3D1 width=3D18)
   Output: location
   Index Cond: ((locations.location >=3D 'midi-Pyr=C3=A9n'::text) AND
(locations.location <=3D 'midi-Pyr=C3=A9n=EF=BF=BF'::text))
   Filter: ((locations.location)::text ~~* 'midi-Pyr=C3=A9n%=C3=A9es'::text=
)
(4 rows)


-----
Jim Finnerty, AWS, Amazon Aurora PostgreSQL
--
Sent from: https://www.postgresql-archive.org/PostgreSQL-hackers-f1928748.h=
tml