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

[Mesut Öncel]

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


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

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

[Tom Forbes]

Unless I’m missing something, the ticket you linked was last updated 7
years ago with two now-broken images. Which ticket did you open with the
examples?

Tom

On Sun, 28 Feb 2021 at 08:31, Mesut Öncel  wrote:

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

-- 
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/CAFNZOJOBzXOTSsE-i1cudhGgZ84_tV30%2B40kp%3D0JQnw7xwDnmA%40mail.gmail.com.

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

[Mesut Öncel]

Yes, 7 years ago I reviewed the ticket for the updated code, but there are
problems there.https: //code.djangoproject.com/ticket/32485 The ticket I
opened has the necessary details. If you want more details, I can make the
necessary explanations.

Tom Forbes , 28 Şub 2021 Paz, 12:36 tarihinde şunu yazdı:

> Unless I’m missing something, the ticket you linked was last updated 7
> years ago with two now-broken images. Which ticket did you open with the
> examples?
>
> Tom
>
> On Sun, 28 Feb 2021 at 08:31, Mesut Öncel  wrote:
>
>> 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 , 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 
>>> 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
>>> 
>>> .
>>>
>>
>>
>> --
>> İ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
>> 
>> .
>>
> --
> 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 djang

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

[Tom Forbes]

Thank you for the clarification!

On Sun, 28 Feb 2021 at 09:23, Mesut Öncel  wrote:

> Yes, 7 years ago I reviewed the ticket for the updated code, but there are
> problems there.https: //code.djangoproject.com/ticket/32485 The ticket I
> opened has the necessary details. If you want more details, I can make the
> necessary explanations.
>
> Tom Forbes , 28 Şub 2021 Paz, 12:36 tarihinde şunu yazdı:
>
>> Unless I’m missing something, the ticket you linked was last updated 7
>> years ago with two now-broken images. Which ticket did you open with the
>> examples?
>>
>> Tom
>>
>> On Sun, 28 Feb 2021 at 08:31, Mesut Öncel  wrote:
>>
>>> 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 , 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 
 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
 
 .

>>>
>>>
>>> --
>>> İ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
>>> 
>>> .
>>>
>> --
>> You received this message 

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

[Tom Forbes]

Thank you for the clarification! I think the biggest argument for this
change is the fact that uppercasing Unicode can cause incorrect results to
be returned.

Given that we now have much better support for custom index types, perhaps
we should change this? We need a custom expression index anyway, so it
might not be a huge ask to say “now you should use a gin index”?

Tom

On Sun, 28 Feb 2021 at 09:28, Tom Forbes  wrote:

> Thank you for the clarification!
>
> On Sun, 28 Feb 2021 at 09:23, Mesut Öncel  wrote:
>
>> Yes, 7 years ago I reviewed the ticket for the updated code, but there
>> are problems there.https: //code.djangoproject.com/ticket/32485 The
>> ticket I opened has the necessary details. If you want more details, I can
>> make the necessary explanations.
>>
>> Tom Forbes , 28 Şub 2021 Paz, 12:36 tarihinde şunu yazdı:
>>
>>> Unless I’m missing something, the ticket you linked was last updated 7
>>> years ago with two now-broken images. Which ticket did you open with the
>>> examples?
>>>
>>> Tom
>>>
>>> On Sun, 28 Feb 2021 at 08:31, Mesut Öncel 
>>> wrote:
>>>
 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 , 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 
> 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
> 
> .
>


 --
 İyi çalışmalar. Saygılarımla.

 *Mesut Öncel*

 --
 You received this message because you are subscribed to the Google
 Groups "Django developers (Contributions to Djan

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

[Mesut Öncel]

You're welcome. Yes, database administrators can create an index as
desired. However, creating multiple cin indexes in a single column with
different methods will cause performance issues. I don't think using
expressions when using like and ilike is a correct use for query
performance.
It doesn't hurt to add an extra expression string for the "exact" and
"iexact" operators. However, it will be a huge cost for full text search.

Tom Forbes , 28 Şub 2021 Paz, 13:39 tarihinde şunu yazdı:

