+++ This bug was initially created as a clone of Bug #1661575 +++

Description of problem:
Octavia seems to grow in utilization after each performance iteration[1].

Our Rally workload will run and clean up, leaving no let-over load balancer or resources[2].

[1] http://norton.perf.lab.eng.rdu.redhat.com:3000/dashboard/snapshot/4mMx5Er4mQGE5pQOdwJh6Kth6jkq8YLk?orgId=1
[2] https://gist.github.com/jtaleric/64da254aa6463ef39da3090a67742119

Version-Release number of selected component (if applicable):
puppet-octavia-13.3.1-0.20181013113434.e19b590.el7ost.noarch
python-octavia-3.0.1-0.20181009115731.c57ae8d.el7ost.noarch
openstack-octavia-api-3.0.1-0.20181009115731.c57ae8d.el7ost.noarch
openstack-octavia-common-3.0.1-0.20181009115731.c57ae8d.el7ost.noarch


How reproducible:
100%

Steps to Reproduce:
1. Deploy Overcloud w/ Octavia
2. Run Browbeat Rally Octavia workload
3. Monitor load

Additional info:
Restarting the containers does not help reduce the load. They return to the same utilization

--- Additional comment from Carlos Goncalves on 2018-12-22 23:45:54 CET ---

One of Housekeeping's periodic tasks is to clean up amphorae deleted more than $amphora_expiry_age seconds ago (default is 604800 seconds, or 7 days). As you performed multiple performance iterations in a short period of time, you did not hit the expiry age meaning the amphora table kept on growing after each iteration. The real problem lays on the SELECT statement constructed by Octavia via the ORM (SQLAlchemy) -- it is joined eager loading all relationships [1]. The SELECT statement is:


