Improve "NeutronDbObject.objects_exist" performance

[Rodolfo Alonso]

Current "NeutronDbObject.objects_exist" implementation generates a query (quite complex most of the time) to retrieve an OVO object. That usually implies a large set of register columns, joined queries or subqueries. Then, the method adds the "count" SQL syntagm to return only the number of registers found.

This query can be optimized by:
- Limiting the number of registers to be retrieved to only one. The goal of the "objects_exist" method is to know if there are objects or not. Finding one is enough
- Limiting the complexity of the query by requesting only one column, provided as a method parameter, that could be, for example, the ID.

[Rodolfo Alonso (rodolfo-alonso-hernandez)]

As an example, using the method "DbBasePluginCommon._is_mac_in_use". This is called when you create a port passing the MAC address. An easy example to test.

The query at [1], that is called from [2], called from "objects_exist", is [3].

If we instead query by one single parameter, "id" for example (Port OVO has ID), the query is:
  SELECT ports.id AS ports_id
  FROM ports
  WHERE ports.network_id IN (%(network_id_1)s) AND ports.mac_address IN (%(mac_address_1)s)

This is clearly a much easier query to be processed by the DB backend.

[1]https://github.com/openstack/neutron/blob/98c934ef6a8041bbd7b99ac49f53986798e8ef81/neutron/objects/db/api.py#L28
[2]https://github.com/openstack/neutron/blob/98c934ef6a8041bbd7b99ac49f53986798e8ef81/neutron/objects/db/api.py#L36
[3]http://paste.openstack.org/show/804727/

[OpenStack Infra (hudson-openstack)]

Related fix proposed to branch: master
Review: https://review.opendev.org/c/openstack/neutron-lib/+/787672

[OpenStack Infra (hudson-openstack)]

Fix proposed to branch: master
Review: https://review.opendev.org/c/openstack/neutron/+/787681

[OpenStack Infra (hudson-openstack)]

Reviewed: https://review.opendev.org/c/openstack/neutron-lib/+/787672
Committed: https://opendev.org/openstack/neutron-lib/commit/e2a0f7008d4ed3edea32c8b6e083ef3044154848
Submitter: "Zuul (22348)"
Branch: master

commit e2a0f7008d4ed3edea32c8b6e083ef3044154848
Author: Rodolfo Alonso Hernandez <email address hidden>
Date: Fri Apr 23 07:13:33 2021 +0000

    Return one single model field in "get_collection_query"

    Added a new input parameter, "field". This parameter represents a
    column from the model DB register. The "query_with_hooks" method
    will return a SQL query that retrieves only the selected field
    instead of the full DB model, that implies all register back
    references (that implies JOIN clauses for each child DB register
    related to the parent DB model).

    The goal of this patch is to reduce the query complexity and thus
    reduce the DB execution time.

    This new parameter is a string.

    Related-Bug: #1925528

    Change-Id: Ia2f02ed576fda9d91367619de35d7c0900336a4e

[OpenStack Infra (hudson-openstack)]

Reviewed: https://review.opendev.org/c/openstack/neutron/+/787681
Committed: https://opendev.org/openstack/neutron/commit/9911d414c6a03cbdcc5f46a9ed7cfc59d70a5168
Submitter: "Zuul (22348)"
Branch: master

commit 9911d414c6a03cbdcc5f46a9ed7cfc59d70a5168
Author: Rodolfo Alonso Hernandez <email address hidden>
Date: Fri Apr 23 13:23:28 2021 +0000

    Improve "objects.db.api.count" method

    Two improvements are implemented in this method:
    - Add a query limit number parameter. This parameter is used by
      "NeutronDbObject.objects_exist" to limit the number of registers
      retrieved to 1.
    - Add a query field parameter. This is the name (string) of the
      "obj_cls.fields" list. That will reduce the columns to be
      retrieved to only one. To check the existence of a DB register,
      there is not need to retrieve the full DB model including the back
      references.
      In case of not passing any value, the first "obj_cls.primary_keys"
      value will be used instead.

    Closes-Bug: #1925528

    Change-Id: I9fd5e306e293102c366d89c01bbe8b13721d59b0

[OpenStack Infra (hudson-openstack)]

Fix proposed to branch: master
Review: https://review.opendev.org/c/openstack/neutron-lib/+/807686

[OpenStack Infra (hudson-openstack)]

This issue was fixed in the openstack/neutron 19.0.0.0rc1 release candidate.