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) |
---|---|
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
-
📋 Does this MR need a changelog?-
I have included a changelog entry. -
I have not included a changelog entry because _____.
-
-
Documentation (if required) -
Code review guidelines -
Merge request performance guidelines -
Style guides -
Database guides -
Separation of EE specific content
Availability and Testing
-
Review and add/update tests for this feature/bug. Consider all test levels. See the Test Planning Process. -
Tested in all supported browsers -
Informed Infrastructure department of a default or new setting change, if applicable per definition of done
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