#29577: Generated SQL for exists filter does Exists(...)=true. Having just
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.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/051.0b2c18299f77b6314cf0a0a72bd9286a%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
