RE: Permissions Model Inefficiency Killing DB

Do you think this is a consequence of permission complexity?

I have instinctively avoided individual permissions for purely management reasons and stuck to user membership of auth groups (roles) and given permissions exclusively to groups.

That postpones lookups to when they are needed.

I can't imagine how difficult it would be to unravel your scenario and impose group permissions.

Sorry I can't help with your actual question.

Cheers

Mike

--

(Unsigned mail from my phone)

-------- Original message --------

From: Ryan Skadberg <skadz@skadz.com>

Date: 25/11/21 07:35 (GMT+10:00)

To: Django users <django-users@googlegroups.com>

Subject: Permissions Model Inefficiency Killing DB

Hi All -

  Running Django 2.2.24 (Yes, I know, we are working on moving to 3.2)

  I've noticed some stalls in startup and finally have tracked it down.  It appears when permissions are imported, it checks EVERY user for permissions, NOT just the ones in the user_user_permissions table.  When you have 30k users in your DB this causes at least 60k SQL calls on startup.

They all look something like this:

2021-11-24 19:04:46.725 UTC [39] LOG:  duration: 0.344 ms  statement: SELECT "auth_group"."id", "auth_group"."name" FROM "auth_group" INNER JOIN "user_groups" ON ("auth_group"."id" = "user_groups"."group_id") WHERE "user_groups"."customuser_id" = 27345

2021-11-24 19:04:46.728 UTC [39] LOG:  duration: 0.379 ms  statement: SELECT "auth_permission"."id", "auth_permission"."name", "auth_permission"."content_type_id", "auth_permission"."codename" FROM "auth_permission" INNER JOIN "user_user_permissions" ON ("auth_permission"."id" = "user_user_permissions"."permission_id") INNER JOIN "django_content_type" ON ("auth_permission"."content_type_id" = "django_content_type"."id") WHERE "user_user_permissions"."customuser_id" = 27345 ORDER BY "django_content_type"."app_label" ASC, "django_content_type"."model" ASC, "auth_permission"."codename" ASC

 I have 677 rows in user_user_permissions with a minimum customuser_id of 0 and a max of 27346.  When I start up my tests, instead of looking at the 677 users that have permissions in the user_user_permissions table, it checks all 27346.  As there is nothing in the table for them, this is super super inefficient.

It appears that the SQL is doing something like:

select id from public.user

And really should be doing something like this to minimize SQL calls:

select id from public.user where id in (select customuser_id from user_user_permissions);

which in my case would be 1/30th of the calls, which would be HUGE for startup (60k to 2k or so).

Can anyone either explain why this is happening or a way to work around it or if I should file a bug?

Thanks!

Ryan

--
You received this message because you are subscribed to the Google Groups "Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to django-users+unsubscribe@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/0d5aa4db-ccd7-482a-8530-1cc8d76bbcc0n%40googlegroups.com.