SELECT amphora.created_at AS amphora_created_at, amphora.updated_at AS amphora_updated_at, amphora.id AS amphora_id, amphora.load_balancer_id AS amphora_load_balancer_id, amphora.compute_id AS amphora_compute_id, amphora.lb_network_ip AS amphora_lb_network_ip, amphora.vrrp_ip AS amphora_vrrp_ip, amphora.ha_ip AS amphora_ha_ip, amphora.vrrp_port_id AS amphora_vrrp_port_id, amphora.ha_port_id AS amphora_ha_port_id, amphora.cert_expiration AS amphora_cert_expiration, amphora.cert_busy AS amphora_cert_busy, amphora.`role` AS amphora_role, amphora.status AS amphora_status, amphora.vrrp_interface AS amphora_vrrp_interface, amphora.vrrp_id AS amphora_vrrp_id, amphora.vrrp_priority AS amphora_vrrp_priority, amphora.cached_zone AS amphora_cached_zone, amphora.image_id AS amphora_image_id, amphora.compute_flavor AS amphora_compute_flavor, vip_1.load_balancer_id AS vip_1_load_balancer_id, vip_1.ip_address AS vip_1_ip_address, vip_1.port_id AS vip_1_port_id, vip_1.subnet_id AS vip_1_subnet_id, vip_1.network_id AS vip_1_network_id, vip_1.qos_policy_id AS vip_1_qos_policy_id, vip_1.octavia_owned AS vip_1_octavia_owned, tags_1.resource_id AS tags_1_resource_id, tags_1.tag AS tags_1_tag, health_monitor_1.created_at AS health_monitor_1_created_at, health_monitor_1.updated_at AS health_monitor_1_updated_at, health_monitor_1.id AS health_monitor_1_id, health_monitor_1.project_id AS health_monitor_1_project_id, health_monitor_1.name AS health_monitor_1_name, health_monitor_1.type AS health_monitor_1_type, health_monitor_1.pool_id AS health_monitor_1_pool_id, health_monitor_1.delay AS health_monitor_1_delay, health_monitor_1.timeout AS health_monitor_1_timeout, health_monitor_1.fall_threshold AS health_monitor_1_fall_threshold, health_monitor_1.rise_threshold AS health_monitor_1_rise_threshold, health_monitor_1.http_method AS health_monitor_1_http_method, health_monitor_1.url_path AS health_monitor_1_url_path, health_monitor_1.expected_codes AS health_monitor_1_expected_codes, health_monitor_1.enabled AS health_monitor_1_enabled, health_monitor_1.provisioning_status AS health_monitor_1_provisioning_status, health_monitor_1.operating_status AS health_monitor_1_operating_status, tags_2.resource_id AS tags_2_resource_id, tags_2.tag AS tags_2_tag, member_1.created_at AS member_1_created_at, member_1.updated_at AS member_1_updated_at, member_1.id AS member_1_id, member_1.project_id AS member_1_project_id, member_1.name AS member_1_name, member_1.pool_id AS member_1_pool_id, member_1.subnet_id AS member_1_subnet_id, member_1.ip_address AS member_1_ip_address, member_1.protocol_port AS member_1_protocol_port, member_1.weight AS member_1_weight, member_1.backup AS member_1_backup, member_1.monitor_address AS member_1_monitor_address, member_1.monitor_port AS member_1_monitor_port, member_1.provisioning_status AS member_1_provisioning_status, member_1.operating_status AS member_1_operating_status, member_1.enabled AS member_1_enabled, session_persistence_1.pool_id AS session_persistence_1_pool_id, session_persistence_1.type AS session_persistence_1_type, session_persistence_1.cookie_name AS session_persistence_1_cookie_name, session_persistence_1.persistence_timeout AS session_persistence_1_persistence_timeout, session_persistence_1.persistence_granularity AS session_persistence_1_persistence_granularity, sni_1.listener_id AS sni_1_listener_id, sni_1.tls_container_id AS sni_1_tls_container_id, sni_1.position AS sni_1_position, tags_3.resource_id AS tags_3_resource_id, tags_3.tag AS tags_3_tag, l7rule_1.created_at AS l7rule_1_created_at, l7rule_1.updated_at AS l7rule_1_updated_at, l7rule_1.id AS l7rule_1_id, l7rule_1.project_id AS l7rule_1_project_id, l7rule_1.l7policy_id AS l7rule_1_l7policy_id, l7rule_1.type AS l7rule_1_type, l7rule_1.compare_type AS l7rule_1_compare_type, l7rule_1.`key` AS l7rule_1_key, l7rule_1.value AS l7rule_1_value, l7rule_1.invert AS l7rule_1_invert, l7rule_1.enabled AS l7rule_1_enabled, l7rule_1.provisioning_status AS l7rule_1_provisioning_status, l7rule_1.operating_status AS l7rule_1_operating_status, tags_4.resource_id AS tags_4_resource_id, tags_4.tag AS tags_4_tag, l7policy_1.created_at AS l7policy_1_created_at, l7policy_1.updated_at AS l7policy_1_updated_at, l7policy_1.id AS l7policy_1_id, l7policy_1.project_id AS l7policy_1_project_id, l7policy_1.name AS l7policy_1_name, l7policy_1.description AS l7policy_1_description, l7policy_1.listener_id AS l7policy_1_listener_id, l7policy_1.action AS l7policy_1_action, l7policy_1.redirect_pool_id AS l7policy_1_redirect_pool_id, l7policy_1.redirect_url AS l7policy_1_redirect_url, l7policy_1.redirect_prefix AS l7policy_1_redirect_prefix, l7policy_1.position AS l7policy_1_position, l7policy_1.enabled AS l7policy_1_enabled, l7policy_1.provisioning_status AS l7policy_1_provisioning_status, l7policy_1.operating_status AS l7policy_1_operating_status, tags_5.resource_id AS tags_5_resource_id, tags_5.tag AS tags_5_tag, listener_1.created_at AS listener_1_created_at, listener_1.updated_at AS listener_1_updated_at, listener_1.id AS listener_1_id, listener_1.project_id AS listener_1_project_id, listener_1.name AS listener_1_name, listener_1.description AS listener_1_description, listener_1.protocol AS listener_1_protocol, listener_1.protocol_port AS listener_1_protocol_port, listener_1.connection_limit AS listener_1_connection_limit, listener_1.load_balancer_id AS listener_1_load_balancer_id, listener_1.tls_certificate_id AS listener_1_tls_certificate_id, listener_1.default_pool_id AS listener_1_default_pool_id, listener_1.provisioning_status AS listener_1_provisioning_status, listener_1.operating_status AS listener_1_operating_status, listener_1.enabled AS listener_1_enabled, listener_1.peer_port AS listener_1_peer_port, listener_1.insert_headers AS listener_1_insert_headers, listener_1.timeout_client_data AS listener_1_timeout_client_data, listener_1.timeout_member_connect AS listener_1_timeout_member_connect, listener_1.timeout_member_data AS listener_1_timeout_member_data, listener_1.timeout_tcp_inspect AS listener_1_timeout_tcp_inspect, sni_2.listener_id AS sni_2_listener_id, sni_2.tls_container_id AS sni_2_tls_container_id, sni_2.position AS sni_2_position, tags_6.resource_id AS tags_6_resource_id, tags_6.tag AS tags_6_tag, listener_2.created_at AS listener_2_created_at, listener_2.updated_at AS listener_2_updated_at, listener_2.id AS listener_2_id, listener_2.project_id AS listener_2_project_id, listener_2.name AS listener_2_name, listener_2.description AS listener_2_description, listener_2.protocol AS listener_2_protocol, listener_2.protocol_port AS listener_2_protocol_port, listener_2.connection_limit AS listener_2_connection_limit, listener_2.load_balancer_id AS listener_2_load_balancer_id, listener_2.tls_certificate_id AS listener_2_tls_certificate_id, listener_2.default_pool_id AS listener_2_default_pool_id, listener_2.provisioning_status AS listener_2_provisioning_status, listener_2.operating_status AS listener_2_operating_status, listener_2.enabled AS listener_2_enabled, listener_2.peer_port AS listener_2_peer_port, listener_2.insert_headers AS listener_2_insert_headers, listener_2.timeout_client_data AS listener_2_timeout_client_data, listener_2.timeout_member_connect AS listener_2_timeout_member_connect, listener_2.timeout_member_data AS listener_2_timeout_member_data, listener_2.timeout_tcp_inspect AS listener_2_timeout_tcp_inspect, tags_7.resource_id AS tags_7_resource_id, tags_7.tag AS tags_7_tag, l7rule_2.created_at AS l7rule_2_created_at, l7rule_2.updated_at AS l7rule_2_updated_at, l7rule_2.id AS l7rule_2_id, l7rule_2.project_id AS l7rule_2_project_id, l7rule_2.l7policy_id AS l7rule_2_l7policy_id, l7rule_2.type AS l7rule_2_type, l7rule_2.compare_type AS l7rule_2_compare_type, l7rule_2.`key` AS l7rule_2_key, l7rule_2.value AS l7rule_2_value, l7rule_2.invert AS l7rule_2_invert, l7rule_2.enabled AS l7rule_2_enabled, l7rule_2.provisioning_status AS l7rule_2_provisioning_status, l7rule_2.operating_status AS l7rule_2_operating_status, tags_8.resource_id AS tags_8_resource_id, tags_8.tag AS tags_8_tag, l7policy_2.created_at AS l7policy_2_created_at, l7policy_2.updated_at AS l7policy_2_updated_at, l7policy_2.id AS l7policy_2_id, l7policy_2.project_id AS l7policy_2_project_id, l7policy_2.name AS l7policy_2_name, l7policy_2.description AS l7policy_2_description, l7policy_2.listener_id AS l7policy_2_listener_id, l7policy_2.action AS l7policy_2_action, l7policy_2.redirect_pool_id AS l7policy_2_redirect_pool_id, l7policy_2.redirect_url AS l7policy_2_redirect_url, l7policy_2.redirect_prefix AS l7policy_2_redirect_prefix, l7policy_2.position AS l7policy_2_position, l7policy_2.enabled AS l7policy_2_enabled, l7policy_2.provisioning_status AS l7policy_2_provisioning_status, l7policy_2.operating_status AS l7policy_2_operating_status, tags_9.resource_id AS tags_9_resource_id, tags_9.tag AS tags_9_tag, pool_1.created_at AS pool_1_created_at, pool_1.updated_at AS pool_1_updated_at, pool_1.id AS pool_1_id, pool_1.project_id AS pool_1_project_id, pool_1.name AS pool_1_name, pool_1.description AS pool_1_description, pool_1.protocol AS pool_1_protocol, pool_1.lb_algorithm AS pool_1_lb_algorithm, pool_1.provisioning_status AS pool_1_provisioning_status, pool_1.operating_status AS pool_1_operating_status, pool_1.enabled AS pool_1_enabled, pool_1.load_balancer_id AS pool_1_load_balancer_id, tags_10.resource_id AS tags_10_resource_id, tags_10.tag AS tags_10_tag, health_monitor_2.created_at AS health_monitor_2_created_at, health_monitor_2.updated_at AS health_monitor_2_updated_at, health_monitor_2.id AS health_monitor_2_id, health_monitor_2.project_id AS health_monitor_2_project_id, health_monitor_2.name AS health_monitor_2_name, health_monitor_2.type AS health_monitor_2_type, health_monitor_2.pool_id AS health_monitor_2_pool_id, health_monitor_2.delay AS health_monitor_2_delay, health_monitor_2.timeout AS health_monitor_2_timeout, health_monitor_2.fall_threshold AS health_monitor_2_fall_threshold, health_monitor_2.rise_threshold AS health_monitor_2_rise_threshold, health_monitor_2.http_method AS health_monitor_2_http_method, health_monitor_2.url_path AS health_monitor_2_url_path, health_monitor_2.expected_codes AS health_monitor_2_expected_codes, health_monitor_2.enabled AS health_monitor_2_enabled, health_monitor_2.provisioning_status AS health_monitor_2_provisioning_status, health_monitor_2.operating_status AS health_monitor_2_operating_status, tags_11.resource_id AS tags_11_resource_id, tags_11.tag AS tags_11_tag, member_2.created_at AS member_2_created_at, member_2.updated_at AS member_2_updated_at, member_2.id AS member_2_id, member_2.project_id AS member_2_project_id, member_2.name AS member_2_name, member_2.pool_id AS member_2_pool_id, member_2.subnet_id AS member_2_subnet_id, member_2.ip_address AS member_2_ip_address, member_2.protocol_port AS member_2_protocol_port, member_2.weight AS member_2_weight, member_2.backup AS member_2_backup, member_2.monitor_address AS member_2_monitor_address, member_2.monitor_port AS member_2_monitor_port, member_2.provisioning_status AS member_2_provisioning_status, member_2.operating_status AS member_2_operating_status, member_2.enabled AS member_2_enabled, session_persistence_2.pool_id AS session_persistence_2_pool_id, session_persistence_2.type AS session_persistence_2_type, session_persistence_2.cookie_name AS session_persistence_2_cookie_name, session_persistence_2.persistence_timeout AS session_persistence_2_persistence_timeout, session_persistence_2.persistence_granularity AS session_persistence_2_persistence_granularity, tags_12.resource_id AS tags_12_resource_id, tags_12.tag AS tags_12_tag, l7rule_3.created_at AS l7rule_3_created_at, l7rule_3.updated_at AS l7rule_3_updated_at, l7rule_3.id AS l7rule_3_id, l7rule_3.project_id AS l7rule_3_project_id, l7rule_3.l7policy_id AS l7rule_3_l7policy_id, l7rule_3.type AS l7rule_3_type, l7rule_3.compare_type AS l7rule_3_compare_type, l7rule_3.`key` AS l7rule_3_key, l7rule_3.value AS l7rule_3_value, l7rule_3.invert AS l7rule_3_invert, l7rule_3.enabled AS l7rule_3_enabled, l7rule_3.provisioning_status AS l7rule_3_provisioning_status, l7rule_3.operating_status AS l7rule_3_operating_status, tags_13.resource_id AS tags_13_resource_id, tags_13.tag AS tags_13_tag, l7policy_3.created_at AS l7policy_3_created_at, l7policy_3.updated_at AS l7policy_3_updated_at, l7policy_3.id AS l7policy_3_id, l7policy_3.project_id AS l7policy_3_project_id, l7policy_3.name AS l7policy_3_name, l7policy_3.description AS l7policy_3_description, l7policy_3.listener_id AS l7policy_3_listener_id, l7policy_3.action AS l7policy_3_action, l7policy_3.redirect_pool_id AS l7policy_3_redirect_pool_id, l7policy_3.redirect_url AS l7policy_3_redirect_url, l7policy_3.redirect_prefix AS l7policy_3_redirect_prefix, l7policy_3.position AS l7policy_3_position, l7policy_3.enabled AS l7policy_3_enabled, l7policy_3.provisioning_status AS l7policy_3_provisioning_status, l7policy_3.operating_status AS l7policy_3_operating_status, tags_14.resource_id AS tags_14_resource_id, tags_14.tag AS tags_14_tag, pool_2.created_at AS pool_2_created_at, pool_2.updated_at AS pool_2_updated_at, pool_2.id AS pool_2_id, pool_2.project_id AS pool_2_project_id, pool_2.name AS pool_2_name, pool_2.description AS pool_2_description, pool_2.protocol AS pool_2_protocol, pool_2.lb_algorithm AS pool_2_lb_algorithm, pool_2.provisioning_status AS pool_2_provisioning_status, pool_2.operating_status AS pool_2_operating_status, pool_2.enabled AS pool_2_enabled, pool_2.load_balancer_id AS pool_2_load_balancer_id, sni_3.listener_id AS sni_3_listener_id, sni_3.tls_container_id AS sni_3_tls_container_id, sni_3.position AS sni_3_position, tags_15.resource_id AS tags_15_resource_id, tags_15.tag AS tags_15_tag, health_monitor_3.created_at AS health_monitor_3_created_at, health_monitor_3.updated_at AS health_monitor_3_updated_at, health_monitor_3.id AS health_monitor_3_id, health_monitor_3.project_id AS health_monitor_3_project_id, health_monitor_3.name AS health_monitor_3_name, health_monitor_3.type AS health_monitor_3_type, health_monitor_3.pool_id AS health_monitor_3_pool_id, health_monitor_3.delay AS health_monitor_3_delay, health_monitor_3.timeout AS health_monitor_3_timeout, health_monitor_3.fall_threshold AS health_monitor_3_fall_threshold, health_monitor_3.rise_threshold AS health_monitor_3_rise_threshold, health_monitor_3.http_method AS health_monitor_3_http_method, health_monitor_3.url_path AS health_monitor_3_url_path, health_monitor_3.expected_codes AS health_monitor_3_expected_codes, health_monitor_3.enabled AS health_monitor_3_enabled, health_monitor_3.provisioning_status AS health_monitor_3_provisioning_status, health_monitor_3.operating_status AS health_monitor_3_operating_status, tags_16.resource_id AS tags_16_resource_id, tags_16.tag AS tags_16_tag, member_3.created_at AS member_3_created_at, member_3.updated_at AS member_3_updated_at, member_3.id AS member_3_id, member_3.project_id AS member_3_project_id, member_3.name AS member_3_name, member_3.pool_id AS member_3_pool_id, member_3.subnet_id AS member_3_subnet_id, member_3.ip_address AS member_3_ip_address, member_3.protocol_port AS member_3_protocol_port, member_3.weight AS member_3_weight, member_3.backup AS member_3_backup, member_3.monitor_address AS member_3_monitor_address, member_3.monitor_port AS member_3_monitor_port, member_3.provisioning_status AS member_3_provisioning_status, member_3.operating_status AS member_3_operating_status, member_3.enabled AS member_3_enabled, session_persistence_3.pool_id AS session_persistence_3_pool_id, session_persistence_3.type AS session_persistence_3_type, session_persistence_3.cookie_name AS session_persistence_3_cookie_name, session_persistence_3.persistence_timeout AS session_persistence_3_persistence_timeout, session_persistence_3.persistence_granularity AS session_persistence_3_persistence_granularity, tags_17.resource_id AS tags_17_resource_id, tags_17.tag AS tags_17_tag, pool_3.created_at AS pool_3_created_at, pool_3.updated_at AS pool_3_updated_at, pool_3.id AS pool_3_id, pool_3.project_id AS pool_3_project_id, pool_3.name AS pool_3_name, pool_3.description AS pool_3_description, pool_3.protocol AS pool_3_protocol, pool_3.lb_algorithm AS pool_3_lb_algorithm, pool_3.provisioning_status AS pool_3_provisioning_status, pool_3.operating_status AS pool_3_operating_status, pool_3.enabled AS pool_3_enabled, pool_3.load_balancer_id AS pool_3_load_balancer_id, tags_18.resource_id AS tags_18_resource_id, tags_18.tag AS tags_18_tag, l7rule_4.created_at AS l7rule_4_created_at, l7rule_4.updated_at AS l7rule_4_updated_at, l7rule_4.id AS l7rule_4_id, l7rule_4.project_id AS l7rule_4_project_id, l7rule_4.l7policy_id AS l7rule_4_l7policy_id, l7rule_4.type AS l7rule_4_type, l7rule_4.compare_type AS l7rule_4_compare_type, l7rule_4.`key` AS l7rule_4_key, l7rule_4.value AS l7rule_4_value, l7rule_4.invert AS l7rule_4_invert, l7rule_4.enabled AS l7rule_4_enabled, l7rule_4.provisioning_status AS l7rule_4_provisioning_status, l7rule_4.operating_status AS l7rule_4_operating_status, tags_19.resource_id AS tags_19_resource_id, tags_19.tag AS tags_19_tag, l7policy_4.created_at AS l7policy_4_created_at, l7policy_4.updated_at AS l7policy_4_updated_at, l7policy_4.id AS l7policy_4_id, l7policy_4.project_id AS l7policy_4_project_id, l7policy_4.name AS l7policy_4_name, l7policy_4.description AS l7policy_4_description, l7policy_4.listener_id AS l7policy_4_listener_id, l7policy_4.action AS l7policy_4_action, l7policy_4.redirect_pool_id AS l7policy_4_redirect_pool_id, l7policy_4.redirect_url AS l7policy_4_redirect_url, l7policy_4.redirect_prefix AS l7policy_4_redirect_prefix, l7policy_4.position AS l7policy_4_position, l7policy_4.enabled AS l7policy_4_enabled, l7policy_4.provisioning_status AS l7policy_4_provisioning_status, l7policy_4.operating_status AS l7policy_4_operating_status, tags_20.resource_id AS tags_20_resource_id, tags_20.tag AS tags_20_tag, listener_3.created_at AS listener_3_created_at, listener_3.updated_at AS listener_3_updated_at, listener_3.id AS listener_3_id, listener_3.project_id AS listener_3_project_id, listener_3.name AS listener_3_name, listener_3.description AS listener_3_description, listener_3.protocol AS listener_3_protocol, listener_3.protocol_port AS listener_3_protocol_port, listener_3.connection_limit AS listener_3_connection_limit, listener_3.load_balancer_id AS listener_3_load_balancer_id, listener_3.tls_certificate_id AS listener_3_tls_certificate_id, listener_3.default_pool_id AS listener_3_default_pool_id, listener_3.provisioning_status AS listener_3_provisioning_status, listener_3.operating_status AS listener_3_operating_status, listener_3.enabled AS listener_3_enabled, listener_3.peer_port AS listener_3_peer_port, listener_3.insert_headers AS listener_3_insert_headers, listener_3.timeout_client_data AS listener_3_timeout_client_data, listener_3.timeout_member_connect AS listener_3_timeout_member_connect, listener_3.timeout_member_data AS listener_3_timeout_member_data, listener_3.timeout_tcp_inspect AS listener_3_timeout_tcp_inspect, tags_21.resource_id AS tags_21_resource_id, tags_21.tag AS tags_21_tag, load_balancer_1.created_at AS load_balancer_1_created_at, load_balancer_1.updated_at AS load_balancer_1_updated_at, load_balancer_1.id AS load_balancer_1_id, load_balancer_1.project_id AS load_balancer_1_project_id, load_balancer_1.name AS load_balancer_1_name, load_balancer_1.description AS load_balancer_1_description, load_balancer_1.provisioning_status AS load_balancer_1_provisioning_status, load_balancer_1.operating_status AS load_balancer_1_operating_status, load_balancer_1.topology AS load_balancer_1_topology, load_balancer_1.enabled AS load_balancer_1_enabled, load_balancer_1.server_group_id AS load_balancer_1_server_group_id, load_balancer_1.provider AS load_balancer_1_provider, vrrp_group_1.load_balancer_id AS vrrp_group_1_load_balancer_id, vrrp_group_1.vrrp_group_name AS vrrp_group_1_vrrp_group_name, vrrp_group_1.vrrp_auth_type AS vrrp_group_1_vrrp_auth_type, vrrp_group_1.vrrp_auth_pass AS vrrp_group_1_vrrp_auth_pass, vrrp_group_1.advert_int AS vrrp_group_1_advert_int
FROM amphora LEFT OUTER JOIN load_balancer AS load_balancer_1 ON load_balancer_1.id = amphora.load_balancer_id LEFT OUTER JOIN vip AS vip_1 ON load_balancer_1.id = vip_1.load_balancer_id LEFT OUTER JOIN pool AS pool_1 ON load_balancer_1.id = pool_1.load_balancer_id LEFT OUTER JOIN health_monitor AS health_monitor_1 ON pool_1.id = health_monitor_1.pool_id LEFT OUTER JOIN tags AS tags_1 ON tags_1.resource_id = health_monitor_1.id LEFT OUTER JOIN member AS member_1 ON pool_1.id = member_1.pool_id LEFT OUTER JOIN tags AS tags_2 ON tags_2.resource_id = member_1.id LEFT OUTER JOIN session_persistence AS session_persistence_1 ON pool_1.id = session_persistence_1.pool_id LEFT OUTER JOIN listener AS listener_1 ON pool_1.id = listener_1.default_pool_id LEFT OUTER JOIN sni AS sni_1 ON listener_1.id = sni_1.listener_id LEFT OUTER JOIN l7policy AS l7policy_1 ON listener_1.id = l7policy_1.listener_id LEFT OUTER JOIN l7rule AS l7rule_1 ON l7policy_1.id = l7rule_1.l7policy_id LEFT OUTER JOIN tags AS tags_3 ON tags_3.resource_id = l7rule_1.id LEFT OUTER JOIN tags AS tags_4 ON tags_4.resource_id = l7policy_1.id LEFT OUTER JOIN tags AS tags_5 ON tags_5.resource_id = listener_1.id LEFT OUTER JOIN l7policy AS l7policy_2 ON pool_1.id = l7policy_2.redirect_pool_id LEFT OUTER JOIN listener AS listener_2 ON listener_2.id = l7policy_2.listener_id LEFT OUTER JOIN sni AS sni_2 ON listener_2.id = sni_2.listener_id LEFT OUTER JOIN tags AS tags_6 ON tags_6.resource_id = listener_2.id LEFT OUTER JOIN l7rule AS l7rule_2 ON l7policy_2.id = l7rule_2.l7policy_id LEFT OUTER JOIN tags AS tags_7 ON tags_7.resource_id = l7rule_2.id LEFT OUTER JOIN tags AS tags_8 ON tags_8.resource_id = l7policy_2.id LEFT OUTER JOIN tags AS tags_9 ON tags_9.resource_id = pool_1.id LEFT OUTER JOIN listener AS listener_3 ON load_balancer_1.id = listener_3.load_balancer_id LEFT OUTER JOIN pool AS pool_2 ON pool_2.id = listener_3.default_pool_id LEFT OUTER JOIN health_monitor AS health_monitor_2 ON pool_2.id = health_monitor_2.pool_id LEFT OUTER JOIN tags AS tags_10 ON tags_10.resource_id = health_monitor_2.id LEFT OUTER JOIN member AS member_2 ON pool_2.id = member_2.pool_id LEFT OUTER JOIN tags AS tags_11 ON tags_11.resource_id = member_2.id LEFT OUTER JOIN session_persistence AS session_persistence_2 ON pool_2.id = session_persistence_2.pool_id LEFT OUTER JOIN l7policy AS l7policy_3 ON pool_2.id = l7policy_3.redirect_pool_id LEFT OUTER JOIN l7rule AS l7rule_3 ON l7policy_3.id = l7rule_3.l7policy_id LEFT OUTER JOIN tags AS tags_12 ON tags_12.resource_id = l7rule_3.id LEFT OUTER JOIN tags AS tags_13 ON tags_13.resource_id = l7policy_3.id LEFT OUTER JOIN tags AS tags_14 ON tags_14.resource_id = pool_2.id LEFT OUTER JOIN sni AS sni_3 ON listener_3.id = sni_3.listener_id LEFT OUTER JOIN l7policy AS l7policy_4 ON listener_3.id = l7policy_4.listener_id LEFT OUTER JOIN pool AS pool_3 ON pool_3.id = l7policy_4.redirect_pool_id LEFT OUTER JOIN health_monitor AS health_monitor_3 ON pool_3.id = health_monitor_3.pool_id LEFT OUTER JOIN tags AS tags_15 ON tags_15.resource_id = health_monitor_3.id LEFT OUTER JOIN member AS member_3 ON pool_3.id = member_3.pool_id LEFT OUTER JOIN tags AS tags_16 ON tags_16.resource_id = member_3.id LEFT OUTER JOIN session_persistence AS session_persistence_3 ON pool_3.id = session_persistence_3.pool_id LEFT OUTER JOIN tags AS tags_17 ON tags_17.resource_id = pool_3.id LEFT OUTER JOIN l7rule AS l7rule_4 ON l7policy_4.id = l7rule_4.l7policy_id LEFT OUTER JOIN tags AS tags_18 ON tags_18.resource_id = l7rule_4.id LEFT OUTER JOIN tags AS tags_19 ON tags_19.resource_id = l7policy_4.id LEFT OUTER JOIN tags AS tags_20 ON tags_20.resource_id = listener_3.id LEFT OUTER JOIN tags AS tags_21 ON tags_21.resource_id = load_balancer_1.id LEFT OUTER JOIN vrrp_group AS vrrp_group_1 ON load_balancer_1.id = vrrp_group_1.load_balancer_id
WHERE amphora.status = %(status_1)s AND amphora.updated_at < %(updated_at_1)s ORDER BY l7policy_1.position, l7policy_4.position


