[Django] #29577: Generated SQL for exists filter does Exists(...)=true. Having just Exists(...) without "true" is faster by nearly 50% in many of my queries
Django
Exists(...) without "true" is faster by nearly 50% in many of my queries
-------------------------------------+-------------------------------------
Reporter: MChiciak | Owner: nobody
Type: | Status: new
Cleanup/optimization |
Component: Database | Version: 2.0
layer (models, ORM) |
Severity: Normal | Keywords: slow query
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
I am trying to optimize my exists filters, since on large record sets I
noticed some slowness. I have narrowed down part of it to the "= true"
portion of the "Exists(...)" subquery. Removing the "= true" gives a
noticeable performance boost
Also, it would be really nice to be able to just filter and not have a
field inside the select portion of the query. When I use the Paginator
class for pagination, it turns the count query into one big subquery and
adds all my Exists filters as select fields and thus performs a Group BY
on each of them, drastically slowing down my count query. Only having the
Exists in the where clause would be very nice. Is there a way to do this
already?
Sample django code
{{{
fits_vehicle = ProductFitment.objects.filter_vehicle(2008, 2013,
'Chevrolet', 'Corvette').filter(product=OuterRef('pk')).only("pk")
queryset =
queryset.annotate(fits_vehicle=Exists(fits_vehicle)).filter(fits_vehicle=True)
# With exists = true 500ms
SELECT COUNT(*)
FROM "aces_pies_data_product"
WHERE EXISTS(SELECT 1 FROM "aces_pies_data_productfitment" U0 INNER JOIN
"django_vehiclefitment_vehicle" U1 ON (U0."vehicle_id" = U1."id") INNER
JOIN "django_vehiclefitment_vehiclemake" U2 ON (U1."make_id" = U2."id")
INNER JOIN "django_vehiclefitment_vehiclemodel" U3 ON (U1."model_id" =
U3."id") WHERE (U2."name" = 'Chevrolet' AND U3."name" = 'Corvette' AND
(2008 BETWEEN U0."start_year" AND U0."end_year" OR 2009 BETWEEN
U0."start_year" AND U0."end_year" OR 2010 BETWEEN U0."start_year" AND
U0."end_year" OR 2011 BETWEEN U0."start_year" AND U0."end_year" OR 2012
BETWEEN U0."start_year" AND U0."end_year" OR 2013 BETWEEN U0."start_year"
AND U0."end_year") AND U0."product_id" = ("aces_pies_data_product"."id")))
= true
#With no "true" 350MS
SELECT COUNT(*)
FROM "aces_pies_data_product"
WHERE EXISTS(SELECT 1 FROM "aces_pies_data_productfitment" U0 INNER JOIN
"django_vehiclefitment_vehicle" U1 ON (U0."vehicle_id" = U1."id") INNER
JOIN "django_vehiclefitment_vehiclemake" U2 ON (U1."make_id" = U2."id")
INNER JOIN "django_vehiclefitment_vehiclemodel" U3 ON (U1."model_id" =
U3."id") WHERE (U2."name" = 'Chevrolet' AND U3."name" = 'Corvette' AND
(2008 BETWEEN U0."start_year" AND U0."end_year" OR 2009 BETWEEN
U0."start_year" AND U0."end_year" OR 2010 BETWEEN U0."start_year" AND
U0."end_year" OR 2011 BETWEEN U0."start_year" AND U0."end_year" OR 2012
BETWEEN U0."start_year" AND U0."end_year" OR 2013 BETWEEN U0."start_year"
AND U0."end_year") AND U0."product_id" = ("aces_pies_data_product"."id")))
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/29577>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
Exists(...) without "true" is faster by nearly 50% in many of my queries
-------------------------------------+-------------------------------------
Type: | Status: new
Cleanup/optimization |
Component: Database layer | Version: 2.0
(models, ORM) |
Severity: Normal | Resolution:
Keywords: slow query | Triage Stage:
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Michael Chiciak):
Did some playing around with the "only having columns as filters". If you
use "values", it does take away the annotation which is what I want. But
it would be nice if defer behaved the same way so I can still retain all
the model fields naturally.
--
Ticket URL: <https://code.djangoproject.com/ticket/29577#comment:1>
Django
just Exists(...) without "true" is faster by nearly 50% in many of my
queries
-------------------------------------+-------------------------------------
Type: | Status: new
Cleanup/optimization |
Component: Database layer | Version: 2.0
(models, ORM) |
Severity: Normal | Resolution:
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
--
Ticket URL: <https://code.djangoproject.com/ticket/29577#comment:2>
Django
just Exists(...) without "true" is faster by nearly 50% in many of my
queries
-------------------------------------+-------------------------------------
Type: | Status: new
Cleanup/optimization |
Component: Database layer | Version: 2.0
(models, ORM) |
Severity: Normal | Resolution:
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Old description:
New description:
Sample django code
}}}
**Edit**
Poked around some of the annotation source code and got a work around
To remove the annotations from my selects and thus drastically increase
the count(*) query speed, I just did
products.query.annotation_select.pop('fits_vehicle'). This correctly
filters without actually selecting, big improvement in speed!
--
--
Ticket URL: <https://code.djangoproject.com/ticket/29577#comment:3>
Django
just Exists(...) without "true" is faster by nearly 50% in many of my
queries
-------------------------------------+-------------------------------------
Cleanup/optimization |
Component: Database layer | Version: 2.0
(models, ORM) |
Keywords: slow query | Triage Stage:
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* status: new => closed
* resolution: => duplicate
Comment:
Duplicate of #25367 which would allow the `Exist()` expression to be
directly passed to `filter` instead of having to annotate it and filter
against.
--
Ticket URL: <https://code.djangoproject.com/ticket/29577#comment:4>
Django
just Exists(...) without "true" is faster by nearly 50% in many of my
queries
-------------------------------------+-------------------------------------
Type: | Status: closed
Cleanup/optimization |
Component: Database layer | Version: 2.0
(models, ORM) |
Severity: Normal | Resolution: duplicate
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Simon Charette):
Here's a discussion about not including the `=true` part on backends that
support it that might interest you.
https://github.com/django/django/pull/8119#issuecomment-373844543
--
Ticket URL: <https://code.djangoproject.com/ticket/29577#comment:5>