Custom Operator for citext LIKE predicates question
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? Also, could this not be incorporated into postgres natively? I'm posting the old and new explain plans; New explain; QUERY PLAN Aggregate (cost=874327.76..874327.77 rows=1 width=8) (actual time=21.952..21.954 rows=1 loops=1)-> Nested Loop (cost=1620.95..874284.13 rows=17449 width=0) (actual time=6.259..21.948 rows=9 loops=1)-> Bitmap Heap Scan on t775 b1 (cost=1620.39..525029.25 rows=45632 width=35) (actual time=6.212..8.189 rows=13 loops=1)Recheck Cond: ((c240001002 ~~ 'smp%'::citext) OR (c20020 ~~ 'smp%'::citext) OR (c20001 ~~ 'smp%'::citext))Rows Removed by Index Recheck: 259Filter: ((c400079600 <> 'ABC_BUSINESSSERVICE'::citext) AND (c400127400 = 'ABC.ASSET'::citext) AND ((c11 = 'Mrictton Global'::citext) OR (c11 = 'ABCOpsMonitoring'::citext) OR (c11 = 'Mrictton'::citext) OR (c11 = 'Mrictton EITTE'::citext) OR (c11 = 'Mrictton Finance'::citext) OR (c11 = 'Mrictton Generic Services and Support'::citext) OR (c11 = 'Mrictton Global'::citext) OR (c11 = 'Mrictton Global Demo Solutions'::citext) OR (c11 = 'Mrictton HR Direct'::citext) OR (c11 = 'Mrictton Marketing and Communications'::citext) OR (c11 = 'Ericsson Master Data Management'::citext) OR (c11 = 'Mrictton OHS'::citext) OR (c11 = 'Mrictton Patents and Licensing'::citext) OR (c11 = 'Mrictton Sales'::citext) OR (c11 = 'MricttonSecurity'::citext) OR (c11 = 'Mrictton Shared Services'::citext) OR (c11 = 'Mrictton Sourcing'::citext) OR (c11 = 'Mrict
Re: Custom Operator for citext LIKE predicates question
Thank you for the feedback. In our setup it has actually worked per the explains provided making the query run in milliseconds instead of seconds. We weren't sure if this should be something that could be added natively with future Postgres deployments. Thanks,Efrain J. Berdecia On Thursday, January 13, 2022, 12:58:27 AM EST, Tom Lane wrote: "Efrain J. Berdecia" 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
Re: Custom Operator for citext LIKE predicates question
Good points. At least on the limited testing we did, we were able to get the same answer back with both executions; at least for the use cases we tested. We are still doing more thourough testing. This is an application that is been ported from MS SQL server to postgres and apparently the migration dba team determined citext was the way to go to maintain MSSQL existing usage of the data in the columns. Thanks,Efrain J. Berdecia On Thursday, January 13, 2022, 10:10:38 AM EST, Tom Lane wrote: "Efrain J. Berdecia" writes: > In our setup it has actually worked per the explains provided making the > query run in milliseconds instead of seconds. To me, "work" includes "get the right answer". I do not think you are getting the same answers that citext would normally provide. If you don't care about case-insensitivity, why don't you just use plain text? regards, tom lane
Add create and update timestamp to all objects
Are there any plans to add a create and last updated time stamp field to any and all objects in postgres? Possibly even adding a updated_by documenting which role created and last updated the object. All done natively and without the need for extra extensions. Thanks in advance.
