Skip to content

Force select all during pagination

Alex Pooley requested to merge 368969-paginated-query-timeout-on-api-groups-list into master

What does this MR do and why?

A REST API request for groups can result in an extremely poor performing query.

As part of our Kaminari pagination code pathway, we remove offset/limit/order parts of a query. When Rails performs a count on this modified relation, it may use SELECT count(count_column) rather than SELECT count(*) resulting in poor performance.

This MR will force the SELECT count(*) for this specific REST endpoint by explicitly defining a select value.

This work is behind a feature flag.

Query Plans

With feature flag disabled: https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/11813/commands/41865

With feature flag enabled: https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/11813/commands/41866

Query generated by hitting GET "/api/v4/groups?private_token=[FILTERED]&min_access_level=40" as part of the spec https://gitlab.com/gitlab-org/gitlab/-/blob/2dfee66ccb4d5bbc3f0038afbb00f9caca7bc9de/spec/requests/api/groups_spec.rb#L508.

How to set up and validate locally

Numbered steps to set up and validate the change are strongly suggested.

MR acceptance checklist

This checklist encourages us to confirm any changes have been analyzed to reduce risks in quality, performance, reliability, security, and maintainability.

Related to #368969 (closed)

Edited by Alex Pooley

Merge request reports