On Thu, 2014-08-14 at 02:39 +0300, Shai Berger wrote:
> > So, both filters must target the same join
>
> I don't see how this follows -- separate joins will each have the same set of
> related records.
True, the query results will be correct for exists lookup (but not for
most other lookups if using two joins). The performance will likely be
suboptimal.
It seems __exists filter would be an optimization only in relatively
rare cases. At least PostgreSQL and Oracle can do antijoin
optimizations, and it seems the proposed subquery plan is actually
slower than LEFT JOIN plan on MySQL[1], too.
For example on PostgreSQL both variants of the query generate the exact
same query plan:
akaariai=# explain select * from fooasdf left join barasdf on foo_id =
id where foo_id is null;
QUERY PLAN
----------------------------------------------------------------------
Hash Anti Join (cost=26.48..44.26 rows=2 width=8)
Hash Cond: (fooasdf.id = barasdf.foo_id)
-> Seq Scan on fooasdf (cost=0.00..14.01 rows=1001 width=4)
-> Hash (cost=13.99..13.99 rows=999 width=4)
-> Seq Scan on barasdf (cost=0.00..13.99 rows=999 width=4)
akaariai=# explain select * from fooasdf where not exists (select 1 from
barasdf where foo_id = id);
QUERY PLAN
----------------------------------------------------------------------
Hash Anti Join (cost=26.48..44.26 rows=2 width=4)
Hash Cond: (fooasdf.id = barasdf.foo_id)
-> Seq Scan on fooasdf (cost=0.00..14.01 rows=1001 width=4)
-> Hash (cost=13.99..13.99 rows=999 width=4)
-> Seq Scan on barasdf (cost=0.00..13.99 rows=999 width=4)
We should make related fields use the custom lookups system in any case.
If that is done, it should be relatively easy to add the two joins
exists variant in projects that need it.
- Anssi
[1]http://explainextended.com/2009/09/18/not-in-vs-not-exists-vs-left-join-is-null-mysql/
--
You received this message because you are subscribed to the Google Groups
"Django developers" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to django-developers+unsubscr...@googlegroups.com.
To post to this group, send email to django-developers@googlegroups.com.
Visit this group at http://groups.google.com/group/django-developers.
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-developers/1408000224.11410.218.camel%40TTY32.
For more options, visit https://groups.google.com/d/optout.
