Excess number of suboptimal queries when querying collections list
/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
- Queries list:
- Explain analyze of query executed against the production database:
To be added later in comments.
pulpcore==3.5.0 + pulp-ansible==0.2.0b15
pulpcore==3.7.0 + pulp-ansible==0.4.0
- Eliminate 2N queries.
SELECT DISTINCTor remove.
- Remove explicit list of all certified collections in the
INclause. Replace with a subquery if needed.
#2 Updated by fao89 about 1 year ago
the distinct comes from MultipleChoiceFilter: https://django-filter.readthedocs.io/en/stable/ref/filters.html?highlight=distinct#multiplechoicefilter