> 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 <@ > > operators when examining if an element is contained in a range. > > Based on the discussion in [1] I would like to suggest the following > > changes: > > > > This patch attempts to improve the row estimation, as well as opening > > the possibility of using a btree index scan when using the containment > > operators.
I managed to break the patch: CREATE DATABASE czech ICU_LOCALE "cs-CZ" LOCALE "cs_CZ.utf8" TEMPLATE template0; \c czech CREATE TYPE textrange AS RANGE (SUBTYPE = text, SUBTYPE_OPCLASS = 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 ════════════════════════════════════════════════════ Seq Scan on tx (cost=0.00..30.40 rows=7 width=32) Filter: ((t >= '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 ~>=~ and ~<~ . Also, there should be no error message. The result should be 'a', 'c' and 'ch'. Yours, Laurenz Albe