Re: Custom Operator for citext LIKE predicates question

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Efrain J(dot) Berdecia" <ejberdecia(at)yahoo(dot)com>
Cc: "pgsql-hackers(at)lists(dot)postgresql(dot)org" <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Subject: Re: Custom Operator for citext LIKE predicates question
Date: 2022-01-13 05:58:17
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

"Efrain J. Berdecia" <ejberdecia(at)yahoo(dot)com> writes:
> After attempting to use gin and gist indexes for our queries that run against citext columns, our team has come up with the following to make our queries run from 2 mins to 25ms;CREATE EXTENSION pg_trgmCREATE EXTENSION btree_gin --may not be needed, checking
> CREATE OPERATOR CLASS gin_trgm_ops_ci_newFOR TYPE citext USING ginASOPERATOR 1 % (text, text),FUNCTION 1 btint4cmp (int4, int4),FUNCTION 2 gin_extract_value_trgm (text, internal),FUNCTION 3 gin_extract_query_trgm (text, internal, int2, internal, internal, internal, internal),FUNCTION 4 gin_trgm_consistent (internal,int2, text, int4, internal, internal, internal, internal),STORAGE int4;
> ALTER OPERATOR FAMILY gin_trgm_ops_ci_new USING gin ADDOPERATOR 3 ~~ (citext, citext),OPERATOR 4 ~~* (citext, citext);ALTER OPERATOR FAMILY gin_trgm_ops_ci_new USING gin ADDOPERATOR 7 %> (text, text),FUNCTION 6 (text,text) gin_trgm_triconsistent (internal, int2, text, int4, internal, internal, internal);

> Our question is, does anyone see any flaw on this? 

Umm ... does it actually work? I'd expect that you get case-sensitive
comparison behavior in such an index, because those support functions
are for plain text and they're not going to know that you'd like
case-insensitive behavior.

You generally can't make a new gin or gist opclass without actually
writing some C code, because the support functions embody all
the semantics of the operators.

regards, tom lane

In response to


Browse pgsql-hackers by date

  From Date Subject
Next Message Bharath Rupireddy 2022-01-13 06:02:53 Re: Avoid erroring out when unable to remove or parse logical rewrite files to save checkpoint work
Previous Message Andres Freund 2022-01-13 05:44:33 Re: psql - add SHOW_ALL_RESULTS option