The task does not need such amount of information, only the amphora ID. The statement could be simplified by not loading any relationship or, at most, lazy loading them. In any of both loading strategies, the statement would look like:

SELECT amphora.created_at AS amphora_created_at, amphora.updated_at AS amphora_updated_at, amphora.id AS amphora_id, amphora.load_balancer_id AS amphora_load_balancer_id, amphora.compute_id AS amphora_compute_id, amphora.lb_network_ip AS amphora_lb_network_ip, amphora.vrrp_ip AS amphora_vrrp_ip, amphora.ha_ip AS amphora_ha_ip, amphora.vrrp_port_id AS amphora_vrrp_port_id, amphora.ha_port_id AS amphora_ha_port_id, amphora.cert_expiration AS amphora_cert_expiration, amphora.cert_busy AS amphora_cert_busy, amphora.`role` AS amphora_role, amphora.status AS amphora_status, amphora.vrrp_interface AS amphora_vrrp_interface, amphora.vrrp_id AS amphora_vrrp_id, amphora.vrrp_priority AS amphora_vrrp_priority, amphora.cached_zone AS amphora_cached_zone, amphora.image_id AS amphora_image_id, amphora.compute_flavor AS amphora_compute_flavor
FROM amphora
WHERE amphora.status = %(status_1)s AND amphora.updated_at < %(updated_at_1)s



[1] https://github.com/openstack/octavia/blob/9bb4c5c1c4afeb14942f00de6f68eda27553b669/octavia/db/repositories.py#L1169