[Django] #34811: Queryset filter Q order changed

[Django]

#34811: Queryset filter Q order changed
-------------------------------------+-------------------------------------
Reporter: | Owner: nobody
cosmoscalibur |
Type: Bug | Status: new
Component: Database | Version: 4.2
layer (models, ORM) |
Severity: Normal | Keywords: filter, Q
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
Using filters in order explicitly expressed by user is important because
take advantage of indexes.

When apply this query

{{{
TaxInput.objects.all(
force_visibility=True
).filter(
Q(filling_id=pk, updated_at__gt=last_engine.created_at)
| Q(filling_id=pk, deleted__gt=last_engine.created_at),
)
}}}

the MySQL generated query is

{{{
SELECT
*
FROM
`taxobjects_taxinput`
WHERE
( `taxobjects_taxinput` . `filling_id` = ?
AND `taxobjects_taxinput` . `updated_at` > ? )
OR ( `taxobjects_taxinput` . `deleted` > ?
AND `taxobjects_taxinput` . `filling_id` = ? )
LIMIT
?
}}}

But should be

{{{
SELECT
*
FROM
`taxobjects_taxinput`
WHERE
( `taxobjects_taxinput` . `filling_id` = ?
AND `taxobjects_taxinput` . `updated_at` > ? )
OR ( `taxobjects_taxinput` . `filling_id` > ?
AND `taxobjects_taxinput` . `deleted` = ? )
LIMIT
?
}}}

This change of order create a 10x factor in query time, because this table
has index filing_id, and also index (filing_id, deleted).

Using Django 4.2.1 and SafeDelete 1.3.1 (because this is used
force_visibility=True in all manager).

--
Ticket URL: <https://code.djangoproject.com/ticket/34811>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

[Django]

#34811: Queryset filter Q order changed
-------------------------------------+-------------------------------------

Reporter: Edward Villegas- | Owner: nobody
Pulgarin |
Type: Bug | Status: closed
Component: Database layer | Version: 4.2
(models, ORM) | Resolution:
Severity: Normal | worksforme

Keywords: filter, Q | Triage Stage:

| Unreviewed
Has patch: 0 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by David Sanders):

* status: new => closed
* resolution: => worksforme

Comment:

Hi,

Thanks for the report though I don't see an issue with Django here. There
are many reasons why a database will choose to ignore indexes but sadly
parameter ordering is not one of those. (To test this I recreated your
setup and MySQL reported that the indexes are a possibility both ways.)

I'd encourage you to seek help from one of the Django support channels
where there are many friendly people who are eager to help:
https://www.djangoproject.com/community/

You'll likely need to paste detailed descriptions of your models, query,
setup & any query plans (you may even be asked to paste memory settings
etc as this will have an impact on query planning).

If it is discovered there is an issue with Django feel free to reopen this
with links to discussion & findings.

Good luck!

--
Ticket URL: <https://code.djangoproject.com/ticket/34811#comment:1>

[Django]

#34811: Queryset filter Q order changed
-------------------------------------+-------------------------------------
Reporter: Edward Villegas- | Owner: nobody
Pulgarin |
Type: Bug | Status: closed
Component: Database layer | Version: 4.2
(models, ORM) | Resolution:
Severity: Normal | worksforme
Keywords: filter, Q | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Simon Charette):

If that can be of any help, we've observed that MySQL's query planer is
can be pretty bad at using composite indexes when `OR` conditions are
involved and had to resort to using `UNION` to target them.

Re-writing your lookup to move the `filling_id=pk` outside of the `OR`
should help MySQL at figuring out that the `filing_id` index can be used
at least.

{{{#!python
TaxInput.objects.filter(
filling_id=pk,
Q(updated_at__gt=last_engine.created_at) |
Q(deleted__gt=last_engine.created_at)
)
}}}

{{{#!sql

SELECT
*
FROM
`taxobjects_taxinput`
WHERE

`taxobjects_taxinput` . `filling_id` = ?

AND (
`taxobjects_taxinput` . `updated_at` > ?
OR `taxobjects_taxinput` . `deleted` > ?
)
LIMIT
?
}}}

--
Ticket URL: <https://code.djangoproject.com/ticket/34811#comment:2>