> Thank you for the clarification! I think the biggest argument for this
> change is the fact that uppercasing Unicode can cause incorrect results to
> be returned.
>
> Given that we now have much better support for custom index types, perhaps
> we should change this? We need a custom expression index anyway, so it
> might not be a huge ask to say “now you should use a gin index”?
>
> Tom
>
> On Sun, 28 Feb 2021 at 09:28, Tom Forbes  wrote:
>
>> Thank you for the clarification!
>>
>> On Sun, 28 Feb 2021 at 09:23, Mesut Öncel  wrote:
>>
>>> Yes, 7 years ago I reviewed the ticket for the updated code, but there
>>> are problems there.https: //code.djangoproject.com/ticket/32485 The
>>> ticket I opened has the necessary details. If you want more details, I can
>>> make the necessary explanations.
>>>
>>> Tom Forbes , 28 Şub 2021 Paz, 12:36 tarihinde şunu
>>> yazdı:
>>>
 Unless I’m missing something, the ticket you linked was last updated 7
 years ago with two now-broken images. Which ticket did you open with the
 examples?

 Tom

 On Sun, 28 Feb 2021 at 08:31, Mesut Öncel 
 wrote:

> 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 , 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 
>> 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 d

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

[Mesut Öncel]

A discussion was requested from here to make changes on this issue. Do you
think it would be appropriate if I open a pull request for this change?

Mesut Öncel , 28 Şub 2021 Paz, 14:06 tarihinde şunu
yazdı:

> You're welcome. Yes, database administrators can create an index as
> desired. However, creating multiple cin indexes in a single column with
> different methods will cause performance issues. I don't think using
> expressions when using like and ilike is a correct use for query
> performance.
> It doesn't hurt to add an extra expression string for the "exact" and
> "iexact" operators. However, it will be a huge cost for full text search.
>
> Tom Forbes , 28 Şub 2021 Paz, 13:39 tarihinde şunu yazdı:
>
>> Thank you for the clarification! I think the biggest argument for this
>> change is the fact that uppercasing Unicode can cause incorrect results to
>> be returned.
>>
>> Given that we now have much better support for custom index types,
>> perhaps we should change this? We need a custom expression index anyway, so
>> it might not be a huge ask to say “now you should use a gin index”?
>>
>> Tom
>>
>> On Sun, 28 Feb 2021 at 09:28, Tom Forbes  wrote:
>>
>>> Thank you for the clarification!
>>>
>>> On Sun, 28 Feb 2021 at 09:23, Mesut Öncel 
>>> wrote:
>>>
 Yes, 7 years ago I reviewed the ticket for the updated code, but there
 are problems there.https: //code.djangoproject.com/ticket/32485 The
 ticket I opened has the necessary details. If you want more details, I can
 make the necessary explanations.

 Tom Forbes , 28 Şub 2021 Paz, 12:36 tarihinde şunu
 yazdı:

> Unless I’m missing something, the ticket you linked was last updated 7
> years ago with two now-broken images. Which ticket did you open with the
> examples?
>
> Tom
>
> On Sun, 28 Feb 2021 at 08:31, Mesut Öncel 
> wrote:
>
>> 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 , 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 
>>> 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
 

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

[Sky Lord]

Guys, am noob anyone gonna guide?

On Sun, Feb 28, 2021, 11:39 AM Tom Forbes  wrote:

> Thank you for the clarification! I think the biggest argument for this
> change is the fact that uppercasing Unicode can cause incorrect results to
> be returned.
>
> Given that we now have much better support for custom index types, perhaps
> we should change this? We need a custom expression index anyway, so it
> might not be a huge ask to say “now you should use a gin index”?
>
> Tom
>
> On Sun, 28 Feb 2021 at 09:28, Tom Forbes  wrote:
>
>> Thank you for the clarification!
>>
>> On Sun, 28 Feb 2021 at 09:23, Mesut Öncel  wrote:
>>
>>> Yes, 7 years ago I reviewed the ticket for the updated code, but there
>>> are problems there.https: //code.djangoproject.com/ticket/32485 The
>>> ticket I opened has the necessary details. If you want more details, I can
>>> make the necessary explanations.
>>>
>>> Tom Forbes , 28 Şub 2021 Paz, 12:36 tarihinde şunu
>>> yazdı:
>>>
 Unless I’m missing something, the ticket you linked was last updated 7
 years ago with two now-broken images. Which ticket did you open with the
 examples?

 Tom

 On Sun, 28 Feb 2021 at 08:31, Mesut Öncel 
 wrote:

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

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

[Mesut Öncel]

The ticket I posted in the first email does not belong to me. The ticket I
opened is https://code.djangoproject.com/ticket/32485.

Sky Lord , 28 Şub 2021 Paz, 14:59 tarihinde şunu
yazdı:

