The reason I opened all the problems in a single ticket was to show the disadvantages of the "UPPER" function used in the "icontains" operator.I think that instead of using "UPPER (column) like" used in the Django ORM filter method, the "icontains" operator should use the "ilike" operator and this should be changed in the source code I specified in the ticket. From the query examples I added to the ticket, you can see that the query "UPPER (column) like" generated by the "icontains" operator has a performance problem and produces incorrect results. If you use "ilike" instead of "UPPER (column)" in the icontains operator, the 2 problems I mentioned will be solved.
Tom Forbes <t...@tomforb.es>, 28 Şub 2021 Paz, 01:17 tarihinde şunu yazdı: > Hey, > I think it would be best if you opened a new ticket explaining your issue > and the performance problems you’ve found (especially around Turkish > characters). I’m not entirely clear what you wish to be changed: you wish > for an expression index to be created automatically? > > Tom > > On Sat, 27 Feb 2021 at 21:13, Mesut Öncel <mesutonce...@gmail.com> wrote: > >> Hi, >> I opened the ticket https://code.djangoproject.com/ticket/3575. In this >> ticket, I wanted to prove the effect of UPPER function used in icontains >> operator on indexes and query result. UPPER function causes performance >> problems regarding indexing in PostgreSQL. Another issue is that it does >> not give correct results especially for Turkish characters. >> >> Database administrators do not want to add individual indexes in this >> regard. Because adding more than one index to the same column in full text >> search will negatively affect cost and performance. >> >> An expression index should be created to prevent performance loss in >> database queries to be used outside the framework. However, the existence >> of such an index should be notified to everyone who uses this database, and >> the use of "UPPER (name) ilike '% AA%" instead of "ilike" should be >> encouraged. This will cause PostgreSQL to block the "ilike" operator. >> >> If you give the necessary approval in this regard, I want to open a pull >> request for the field specified on the ticket. >> >> Thanks. >> >> -- >> You received this message because you are subscribed to the Google Groups >> "Django developers (Contributions to Django itself)" group. >> To unsubscribe from this group and stop receiving emails from it, send an >> email to django-developers+unsubscr...@googlegroups.com. >> To view this discussion on the web visit >> https://groups.google.com/d/msgid/django-developers/ba167e5c-49dd-4d96-a2c4-855082639d4en%40googlegroups.com >> <https://groups.google.com/d/msgid/django-developers/ba167e5c-49dd-4d96-a2c4-855082639d4en%40googlegroups.com?utm_medium=email&utm_source=footer> >> . >> > -- > You received this message because you are subscribed to the Google Groups > "Django developers (Contributions to Django itself)" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to django-developers+unsubscr...@googlegroups.com. > To view this discussion on the web visit > https://groups.google.com/d/msgid/django-developers/CAFNZOJOr7N%3DT_7Jwj5fqv2ec3UuoVqrjCUwC_C9s_3-sckHbwQ%40mail.gmail.com > <https://groups.google.com/d/msgid/django-developers/CAFNZOJOr7N%3DT_7Jwj5fqv2ec3UuoVqrjCUwC_C9s_3-sckHbwQ%40mail.gmail.com?utm_medium=email&utm_source=footer> > . > -- İyi çalışmalar. Saygılarımla. *Mesut Öncel* -- You received this message because you are subscribed to the Google Groups "Django developers (Contributions to Django itself)" group. To unsubscribe from this group and stop receiving emails from it, send an email to django-developers+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/django-developers/CAACgSnQE4KJyKNU7hi1REQFXCVyo07QhBw6F2QUxUP5m%2BK06qQ%40mail.gmail.com.
