yahoo-eng-team team mailing list archive

[Mike Bayer <mike_mp@xxxxxxxxxxxxxxxx>]

Public bug reported:

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.

** Affects: neutron
     Importance: Undecided
         Status: New

-- 
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):
  New

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