[bug] Sending a notification generates many SQL queries

[nemesifier]

Describe the bug
I was manually testing openwisp/openwisp-controller#1069 when I read the query generated by notify.send, I wasn't expecting this:

(0.000) SELECT DISTINCT "openwisp_users_user"."password", "openwisp_users_user"."last_login", "openwisp_users_user"."is_superuser", "openwisp_users_user"."username", "openwisp_users_user"."first_name", "openwisp_users_user"."last_name", "openwisp_users_user"."is_staff", "openwisp_users_user"."is_active", "openwisp_users_user"."date_joined", "openwisp_users_user"."id", "openwisp_users_user"."email", "openwisp_users_user"."bio", "openwisp_users_user"."url", "openwisp_users_user"."company", "openwisp_users_user"."location", "openwisp_users_user"."phone_number", "openwisp_users_user"."birth_date", "openwisp_users_user"."notes", "openwisp_users_user"."language", "openwisp_users_user"."password_updated" FROM "openwisp_users_user" WHERE ("openwisp_users_user"."is_superuser" AND "openwisp_users_user"."is_active" AND NOT (EXISTS(SELECT 1 AS "a" FROM "openwisp_notifications_ignoreobjectnotification" U1 WHERE (U1."object_content_type_id" = 21 AND U1."user_id" = ("openwisp_users_user"."id")) LIMIT 1) AND EXISTS(SELECT 1 AS "a" FROM "openwisp_notifications_ignoreobjectnotification" U1 WHERE (U1."object_id" = '0adc5607-4c85-47db-89a7-e4973f315a43' AND U1."user_id" = ("openwisp_users_user"."id")) LIMIT 1) AND (EXISTS(SELECT 1 AS "a" FROM "openwisp_users_user" U0 LEFT OUTER JOIN "openwisp_notifications_ignoreobjectnotification" U1 ON (U0."id" = U1."user_id") WHERE (U1."valid_till" IS NULL AND U0."id" = ("openwisp_users_user"."id")) LIMIT 1) OR EXISTS(SELECT 1 AS "a" FROM "openwisp_notifications_ignoreobjectnotification" U1 WHERE (U1."valid_till" > '2025-07-03 17:09:55.746189' AND U1."user_id" = ("openwisp_users_user"."id")) LIMIT 1)))) ORDER BY "openwisp_users_user"."date_joined" ASC; args=(1, 21, 1, '0adc5607-4c85-47db-89a7-e4973f315a43', 1, 1, '2025-07-03 17:09:55.746189'); alias=default
(0.000) SELECT "openwisp_notifications_notificationsetting"."id", "openwisp_notifications_notificationsetting"."user_id", "openwisp_notifications_notificationsetting"."type", "openwisp_notifications_notificationsetting"."organization_id", "openwisp_notifications_notificationsetting"."web", "openwisp_notifications_notificationsetting"."email", "openwisp_notifications_notificationsetting"."deleted" FROM "openwisp_notifications_notificationsetting" LEFT OUTER JOIN "openwisp_users_organization" ON ("openwisp_notifications_notificationsetting"."organization_id" = "openwisp_users_organization"."id") WHERE "openwisp_notifications_notificationsetting"."user_id" IN ('f717c7c5318041738516ad25f34a7b2d', '8ad332d08cde487e9c8a02fc349f541d') ORDER BY "openwisp_users_organization"."name" ASC, "openwisp_notifications_notificationsetting"."type" ASC; args=('f717c7c5318041738516ad25f34a7b2d', '8ad332d08cde487e9c8a02fc349f541d'); alias=default
(0.000) SELECT "openwisp_notifications_ignoreobjectnotification"."id", "openwisp_notifications_ignoreobjectnotification"."user_id", "openwisp_notifications_ignoreobjectnotification"."object_content_type_id", "openwisp_notifications_ignoreobjectnotification"."object_id", "openwisp_notifications_ignoreobjectnotification"."valid_till" FROM "openwisp_notifications_ignoreobjectnotification" WHERE "openwisp_notifications_ignoreobjectnotification"."user_id" IN ('f717c7c5318041738516ad25f34a7b2d', '8ad332d08cde487e9c8a02fc349f541d') ORDER BY "openwisp_notifications_ignoreobjectnotification"."valid_till" ASC; args=('f717c7c5318041738516ad25f34a7b2d', '8ad332d08cde487e9c8a02fc349f541d'); alias=default
(0.028) INSERT INTO "openwisp_notifications_notification" ("id", "level", "recipient_id", "unread", "actor_content_type_id", "actor_object_id", "verb", "description", "target_content_type_id", "target_object_id", "action_object_content_type_id", "action_object_object_id", "timestamp", "public", "deleted", "emailed", "data", "type") VALUES ('395f65bba8f542518f2eeb27bcf2ac47', 'info', 'f717c7c5318041738516ad25f34a7b2d', 1, 21, '0adc5607-4c85-47db-89a7-e4973f315a43', 'generic verb', NULL, 21, '0adc5607-4c85-47db-89a7-e4973f315a43', NULL, NULL, '2025-07-03 17:09:55.745963', 1, 0, 0, '{"action": "join", "message": "The join operation on wg0 has been completed successfully."}', 'generic_message'); args=('395f65bba8f542518f2eeb27bcf2ac47', 'info', 'f717c7c5318041738516ad25f34a7b2d', True, 21, '0adc5607-4c85-47db-89a7-e4973f315a43', 'generic verb', None, 21, '0adc5607-4c85-47db-89a7-e4973f315a43', None, None, '2025-07-03 17:09:55.745963', True, False, False, '{"action": "join", "message": "The join operation on wg0 has been completed successfully."}', 'generic_message'); alias=default
(0.001) SELECT "config_vpn"."id", "config_vpn"."created", "config_vpn"."modified", "config_vpn"."name", "config_vpn"."config", "config_vpn"."organization_id", "config_vpn"."host", "config_vpn"."ca_id", "config_vpn"."cert_id", "config_vpn"."key", "config_vpn"."backend", "config_vpn"."notes", "config_vpn"."subnet_id", "config_vpn"."ip_id", "config_vpn"."webhook_endpoint", "config_vpn"."auth_token", "config_vpn"."dh", "config_vpn"."public_key", "config_vpn"."private_key", "config_vpn"."node_id", "config_vpn"."network_id" FROM "config_vpn" WHERE "config_vpn"."id" = '0adc56074c8547db89a7e4973f315a43' LIMIT 21; args=('0adc56074c8547db89a7e4973f315a43',); alias=default
(0.000) SELECT 1 AS "a" FROM "account_emailaddress" WHERE ("account_emailaddress"."user_id" = 'f717c7c5318041738516ad25f34a7b2d' AND "account_emailaddress"."email" = '' AND "account_emailaddress"."verified") LIMIT 1; args=(1, 'f717c7c5318041738516ad25f34a7b2d', ''); alias=default
(0.000) SELECT COUNT(*) AS "__count" FROM "openwisp_notifications_notification" WHERE ("openwisp_notifications_notification"."recipient_id" = 'f717c7c5318041738516ad25f34a7b2d' AND "openwisp_notifications_notification"."timestamp" >= '2025-07-03 17:09:45.822756'); args=('f717c7c5318041738516ad25f34a7b2d', '2025-07-03 17:09:45.822756'); alias=default
(0.000) SELECT COUNT(*) AS "__count" FROM "openwisp_notifications_notification" WHERE ("openwisp_notifications_notification"."recipient_id" = 'f717c7c5318041738516ad25f34a7b2d' AND "openwisp_notifications_notification"."timestamp" >= '2025-07-03 17:06:55.827111'); args=('f717c7c5318041738516ad25f34a7b2d', '2025-07-03 17:06:55.827111'); alias=default
(0.000) SELECT COUNT(*) AS "__count" FROM "openwisp_notifications_notification" WHERE ("openwisp_notifications_notification"."recipient_id" = 'f717c7c5318041738516ad25f34a7b2d' AND "openwisp_notifications_notification"."unread"); args=('f717c7c5318041738516ad25f34a7b2d',); alias=default
(0.014) INSERT INTO "openwisp_notifications_notification" ("id", "level", "recipient_id", "unread", "actor_content_type_id", "actor_object_id", "verb", "description", "target_content_type_id", "target_object_id", "action_object_content_type_id", "action_object_object_id", "timestamp", "public", "deleted", "emailed", "data", "type") VALUES ('ba576c330b734e40ad836208092dcb82', 'info', '8ad332d08cde487e9c8a02fc349f541d', 1, 21, '0adc5607-4c85-47db-89a7-e4973f315a43', 'generic verb', NULL, 21, '0adc5607-4c85-47db-89a7-e4973f315a43', NULL, NULL, '2025-07-03 17:09:55.745963', 1, 0, 0, '{"action": "join", "message": "The join operation on wg0 has been completed successfully."}', 'generic_message'); args=('ba576c330b734e40ad836208092dcb82', 'info', '8ad332d08cde487e9c8a02fc349f541d', True, 21, '0adc5607-4c85-47db-89a7-e4973f315a43', 'generic verb', None, 21, '0adc5607-4c85-47db-89a7-e4973f315a43', None, None, '2025-07-03 17:09:55.745963', True, False, False, '{"action": "join", "message": "The join operation on wg0 has been completed successfully."}', 'generic_message'); alias=default
(0.000) SELECT "config_vpn"."id", "config_vpn"."created", "config_vpn"."modified", "config_vpn"."name", "config_vpn"."config", "config_vpn"."organization_id", "config_vpn"."host", "config_vpn"."ca_id", "config_vpn"."cert_id", "config_vpn"."key", "config_vpn"."backend", "config_vpn"."notes", "config_vpn"."subnet_id", "config_vpn"."ip_id", "config_vpn"."webhook_endpoint", "config_vpn"."auth_token", "config_vpn"."dh", "config_vpn"."public_key", "config_vpn"."private_key", "config_vpn"."node_id", "config_vpn"."network_id" FROM "config_vpn" WHERE "config_vpn"."id" = '0adc56074c8547db89a7e4973f315a43' LIMIT 21; args=('0adc56074c8547db89a7e4973f315a43',); alias=default
(0.000) SELECT 1 AS "a" FROM "account_emailaddress" WHERE ("account_emailaddress"."user_id" = '8ad332d08cde487e9c8a02fc349f541d' AND "account_emailaddress"."email" = 'superuser@superuser.com' AND "account_emailaddress"."verified") LIMIT 1; args=(1, '8ad332d08cde487e9c8a02fc349f541d', 'superuser@superuser.com'); alias=default
(0.000) SELECT COUNT(*) AS "__count" FROM "openwisp_notifications_notification" WHERE ("openwisp_notifications_notification"."recipient_id" = '8ad332d08cde487e9c8a02fc349f541d' AND "openwisp_notifications_notification"."timestamp" >= '2025-07-03 17:09:45.861997'); args=('8ad332d08cde487e9c8a02fc349f541d', '2025-07-03 17:09:45.861997'); alias=default
(0.000) SELECT COUNT(*) AS "__count" FROM "openwisp_notifications_notification" WHERE ("openwisp_notifications_notification"."recipient_id" = '8ad332d08cde487e9c8a02fc349f541d' AND "openwisp_notifications_notification"."timestamp" >= '2025-07-03 17:06:55.863234'); args=('8ad332d08cde487e9c8a02fc349f541d', '2025-07-03 17:06:55.863234'); alias=default
(0.000) SELECT COUNT(*) AS "__count" FROM "openwisp_notifications_notification" WHERE ("openwisp_notifications_notification"."recipient_id" = '8ad332d08cde487e9c8a02fc349f541d' AND "openwisp_notifications_notification"."unread"); args=('8ad332d08cde487e9c8a02fc349f541d',); alias=default