> Guys, am noob anyone gonna guide?
>
> On Sun, Feb 28, 2021, 11:39 AM Tom Forbes  wrote:
>
>> Thank you for the clarification! I think the biggest argument for this
>> change is the fact that uppercasing Unicode can cause incorrect results to
>> be returned.
>>
>> Given that we now have much better support for custom index types,
>> perhaps we should change this? We need a custom expression index anyway, so
>> it might not be a huge ask to say “now you should use a gin index”?
>>
>> Tom
>>
>> On Sun, 28 Feb 2021 at 09:28, Tom Forbes  wrote:
>>
>>> Thank you for the clarification!
>>>
>>> On Sun, 28 Feb 2021 at 09:23, Mesut Öncel 
>>> wrote:
>>>
 Yes, 7 years ago I reviewed the ticket for the updated code, but there
 are problems there.https: //code.djangoproject.com/ticket/32485 The
 ticket I opened has the necessary details. If you want more details, I can
 make the necessary explanations.

 Tom Forbes , 28 Şub 2021 Paz, 12:36 tarihinde şunu
 yazdı:

> Unless I’m missing something, the ticket you linked was last updated 7
> years ago with two now-broken images. Which ticket did you open with the
> examples?
>
> Tom
>
> On Sun, 28 Feb 2021 at 08:31, Mesut Öncel 
> wrote:
>
>> 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 , 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 
>>> 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 vi

Fellow Reports -- February 2021

[Carlton Gibson]

Hi all. 


Calendar Week 5 -- ending 07 February.


Triaged:

