Project

Profile

Help

Issue #7564

closed

Excess number of suboptimal queries when querying collections list

Added by osapryki over 3 years ago. Updated about 3 years ago.

Status:
CLOSED - DUPLICATE
Priority:
Normal
Assignee:
-
Sprint/Milestone:
-
Start date:
Due date:
Estimated time:
Severity:
2. Medium
Platform Release:
OS:
Triaged:
No
Groomed:
No
Sprint Candidate:
No
Tags:
Sprint:
Quarter:

Description

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

  1. 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.

Files

Screenshot_20201008_143356.png (1.07 MB) Screenshot_20201008_143356.png osapryki, 10/08/2020 02:56 PM
Actions #1

Updated by osapryki over 3 years ago

  • Priority changed from High to Normal
Actions #3

Updated by osapryki over 3 years ago

This is a partial dump of SQL query generated on CI environment which has 5290 collection version records in the database.

Actions #4

Updated by osapryki over 3 years ago

The previous dump is generated on API call to /api/automation-hub/v3/collections/ which calls pulp_ansible.app.galaxy.v3.CollectionViewSet

Actions #6

Updated by fao89 about 3 years ago

  • Status changed from NEW to CLOSED - DUPLICATE

addressed on #8012

Also available in: Atom PDF