Skip to content

Improve pagination of users in admin panel

What does this MR do?

On instances with a large number of users, the users list in the admin panel times out due to the COUNT query used for pagination.

Screenshots

Before After (if estimate is over 1000 users)
Screen_Shot_2021-04-21_at_7.24.45_PM Screen_Shot_2021-04-21_at_9.14.59_PM

Queries

Before

SELECT COUNT(*) FROM "users" WHERE ("users"."state" IN ('active')) AND ("users"."user_type" IS NULL OR "users"."user_type" != 5)
Time: 7.220 min
  - planning: 2.126 ms
  - execution: 7.220 min (estimated* for prod: 286.056...419.819 s)
    - I/O read: 7.377 min
    - I/O write: N/A

Shared buffers:
  - hits: 1158297 (~8.80 GiB) from the buffer pool
  - reads: 591864 (~4.50 GiB) from the OS file cache, including disk I/O
  - dirtied: 29490 (~230.40 MiB)
  - writes: 0

https://explain.depesz.com/s/QYMu

After

SELECT pg_class.relname AS table_name, reltuples::bigint AS estimate FROM "pg_class" LEFT JOIN pg_stat_user_tables ON pg_stat_user_tables.relid = pg_class.oid WHERE "pg_class"."relname" = 'users' AND (schemaname = current_schema())
Time: 8.494 ms
  - planning: 1.514 ms
  - execution: 6.980 ms
    - I/O read: 0.071 ms
    - I/O write: N/A

Shared buffers:
  - hits: 1642 (~12.80 MiB) from the buffer pool
  - reads: 1 (~8.00 KiB) from the OS file cache, including disk I/O
  - dirtied: 0
  - writes: 0

https://explain.depesz.com/s/phck

Note that the "Before" query will still be executed if this estimate returns < 1000. But that query should be fine for small instances with very few users.

Screenshots (strongly suggested)

Does this MR meet the acceptance criteria?

Conformity

Availability and Testing

Security

If this MR contains changes to processing or storing of credentials or tokens, authorization and authentication methods and other items described in the security review guidelines:

  • Label as security and @ mention @gitlab-com/gl-security/appsec
  • The MR includes necessary changes to maintain consistency between UI, API, email, or other methods
  • Security reports checked/validated by a reviewer from the AppSec team

Related to #328519 (closed)

Edited by Heinrich Lee Yu

Merge request reports