>> ... INNER JOIN tblBig b ON >> (b.other_id = other.id AND >> b.foo = 42 AND >> b.bar = 'zip' >> ) >> >> the cartesian product makes the join huge. I've performance >> tested the resulting queries at the psql prompt, and the above >> version runs in under 2 seconds. Doing the logical equiv of >> >> >> ..., tblBig b >> WHERE >> b.other_id = other.id AND >> b.foo = 42 AND >> b.bar = 'zip' >> >> takes upwards of 10-15 minutes. My figuring is that it's doing a >> full cartesian product of the two tables (producing billions of >> rows) and then filtering that result. > > Something is odd here. Any respectable database server knows that "FROM > tableA, tableB where tableA.id = tableB.id" is the same as "FROM tableA > INNER JOIN tableB ON (id)".
That was my understanding as well. But "should" and "experience" are often not even on the same planet. :) However, as you suggest, I've poured over EXPLAIN statements time and again and it looks like it's doing a hash-scan of too much of my tblBig when it does its thing. >> I'm currently mucking under the hood with a custom object that >> toys with the query, but am fighting with it to a degree that >> someone else may have already done this or would have hints on >> best practices. > > Trying to tweak current QuerySet objects like this is a very hard at the > moment -- as you've no doubt discovered -- because internally they just > push string fragments around. I am in the middle of rewriting QuerySet > to make exactly this type of Query manipulation easier. Yes, it's a non-trivial task at the moment, but it looks like I may be able to make some modified version of something like a Q() object that tweaks the "joins" as returned. Thanks for your confirmation that I'm not crazy and it *should* be doing what I expect. It seems to be more of a postgresql issue now, but that I can work around with some judicious use of a custom object. -tkc --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Django users" group. To post to this group, send email to django-users@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/django-users?hl=en -~----------~----~----~----~------~----~------~--~---
