Message-ID: <ac26649da73eb3b653225c6989355e40d125fdf3.camel@cybertec.at>
Subject: Re: [PATCH] Add support function for containment operators
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Kim Johan Andersson <kimjand@kimmet.dk>, 
	pgsql-hackers@lists.postgresql.org
Date: Thu, 06 Jul 2023 20:19:07 +0200
In-Reply-To: <4e6efd382b3195eb7ea438b7219cc94d9399c0bd.camel@cybertec.at>
References: <94f64d1f-b8c0-b0c5-98bc-0793a34e0851@kimmet.dk>
	 <4e6efd382b3195eb7ea438b7219cc94d9399c0bd.camel@cybertec.at>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.46.4 (3.46.4-1.fc37) 
MIME-Version: 1.0
Archived-At: <https://www.postgresql.org/message-id/ac26649da73eb3b653225c6989355e40d125fdf3.camel%40cybertec.at>
Precedence: bulk

> On Sat, 2023-04-29 at 17:07 +0200, Kim Johan Andersson wrote:
> > I had noticed that performance wasn't great when using the @> or <@=20
> > operators when examining if an element is contained in a range.
> > Based on the discussion in [1] I would like to suggest the following=
=20
> > changes:
> >=20
> > This patch attempts to improve the row estimation, as well as opening=
=20
> > the possibility of using a btree index scan when using the containment=
=20
> > operators.

I managed to break the patch:

CREATE DATABASE czech ICU_LOCALE "cs-CZ" LOCALE "cs_CZ.utf8" TEMPLATE templ=
ate0;

\c czech

CREATE TYPE textrange AS RANGE (SUBTYPE =3D text, SUBTYPE_OPCLASS =3D text_=
pattern_ops);

CREATE TABLE tx (t text);

INSERT INTO tx VALUES ('a'), ('c'), ('d'), ('ch');

EXPLAIN SELECT * FROM tx WHERE t <@ textrange('a', 'd');

                     QUERY PLAN                    =20
=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=
=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=
=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=
=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=
=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=
=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=
=E2=95=90=E2=95=90
 Seq Scan on tx  (cost=3D0.00..30.40 rows=3D7 width=3D32)
   Filter: ((t >=3D 'a'::text) AND (t < 'd'::text))
(2 rows)

SELECT * FROM tx WHERE t <@ textrange('a', 'd');
ERROR:  could not determine which collation to use for string comparison
HINT:  Use the COLLATE clause to set the collation explicitly.


The replacement operators are wrong; it should be ~>=3D~ and ~<~ .

Also, there should be no error message.
The result should be 'a', 'c' and 'ch'.

Yours,
Laurenz Albe