Steps To Reproduce

Call ./manage test shell_plus in the test project and paste this code:

from openwisp_notifications.signals import notify
from django.utils.translation import gettext as _

vpn = Vpn.objects.first()

def test_notification(type="error"):
    status_code = 500
    action = 'join'
    message_map = {
        "error": {
            "verb": _("encountered an unrecoverable error"),
            "message": _(
                "Unable to perform {action} operation on the "
                "{target} VPN server due to an "
                "unrecoverable error "
                "(status code: {status_code})"
            ),
            "level": "error",
        },
        "recovery": {
            "verb": _("has been completed successfully"),
            "message": _("The {action} operation on {target} {verb}."),
            "level": "info",
        },
    }
    meta = message_map[type]
    notify.send(
        type="generic_message",
        sender=vpn,
        target=vpn,
        action=action,
        verb=meta["verb"],
        message=meta["message"].format(
            action=action,
            target=str(vpn),
            status_code=status_code,
            verb=meta["verb"],
        ),
        level=meta["level"],
    )

test_notification()

Are we sure all the queries are necessary?

[pandafy]

We can check what is executing this query and find a way to reduce them.

SELECT COUNT(*) AS "__count" FROM "openwisp_notifications_notification" WHERE ("openwisp_notifications_notification"."recipient_id" = 'f717c7c5318041738516ad25f34a7b2d' AND "openwisp_notifications_notification"."timestamp" >= '2025-07-03 17:09:45.822756')

FYI, these queries are created for two users f717c7c5318041738516ad25f34a7b2d and 8ad332d08cde487e9c8a02fc349f541d