Ansible Plugin

Request to /pulp/api/v3/content/ansible/collection_versions (see Request) produces 6 + 2N SQL queries, where N is a number of collection version in response.

This is highly inefficient and may result in a high request latency in production environments. For Automation Hub it results in 1.8-4.2 seconds per request.

Queries list includes 2 queries that runs SELECT DISTINCT over all fields and includes explicit list of UUIDs of all certified collections in the IN clause.

Request:¶

GET /pulp/api/v3/content/ansible/collection_versions/?is_highest=True&offset=0&limit=12&exclude_fields=docs_blob&certification=certified&deprecated=false

Queries:¶

1. Queries list:

https://gist.github.com/cutwater/70f6adbe68d88cec0df14141d508f5cf

2. Explain analyze of query executed against the production database:

To be added later in comments.

Versions tested:¶

1. pulpcore==3.5.0 + pulp-ansible==0.2.0b15
2. pulpcore==3.7.0 + pulp-ansible==0.4.0

Actions required:¶

1. Eliminate 2N queries.
2. Optimize SELECT DISTINCT or remove.
3. Remove explicit list of all certified collections in the IN clause. Replace with a subquery if needed.