Re: Complex query reduction

On Fri, Nov 1, 2013 at 12:45 PM, Daniele Procida <daniele@vurt.org> wrote:
> In practice I use some tweaks (such as values_list) to speed this up, and caching, but the fundamental pattern is the same. It's slow, because there are 30 thousand BibliographicRecords.


the total number of records shouldn't matter. more important is the
number of selected records at some points in the query. i'd guess the
number of chosen entities (those that _are_ descendant of the first
one), and the number of chosen researches (those that _are_ members of
those entities) should be the most significant quantities.

the point is that most DB optimizers are somewhat shy to shuffle
conditions around IN operators. but it might be easier to work on a
small (a few tens of values?) IN condition than on a very long JOIN
chain.

>
> I'm trying to rearrange the construction of the query in different ways to find a speed improvement. I don't think that either prefetch_related or select_related will help here, but perhaps there are some other tools that would.


have you tried eliminating the second IN relationship? something like

entities = entity.get_descendants()

items = BibliographicRecord.objects.filter(authored__researcher__person__member_of__entity__in=entities).distinct()

that should turn most of the query in a long sequence of "INNER JOIN",
giving the optimizer an easier job, avoiding the IN operation on
research records. if the query chooses too many researches, this
could be a big part of the slowness.

--
Javier

--
You received this message because you are subscribed to the Google Groups "Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to django-users+unsubscribe@googlegroups.com.
To post to this group, send email to django-users@googlegroups.com.
Visit this group at http://groups.google.com/group/django-users.
To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/CAFkDaoSVg3K44wRUnOTrinqXZbVKm1ZZLHxFPB8LdgrzBtxViA%40mail.gmail.com.
For more options, visit https://groups.google.com/groups/opt_out.