As I’ve established oslo.db blueprints which will roll out new SQLAlchemy 
connectivity patterns for consuming applications within both API [1] and tests 
[2], one of the next big areas I’m to focus on is that of querying.   If one 
looks at how SQLAlchemy ORM queries are composed across Openstack, the most 
prominent feature one finds is the prevalent use of the model_query() 
initiation function.    This is a function that is implemented in a specific 
way for each consuming application; its purpose is to act as a factory for new 
Query objects, starting from the point of acquiring a Session, starting up the 
Query against a selected model, and then augmenting that Query right off with 
criteria derived from the given application context, typically oriented around 
the widespread use of so-called “soft-delete” columns, as well as a few other 
fixed criteria.

There’s a few issues with model_query() that I will be looking to solve, 
starting with the proposal of a new blueprint.   Key issues include that it 
will need some changes to interact with my new connectivity specification, it 
may need a big change in how it is invoked in order to work with some new 
querying features I also plan on proposing at some point (see, and 
also it’s current form in some cases tends to slightly discourage the 
construction of appropriate queries.

In order to propose a new system for model_query(), I have to do a survey of 
how this function is implemented and used across projects.  Which is why we 
find me talking about Neutron today - Neutron’s model_query() system is a much 
more significant construct compared to that of all other projects.   It is 
interesting because it makes clear some use cases that SQLAlchemy may very well 
be able to help with.  It also seems to me that in its current form it leads to 
SQL queries that are poorly formed - as I see this, on one hand we can blame 
the structure of neutron’s model_query() for how this occurs, but on the other, 
we can blame SQLAlchemy for not providing more tools oriented towards what 
Neutron is trying to do.   The use case Neutron has here is very common 
throughout many Python applications, but as yet I’ve not had the opportunity to 
address this kind of pattern in a comprehensive way.   

I first sketched out my concerns on a Neutron issue, however I was encouraged to 
move it over to the mailing list.

Specifically with Neutron’s model_query(), we're talking here about the plugin 
architecture in neutron/db/, where the 
register_model_query_hook() method presents a way of applying modifiers to 
queries. This system appears to be used by: db/, 
plugins/ml2/, db/, 

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, the idea is that it's just a left outer join, which will not change the 
primary result if not added to what’s being filtered. And even, in the case of, 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:


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 [3]:


the reason this bugs me is because the SQL output is being compromised as a 
result of how the plugin system is organized. 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.   My thoughts for new 
SQLAlchemy/oslo.db features are being driven largely by Neutron’s use case here.

Towards my goal of proposing a better system of model_query(), along with 
Neutron’s heavy use of generically added criteria, I’ve put some thoughts down 
on a new SQLAlchemy feature which would also be backported to oslo.db. The 
initial sketch is at,
 and the main idea is that Query would include a system by which we can ask 
questions about what it selects from and what it joins to and get definitive 
answers, with this use case in mind.    It would allow functions like “we need 
to INNER JOIN to X but only if our query is already asking for something about 
X” to be easy to write, and would allow Neutron’s system (as well as others) to 
be simplified and generate better queries.  A new event hook would also be 
supplied that allows for a clean place that these additional criteria can be 
augmented on a Query, without the need for the Query to be “created” from a 
factory such as model_query(), e.g. the feature would integrate transparently 
(somehow) with the system I’m proposing separately in [1], allowing the Query() 
API to be used fully without encouraging queries that are only against “a 
single model class".

So as is customary before I start proposing changes to consuming projects, I’m 
putting this out here to get any initial comments on this; such as, if someone 
could give me some background on Neutron’s model_query system, if it is used by 
any plugins that aren’t in the neutron source tree I have to know about, and if 
there is agreement that we’d be better off trying to use INNER JOIN and EXISTS 
appropriately, rather than “cheating” with an OUTER JOIN the way it is now.   
It may be the case that the queries right now are against small datasets or are 
otherwise taking advantage of MySQL’s planner so that they work out fine 
anyway, however in [3] I illustrate how relying on OUTER JOINs generically can 
often lead to poorer performance; if it isn’t a problem right now in Neutron, 
the pattern that’s used here is still less than ideal as it does not allow full 
control of how the SQL is rendered compared to what is needed, and I’d like to 
propose systems that are both more compatible with future needs (such as baked 
query) and which also can do a better job.

[1] make EngineFacade a Facade:    

[2] add long-lived transactionalized DB fixtures:

[3] some background on how INNER vs OUTER join can be a big deal in many cases 
(more often than not).   I tested with a schema containing two tables: “a" and 
“b”.  “a" has 10000 rows and “b" has 1M.  The “b" rows are linked via indexed 
foreign key to a subset of rows in “a".  Comparing the two queries, the OUTER 
JOIN query fails to use the index on “b.aid”:

 | id | select_type | table | type | possible_keys | key | key_len | ref | rows 
| Extra |
 | 1 | SIMPLE | b | index | b_aid | b_aid | 5 | NULL | 999000 | Using where; 
Using index |
 | 1 | SIMPLE | a | eq_ref | PRIMARY | PRIMARY | 4 | test.b.aid | 1 | NULL |
 2 rows in set (0.00 sec)

 | id | select_type | table | type | possible_keys | key | key_len | ref | rows 
| Extra |
 | 1 | SIMPLE | b | ALL | PRIMARY,b_aid | NULL | NULL | NULL | 999000 | Using 
where |
 | 1 | SIMPLE | a | eq_ref | PRIMARY | PRIMARY | 4 | test.b.aid | 1 | NULL |
 2 rows in set (0.00 sec)

Postgresql may have different results here, but in general OUTER JOINs are a 
common source of poor performance, and I think we should have ORM patterns in 
place where there is no tendency for SQL to be pushed in one direction or 
another based on code organization.

OpenStack-dev mailing list

Reply via email to