Excess symbol % within LIKE

Question

We have a GX system EV3 U2 generating Java and connecting in Postgresql 9.3 database. Overall the performance is acceptable, but at certain times we have bottlenecks and I realized that happen just when some query with LIKE is executed.

Looking deeper, I found that the problem is when GX generates code for instruction with LIKE, it fills the field size with % characters and then Postgres ends up generating a bad execution plan for the query. If you take (manually) the excess %, the consultation is quick.

Ex: in an address search field; "varchar(120) address", the code generated for a search turns something like

select endereco from cliente where endereco like 'rua do brasil%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%'

Such a query runs in 1.35 seconds, but removing the excess %, leaving only 1, the same query runs in 0.33 sec.

How can I solve this problem? What can/should be done to circumvent or resolve it for good? It would be a GX bug or the way it is being programmed is that it should be improved?

Answer 1

When one has a LIKE one is completing the string con '%' at the end because in previous versions of GX it was always required to complete the strings with whites, and to prevent the condition being invalid, in this case it is completed with '%'. This is no longer required in the new versions of Genexus.

If the matter was reported in SAC #31034, until now no problem had been reported with this behavior. SAC was activated again to correct it in the next upgrades.