Comment 2 for bug 2037107

Hi,

After ralonsoh´s hint to the following patch-series [1], I started to debug these queries and found a few things:

For me it seems like the change [2] from lazy='subquery' to 'joined' makes queries much slower because sqlalchemy always joins the model to networkrbacs with all fields in the SELECT statement.
This creates a huge amount of results which results in huge response times(see first post)
=> query: https://paste.opendev.org/show/821796/

The group by patch[4] that was introduced for project scoped queries will not help for admin/elevated queries like for example when creating a fip.
It seems like that patch is also not helping for project scoped requests. At least in my setup when executing -> query.group_by(model.id)) (like introduced in [4]), I can see the following query which makes no sense in my eyes.
=> query: https://paste.opendev.org/show/821798/
Sqlalchemy is doing a subquery for the group_by which will not group the amount of networkrbacs in the outer query. Not sure why sqlalchemy is doing that :D

[1] https://review.opendev.org/q/topic:bug%252F1918145
[2] https://review.opendev.org/c/openstack/neutron/+/884877/4/neutron/db/models_v2.py
[3] https://docs.sqlalchemy.org/en/14/orm/loading_relationships.html
[4] https://review.opendev.org/c/openstack/neutron-lib/+/884878/1/neutron_lib/db/model_query.py

Steps to reproduce the issue:
1. create a fresh devstack(with master branches)
2. create a few subnets and rbacs
=> for i in $(seq 0 15); do openstack subnet create --network public --subnet-range 10.0.$i.0/24 test$i; done
=> for i in $(seq 0 2500); do openstack network rbac create --target-project $(openstack project create test$i -c id -f value) --action access_as_shared --type network public; done
3. create a fip (--network public)
+-----------------------------------------------------------------------------------------+-----------+
| URL | Seconds |
+-----------------------------------------------------------------------------------------+-----------+
| GET http://10.1.0.70/identity | 0.019584 |
| POST http://10.1.0.70/identity/v3/auth/tokens | 0.188901 |
| GET http://10.1.0.70:9696/networking/v2.0/networks/64a06492-c675-418b-8b6d-9f5a875795e9 | 12.323395 |
| POST http://10.1.0.70:9696/networking/v2.0/floatingips | 48.035449 |
| Total | 60.567329 |
+-----------------------------------------------------------------------------------------+-----------+