We started investigation on our database as it is the less scalable component in our infrastructure. I checked the table pg_stat_statements of our Postgresql database with the following query:
SELECT userid, calls, total_time, rows, 100.0 * shared_blks_hit /
nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent, query
FROM pg_stat_statements ORDER BY total_time DESC LIMIT 5;
Everytime, the same query is first in the list:
16386 | 21564 | 4077324.749363 | 1423094 | 99.9960264252721535 |
SELECT DISTINCT "auth_user"."id", "auth_user"."password", "auth_user"."last_login",
"auth_user"."is_superuser", "auth_user"."username", "auth_user"."first_name",
"auth_user"."last_name", "auth_user"."email", "auth_user"."is_staff",
"auth_user"."is_active", "auth_user"."date_joined" FROM "auth_user"
LEFT OUTER JOIN "auth_user_groups" ON ("auth_user"."id" = "auth_user_groups"."user_id")
LEFT OUTER JOIN "auth_group" ON ("auth_user_groups"."group_id" = "auth_group"."id")
LEFT OUTER JOIN "auth_group_permissions" ON ("auth_group"."id" = "auth_group_permissions"."group_id")
LEFT OUTER JOIN "auth_user_user_permissions" ON ("auth_user"."id" = "auth_user_user_permissions"."user_id")
WHERE ("auth_group_permissions"."permission_id" = $1 OR "auth_user_user_permissions"."permission_id" = $2)
This sounds like a permission check and as I understand, it is cached at request level. I wonder if someone did a package to cache them into memcached for instance, or found a solution to reduce the amount of requests done to check those permissions?
I checked all indices and they seem correct. The request is a bit slow mostly because we have a lot of permissions but still, the amount of calls is crazy.