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
--
Mailing list: https://launchpad.net/~yahoo-eng-team
Post to : yahoo-eng-team@lists.launchpad.net
Unsubscribe : https://launchpad.net/~yahoo-eng-team
More help : https://help.launchpad.net/ListHelp
