#29577: Generated SQL in PostGres for exists filter does Exists(...)=true.
Having
just Exists(...) without "true" is faster by nearly 50% in many of my
queries
-------------------------------------+-------------------------------------
Reporter: Michael Chiciak | Owner: nobody
Type: | Status: new
Cleanup/optimization |
Component: Database layer | Version: 2.0
(models, ORM) |
Severity: Normal | Resolution:
Keywords: slow query | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Description changed by Michael Chiciak:
Old description:
> 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")))
>
> }}}
New description:
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")))
}}}
**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 <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/066.49c9bbaa5b49eef4320e4d4ce58b0021%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