https://code.djangoproject.com/ticket/32411 -- JSONField filter icontains 
is case sensitive in mysql (Invalid)
https://code.djangoproject.com/ticket/32409 -- TestCase async tests are not 
transaction-aware (Accepted)
https://code.djangoproject.com/ticket/32408 -- Missing documentation for 
django.views.generic.detail.BaseDetailView (Accepted)
https://code.djangoproject.com/ticket/32396 -- UsernameField normalisation 
throws TypeError if the username is empty (duplicate of #31972)
https://code.djangoproject.com/ticket/32406 -- Allow QuerySet.update() to 
return fields on supported backends. (Accepted)
https://code.djangoproject.com/ticket/32397 -- django-admin 
start[project|app] doesn't send proper user agent header when HTTP(S) 
url is sent. (Accepted)



Reviewed:

https://github.com/django/django/pull/13969 -- Skipped test_archive tests 
when bz2/lzma module is not installed.
https://github.com/django/django/pull/13841 -- Fixed #32316 -- Deferred 
accessing __file__.
https://github.com/django/django/pull/13970 -- Refs #32074 -- Fixed 
TextChoices/IntegerChoices crash on Python 3.10.
https://github.com/django/django/pull/13748 -- Fixed #31527 -- Made 
ViewIndexView in admindocs work with non-string ROOT_URLCONF
https://github.com/django/django/pull/13950 -- Fixed #32390 -- Dropped 
support for Oracle 12.2 and 18c.
https://github.com/django/django/pull/13716 -- Contributing docs



Authored:

https://github.com/django/django/pull/13965 -- Refs #32409 -- Doc'd ORM 
calling limitations on async tests.





Calendar Week 6 -- ending 14 February.


Applied for GSoC 2021


Triaged:

https://code.djangoproject.com/ticket/25022 -- collectstatic create 
self-referential symlink (needsinfo)
https://code.djangoproject.com/ticket/32428 -- [Feature Request] Allow 
DeleteView to override the redirect to use 303 (wontfix)
https://code.djangoproject.com/ticket/32431 -- Consider listing the history 
of security issues in reverse chronological order (Accepted)



Reviewed:

https://github.com/django/django/pull/13728 -- Add settings 
EMAIL_MESSAGEID_FQDN
https://github.com/django/django/pull/13958 -- Documented an example custom 
context processor
https://github.com/django/django/pull/13991 -- Improved pagination 
documentation
https://github.com/django/django/pull/13436 -- Fixed #30231 -- Fixed admin 
filter horizontal/vertical verbose_name generation.
https://github.com/django/django/pull/13997 -- Refs #31670 -- Used 
allowlist_externals in tox.ini (again)
https://github.com/django/django/pull/13993 -- Fixed #32431 -- Reversed 
order of security issues history.
https://github.com/django/django/pull/13994 -- Enabled build-system 
isolation via pip.
https://github.com/django/deps/pull/69 -- DEP 11 -- Create an accessibility 
team.
https://github.com/django/django/pull/13915 -- Fixed #32355 -- Dropped 
support for Python 3.6 and 3.7
https://github.com/django/django/pull/13722 -- Fixed #26607 -- Added 
ModelAdmin.get_formset_params() hook
https://github.com/django/django/pull/13988 -- Used .. attribute:: 
directive in authentication views docs.



Authored:

https://github.com/django/django/pull/13996 -- Refs #32355 -- Restored PY36 
and PY37 version constants.





Calendar Week 7 -- ending 21 February.


Released versions 3.1.7, 3.0.13, 2.2.19, and 3.2beta1. 



Triaged:

https://code.djangoproject.com/ticket/32439 -- Dumpdata fails on Windows 
due to non-utf8 system locale (Duplicate of #26721)



Reviewed:

https://github.com/django/django/pull/14015 -- Added documentaion extlink 
for bugs.python.org.
https://github.com/django/django/pull/13981` -- Contributing guide docs




Calendar Week 8 -- ending 28 February.


Holiday 🏝 [1]


[1] Island may not be representative of actual week. 


Kind Regards,

Carlton



-- 
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/5f50f081-4372-4542-aefe-2a541093d76an%40googlegroups.com.

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

[James Bennett]

On Sun, Feb 28, 2021 at 2:39 AM Tom Forbes  wrote:
>
> Thank you for the clarification! I think the biggest argument for this change 
> is the fact that uppercasing Unicode can cause incorrect results to be 
> returned.
>
> Given that we now have much better support for custom index types, perhaps we 
> should change this? We need a custom expression index anyway, so it might not 
> be a huge ask to say “now you should use a gin index”?

It's worth pointing out that case mapping and transformation in
Unicode is difficult and complex. I wrote up an intro to the problem a
while back:

https://www.b-list.org/weblog/2018/nov/26/case/

One thing that's important to note is that there is no generic
one-size-fits-all-languages option that Django can just do by default
and get the right results. For example, a case mapping that does the
right thing for Turkish will do the wrong thing for (to pick a random
example) French, and vice-versa. Unicode itself provides a basic "hope
for the best" set of default case mappings that do the right thing for
many cased scripts, but also is clear in saying that you may need to
use a locale-specific mapping to get what you really want.

Postgres has the ability to configure locale, and when configured it
does the "right thing" -- for example, when the locale is tr_TR or
another Turkish locale variant, the UPPER() function should correctly
handle dotted versus dotless 'i' as required for Turkish. But Postgres
also warns that this will have performance impact, which I think is
what's being noted in the ticket.

I'm not sure there will be an easy or obvious solution here.

-- 
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/CAL13Cg9nYMJZwm2XcsCcWG5Fqn8gqqE93FM11Xcfs4TXsmTbZQ%40mail.gmail.com.

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

[Mesut Öncel]

Hi James,
Thanks for your explanations. However, I wanted to explain the disadvantage
of using "UPPER like"  instead of "ilike" in icontains, istartswith and
iendswith. The performance problem should not be ignored.

James Bennett , 1 Mar 2021 Pzt, 04:05 tarihinde şunu
yazdı:

> On Sun, Feb 28, 2021 at 2:39 AM Tom Forbes  wrote:
> >
> > Thank you for the clarification! I think the biggest argument for this
> change is the fact that uppercasing Unicode can cause incorrect results to
> be returned.
> >
> > Given that we now have much better support for custom index types,
> perhaps we should change this? We need a custom expression index anyway, so
> it might not be a huge ask to say “now you should use a gin index”?
>
> It's worth pointing out that case mapping and transformation in
> Unicode is difficult and complex. I wrote up an intro to the problem a
> while back:
>
> https://www.b-list.org/weblog/2018/nov/26/case/
>
> One thing that's important to note is that there is no generic
> one-size-fits-all-languages option that Django can just do by default
> and get the right results. For example, a case mapping that does the
> right thing for Turkish will do the wrong thing for (to pick a random
> example) French, and vice-versa. Unicode itself provides a basic "hope
> for the best" set of default case mappings that do the right thing for
> many cased scripts, but also is clear in saying that you may need to
> use a locale-specific mapping to get what you really want.
>
> Postgres has the ability to configure locale, and when configured it
> does the "right thing" -- for example, when the locale is tr_TR or
> another Turkish locale variant, the UPPER() function should correctly
> handle dotted versus dotless 'i' as required for Turkish. But Postgres
> also warns that this will have performance impact, which I think is
> what's being noted in the ticket.
>
> I'm not sure there will be an easy or obvious solution here.
>
> --
> 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/CAL13Cg9nYMJZwm2XcsCcWG5Fqn8gqqE93FM11Xcfs4TXsmTbZQ%40mail.gmail.com
> .
>


-- 
İ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/CAACgSnRGvQ8qnXNFqDZYpmxtP%2BHU4b6OhDAFh%2B2dSdRD0gVUPw%40mail.gmail.com.