<html><body><div style="font-family: arial, helvetica, sans-serif; font-size: 12pt; color: #000000"><div>Hi Jeremiah<br></div><div><br data-mce-bogus="1"></div><div>You can try GIN index with trigram option, similar to this (change the table, column, etc. of course):<br data-mce-bogus="1"></div><div>CREATE INDEX m_case_fullTextInfo_idx ON m_case USING gin (fullTextInfo gin_trgm_ops);<br><br data-mce-bogus="1"></div><div>Measure the query before and after, but LIKE should benefit a lot from TRGM ops.<br data-mce-bogus="1"></div><div><br data-mce-bogus="1"></div><div>Regards<br data-mce-bogus="1"></div><div><br data-mce-bogus="1"></div><div>Richard Richter<br data-mce-bogus="1"></div><div>SW engineer<br data-mce-bogus="1"></div><div>evolveum.com<br data-mce-bogus="1"></div><div><br data-mce-bogus="1"></div><div><br></div><hr id="zwchr" data-marker="__DIVIDER__"><div data-marker="__HEADERS__"><b>From: </b>"midPoint General Discussion" <midpoint@lists.evolveum.com><br><b>To: </b>"midPoint General Discussion" <midpoint@lists.evolveum.com><br><b>Cc: </b>"Jeremiah Haywood" <jhaywo1@ilstu.edu><br><b>Sent: </b>Friday, June 23, 2023 5:30:46 PM<br><b>Subject: </b>[midPoint] GIN Indexes in midPoint<br></div><div><br><style><!--
/* Font Definitions */
@font-face
{font-family:"Cambria Math";
panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0in;
font-size:11.0pt;
font-family:"Calibri",sans-serif;}
span.EmailStyle19
{mso-style-type:personal-compose;
font-family:"Calibri",sans-serif;
color:windowtext;}
.MsoChpDefault
{mso-style-type:export-only;
font-family:"Calibri",sans-serif;}
@page WordSection1
{size:8.5in 11.0in;
margin:1.0in 1.0in 1.0in 1.0in;}
div.WordSection1
{page:WordSection1;}
--></style></div><div data-marker="__QUOTED_TEXT__"><div class="WordSection1"><p class="MsoNormal">Hello all,</p><p class="MsoNormal"> </p><p class="MsoNormal">We recently discovered some performance issues that were caused by queries in Midpoint using the <i>LIKE </i>statement. One being the basic user search in the UI and a couple being internal queries we had in the user template. Working with our DBA team we’ve discussed the potential of putting GIN indexes on a few fields to improve performance. Has anyone else used GIN indexes in midPoint to help with query performance?</p><p class="MsoNormal"> </p><p class="MsoNormal">Thank you,</p><p class="MsoNormal"> </p><p class="MsoNormal">Jeremiah Haywood</p><p class="MsoNormal">Lead IAM Administrator</p><p class="MsoNormal">Office of Technology Solutions | Illinois State University</p><p class="MsoNormal">Phone Number (309) 438-3829</p><p class="MsoNormal"> </p></div><br>_______________________________________________<br>midPoint mailing list<br>midPoint@lists.evolveum.com<br>https://lists.evolveum.com/mailman/listinfo/midpoint<br></div></div></body></html>