Performance regression in queries with multiple JOINs between versions 1.76.6 and 1.78.5 #10180
Description
As a Nautobot developer and Dolt user, I wanted to report a performance regression that first appears in version 1.76.6 and still persists in the latest version 1.78.7. This issue does not occur in version 1.76.5.
I have confirmed a significant slowdown affecting queries that contain a large number of INNER JOIN, LEFT JOIN, or OUTER JOIN clauses.
Observed behavior:
- The same complex JOIN query executed on v1.76.5 returns:
1 row in set (0.02 sec) - Executing the identical query on v1.76.6 or later (up to v1.78.7) results in:
1 row in set (112.52 sec)
This indicates a major slowdown introduced between versions 1.76.5 → 1.76.6, which persists in the latest release.
Expected behavior:
Query performance should be consistent with pre-1.76.6 behavior and not degrade drastically for queries involving multiple JOIN operations.
Steps to reproduce:
- Integrate Nautobot with Dolt v1.78.7.
- Run the Nautobot management command
generate_test_datato populate the database. - Execute the query provided below.
I apologize in advance for the personalized query. I was not able to narrow it down further.
Example query:
SELECT `dcim_device`.`id`,
`dcim_device`.`created`,
`dcim_device`.`last_updated`,
`dcim_device`.`_custom_field_data`,
`dcim_device`.`local_config_context_data`,
`dcim_device`.`local_config_context_schema_id`,
`dcim_device`.`local_config_context_data_owner_content_type_id`,
`dcim_device`.`local_config_context_data_owner_object_id`,
`dcim_device`.`device_type_id`,
`dcim_device`.`status_id`,
`dcim_device`.`role_id`,
`dcim_device`.`tenant_id`,
`dcim_device`.`platform_id`,
`dcim_device`.`name`,
`dcim_device`.`_name`,
`dcim_device`.`serial`,
`dcim_device`.`asset_tag`,
`dcim_device`.`location_id`,
`dcim_device`.`rack_id`,
`dcim_device`.`position`,
`dcim_device`.`face`,
`dcim_device`.`primary_ip4_id`,
`dcim_device`.`primary_ip6_id`,
`dcim_device`.`virtual_chassis_id`,
`dcim_device`.`device_redundancy_group_id`,
`dcim_device`.`device_redundancy_group_priority`,
`dcim_device`.`software_version_id`,
`dcim_device`.`vc_position`,
`dcim_device`.`vc_priority`,
`dcim_device`.`comments`,
`dcim_device`.`secrets_group_id`,
`dcim_device`.`controller_managed_device_group_id`,
`dcim_devicetype`.`id`,
`dcim_devicetype`.`created`,
`dcim_devicetype`.`last_updated`,
`dcim_devicetype`.`_custom_field_data`,
`dcim_devicetype`.`manufacturer_id`,
`dcim_devicetype`.`device_family_id`,
`dcim_devicetype`.`model`,
`dcim_devicetype`.`part_number`,
`dcim_devicetype`.`u_height`,
`dcim_devicetype`.`is_full_depth`,
`dcim_devicetype`.`subdevice_role`,
`dcim_devicetype`.`front_image`,
`dcim_devicetype`.`rear_image`,
`dcim_devicetype`.`comments`,
`dcim_manufacturer`.`id`,
`dcim_manufacturer`.`created`,
`dcim_manufacturer`.`last_updated`,
`dcim_manufacturer`.`_custom_field_data`,
`dcim_manufacturer`.`name`,
`dcim_manufacturer`.`description`,
`dcim_devicefamily`.`id`,
`dcim_devicefamily`.`created`,
`dcim_devicefamily`.`last_updated`,
`dcim_devicefamily`.`_custom_field_data`,
`dcim_devicefamily`.`name`,
`dcim_devicefamily`.`description`,
`extras_status`.`id`,
`extras_status`.`created`,
`extras_status`.`last_updated`,
`extras_status`.`_custom_field_data`,
`extras_status`.`name`,
`extras_status`.`color`,
`extras_status`.`description`,
`extras_role`.`id`,
`extras_role`.`created`,
`extras_role`.`last_updated`,
`extras_role`.`_custom_field_data`,
`extras_role`.`name`,
`extras_role`.`color`,
`extras_role`.`description`,
`extras_role`.`weight`,
`tenancy_tenant`.`id`,
`tenancy_tenant`.`created`,
`tenancy_tenant`.`last_updated`,
`tenancy_tenant`.`_custom_field_data`,
`tenancy_tenant`.`name`,
`tenancy_tenant`.`tenant_group_id`,
`tenancy_tenant`.`description`,
`tenancy_tenant`.`comments`,
`tenancy_tenantgroup`.`id`,
`tenancy_tenantgroup`.`created`,
`tenancy_tenantgroup`.`last_updated`,
`tenancy_tenantgroup`.`_custom_field_data`,
`tenancy_tenantgroup`.`parent_id`,
`tenancy_tenantgroup`.`name`,
`tenancy_tenantgroup`.`description`,
`dcim_platform`.`id`,
`dcim_platform`.`created`,
`dcim_platform`.`last_updated`,
`dcim_platform`.`_custom_field_data`,
`dcim_platform`.`name`,
`dcim_platform`.`manufacturer_id`,
`dcim_platform`.`network_driver`,
`dcim_platform`.`napalm_driver`,
`dcim_platform`.`napalm_args`,
`dcim_platform`.`description`,
`dcim_location`.`id`,
`dcim_location`.`created`,
`dcim_location`.`last_updated`,
`dcim_location`.`_custom_field_data`,
`dcim_location`.`parent_id`,
`dcim_location`.`name`,
`dcim_location`.`_name`,
`dcim_location`.`location_type_id`,
`dcim_location`.`status_id`,
`dcim_location`.`tenant_id`,
`dcim_location`.`description`,
`dcim_location`.`facility`,
`dcim_location`.`asn`,
`dcim_location`.`time_zone`,
`dcim_location`.`physical_address`,
`dcim_location`.`shipping_address`,
`dcim_location`.`latitude`,
`dcim_location`.`longitude`,
`dcim_location`.`contact_name`,
`dcim_location`.`contact_phone`,
`dcim_location`.`contact_email`,
`dcim_location`.`comments`,
`dcim_rack`.`id`,
`dcim_rack`.`created`,
`dcim_rack`.`last_updated`,
`dcim_rack`.`_custom_field_data`,
`dcim_rack`.`name`,
`dcim_rack`.`_name`,
`dcim_rack`.`status_id`,
`dcim_rack`.`role_id`,
`dcim_rack`.`facility_id`,
`dcim_rack`.`location_id`,
`dcim_rack`.`rack_group_id`,
`dcim_rack`.`tenant_id`,
`dcim_rack`.`serial`,
`dcim_rack`.`asset_tag`,
`dcim_rack`.`type`,
`dcim_rack`.`width`,
`dcim_rack`.`u_height`,
`dcim_rack`.`desc_units`,
`dcim_rack`.`outer_width`,
`dcim_rack`.`outer_depth`,
`dcim_rack`.`outer_unit`,
`dcim_rack`.`comments`,
`dcim_rackgroup`.`id`,
`dcim_rackgroup`.`created`,
`dcim_rackgroup`.`last_updated`,
`dcim_rackgroup`.`_custom_field_data`,
`dcim_rackgroup`.`parent_id`,
`dcim_rackgroup`.`name`,
`dcim_rackgroup`.`location_id`,
`dcim_rackgroup`.`description`,
`ipam_ipaddress`.`id`,
`ipam_ipaddress`.`created`,
`ipam_ipaddress`.`last_updated`,
`ipam_ipaddress`.`_custom_field_data`,
`ipam_ipaddress`.`host`,
`ipam_ipaddress`.`mask_length`,
`ipam_ipaddress`.`type`,
`ipam_ipaddress`.`status_id`,
`ipam_ipaddress`.`role_id`,
`ipam_ipaddress`.`parent_id`,
`ipam_ipaddress`.`ip_version`,
`ipam_ipaddress`.`tenant_id`,
`ipam_ipaddress`.`nat_inside_id`,
`ipam_ipaddress`.`dns_name`,
`ipam_ipaddress`.`description`,
T14.`id`,
T14.`created`,
T14.`last_updated`,
T14.`_custom_field_data`,
T14.`host`,
T14.`mask_length`,
T14.`type`,
T14.`status_id`,
T14.`role_id`,
T14.`parent_id`,
T14.`ip_version`,
T14.`tenant_id`,
T14.`nat_inside_id`,
T14.`dns_name`,
T14.`description`,
`dcim_virtualchassis`.`id`,
`dcim_virtualchassis`.`created`,
`dcim_virtualchassis`.`last_updated`,
`dcim_virtualchassis`.`_custom_field_data`,
`dcim_virtualchassis`.`master_id`,
`dcim_virtualchassis`.`name`,
`dcim_virtualchassis`.`domain`,
`dcim_deviceredundancygroup`.`id`,
`dcim_deviceredundancygroup`.`created`,
`dcim_deviceredundancygroup`.`last_updated`,
`dcim_deviceredundancygroup`.`_custom_field_data`,
`dcim_deviceredundancygroup`.`name`,
`dcim_deviceredundancygroup`.`status_id`,
`dcim_deviceredundancygroup`.`description`,
`dcim_deviceredundancygroup`.`failover_strategy`,
`dcim_deviceredundancygroup`.`comments`,
`dcim_deviceredundancygroup`.`secrets_group_id`,
`dcim_softwareversion`.`id`,
`dcim_softwareversion`.`created`,
`dcim_softwareversion`.`last_updated`,
`dcim_softwareversion`.`_custom_field_data`,
`dcim_softwareversion`.`platform_id`,
`dcim_softwareversion`.`version`,
`dcim_softwareversion`.`alias`,
`dcim_softwareversion`.`release_date`,
`dcim_softwareversion`.`end_of_support_date`,
`dcim_softwareversion`.`documentation_url`,
`dcim_softwareversion`.`long_term_support`,
`dcim_softwareversion`.`pre_release`,
`dcim_softwareversion`.`status_id`,
`extras_secretsgroup`.`id`,
`extras_secretsgroup`.`created`,
`extras_secretsgroup`.`last_updated`,
`extras_secretsgroup`.`_custom_field_data`,
`extras_secretsgroup`.`name`,
`extras_secretsgroup`.`description`,
`dcim_controllermanageddevicegroup`.`id`,
`dcim_controllermanageddevicegroup`.`created`,
`dcim_controllermanageddevicegroup`.`last_updated`,
`dcim_controllermanageddevicegroup`.`_custom_field_data`,
`dcim_controllermanageddevicegroup`.`parent_id`,
`dcim_controllermanageddevicegroup`.`name`,
`dcim_controllermanageddevicegroup`.`description`,
`dcim_controllermanageddevicegroup`.`weight`,
`dcim_controllermanageddevicegroup`.`controller_id`,
`dcim_controllermanageddevicegroup`.`capabilities`,
`dcim_controllermanageddevicegroup`.`tenant_id`,
`dcim_controller`.`id`,
`dcim_controller`.`created`,
`dcim_controller`.`last_updated`,
`dcim_controller`.`_custom_field_data`,
`dcim_controller`.`name`,
`dcim_controller`.`status_id`,
`dcim_controller`.`description`,
`dcim_controller`.`location_id`,
`dcim_controller`.`platform_id`,
`dcim_controller`.`role_id`,
`dcim_controller`.`capabilities`,
`dcim_controller`.`tenant_id`,
`dcim_controller`.`external_integration_id`,
`dcim_controller`.`controller_device_id`,
`dcim_controller`.`controller_device_redundancy_group_id`
FROM `dcim_device`
INNER JOIN `dcim_devicetype` ON (`dcim_device`.`device_type_id` = `dcim_devicetype`.`id`)
INNER JOIN `dcim_manufacturer` ON (`dcim_devicetype`.`manufacturer_id` = `dcim_manufacturer`.`id`)
LEFT OUTER JOIN `dcim_devicefamily` ON (`dcim_devicetype`.`device_family_id` = `dcim_devicefamily`.`id`)
INNER JOIN `extras_status` ON (`dcim_device`.`status_id` = `extras_status`.`id`)
INNER JOIN `extras_role` ON (`dcim_device`.`role_id` = `extras_role`.`id`)
LEFT OUTER JOIN `tenancy_tenant` ON (`dcim_device`.`tenant_id` = `tenancy_tenant`.`id`)
LEFT OUTER JOIN `tenancy_tenantgroup` ON (`tenancy_tenant`.`tenant_group_id` = `tenancy_tenantgroup`.`id`)
LEFT OUTER JOIN `dcim_platform` ON (`dcim_device`.`platform_id` = `dcim_platform`.`id`)
INNER JOIN `dcim_location` ON (`dcim_device`.`location_id` = `dcim_location`.`id`)
LEFT OUTER JOIN `dcim_rack` ON (`dcim_device`.`rack_id` = `dcim_rack`.`id`)
LEFT OUTER JOIN `dcim_rackgroup` ON (`dcim_rack`.`rack_group_id` = `dcim_rackgroup`.`id`)
LEFT OUTER JOIN `ipam_ipaddress` ON (`dcim_device`.`primary_ip4_id` = `ipam_ipaddress`.`id`)
LEFT OUTER JOIN `ipam_ipaddress` T14 ON (`dcim_device`.`primary_ip6_id` = T14.`id`)
LEFT OUTER JOIN `dcim_virtualchassis` ON (`dcim_device`.`virtual_chassis_id` = `dcim_virtualchassis`.`id`)
LEFT OUTER JOIN `dcim_deviceredundancygroup` ON (`dcim_device`.`device_redundancy_group_id` = `dcim_deviceredundancygroup`.`id`)
LEFT OUTER JOIN `dcim_softwareversion` ON (`dcim_device`.`software_version_id` = `dcim_softwareversion`.`id`)
LEFT OUTER JOIN `extras_secretsgroup` ON (`dcim_device`.`secrets_group_id` = `extras_secretsgroup`.`id`)
LEFT OUTER JOIN `dcim_controllermanageddevicegroup` ON (`dcim_device`.`controller_managed_device_group_id` = `dcim_controllermanageddevicegroup`.`id`)
LEFT OUTER JOIN `dcim_controller` ON (`dcim_controllermanageddevicegroup`.`controller_id` = `dcim_controller`.`id`)
WHERE `dcim_device`.`id` = '4064193ae2514118b5cc9e61fc467d6e'
LIMIT 21;