About the ORM icontains operator's disadvantage on PostgreSQL performance and query results.

[Mesut Öncel]

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.

Re: About the ORM icontains operator's disadvantage on PostgreSQL performance and query results.

[Tom Forbes]

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  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
> 
> .
>

-- 
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.