yahoo-eng-team team mailing list archive

[Eugene Nikanorov <enikanorov@xxxxxxxxxxxx>]

I think it might be better to start off a thread in a mailing list to discuss this/
Plus there might be some interest to discuss the issue at the summit

** Changed in: neutron
       Status: New => Invalid

-- 
You received this bug notification because you are a member of Yahoo!
Engineering Team, which is subscribed to neutron.
https://bugs.launchpad.net/bugs/1380823

Title:
  outerjoins used as a result of plugin architecture are inefficient

Status in OpenStack Neutron (virtual network service):
  Invalid

Bug description:
  Hi there -

  I'm posting this as a bug sort of as a means to locate who best to
  talk about a. how critical these queries are and b. what other
  approaches would be feasible (I'm zzzeek on IRC).

  We're talking here about the plugin architecture in
  neutron/db/common_db_mixin.py, where the register_model_query_hook()
  method presents a way of applying modifiers to queries.    This system
  appears to be used by:  db/external_net_db.py, plugins/ml2/plugin.py,
  db/portbindings_db.py, plugins/metaplugin/meta_neutron_plugin.py.

  What the use of the hook has in common in these cases is that a LEFT
  OUTER JOIN is applied to the Query early on, in anticipation of either
  the filter_hook or result_filters being applied to the query, but only
  *possibly*, and then even within those hooks as supplied, again only
  *possibly*.   It's these two "*possiblies*" that leads to the use of
  LEFT OUTER JOIN - this extra table is present in the query's FROM
  clause, but if we decide we don't need to filter on it, its OK!  it's
  just a left outer join.  And even, in the case of external_net_db.py,
  maybe we even add a criteria "WHERE <extra model id> IS NULL", that is
  doing a "not contains" off of this left outer join.

  The result is that we can get a query like this:

      SELECT a.* FROM a LEFT OUTER JOIN b ON a.id=b.aid WHERE b.id IS
  NOT NULL

  this can happen for example if using External_net_db_mixin, the
  outerjoin to ExternalNetwork is created, _network_filter_hook applies
  "expr.or_(ExternalNetwork.network_id != expr.null())", and that's it.

  The database will usually have a much easier time if this query is
  expressed correctly:

     SELECT a.* FROM a INNER JOIN b ON a.id=b.aid

  
  the reason this bugs me is because the SQL output is being compromised as a result of how the plugin system is organized here.   Preferable would be a system where the plugins are either organized into fewer functions that perform all the checking at once, or if the plugin system had more granularity to know that it needs to apply an optional JOIN or not.   

  There's a lot of ways I could propose reorganizing this but I wanted
  to talk to someone on IRC to make sure that no external projects are
  using these hooks, and to get some other background.

  Overall long term I seek to consolidate the use of model_query into
  oslo.db, so I'm looking to take in all of its variants into a common
  form.

To manage notifications about this bug go to:
https://bugs.launchpad.net/neutron/+bug/1380823/+subscriptions