From f65184b5e454437c3c524d1236041f170fff3ea4 Mon Sep 17 00:00:00 2001 From: Chris Lovering Date: Tue, 14 Dec 2021 18:59:49 +0000 Subject: Query message count directly from messages This was changed due to performance reasons, but after some tweaking in the database, such as increasing work memory and adding an index, this query runs much faster now. To test this, I want to revert this change, so that we can stop the materialised view from refreshing, to see if the act of refreshing is what's causing this query to seem faster when runing against the database. --- pydis_site/apps/api/models/bot/metricity.py | 8 +++++--- 1 file changed, 5 insertions(+), 3 deletions(-) (limited to 'pydis_site/apps/api') diff --git a/pydis_site/apps/api/models/bot/metricity.py b/pydis_site/apps/api/models/bot/metricity.py index 52e946ac..901f191a 100644 --- a/pydis_site/apps/api/models/bot/metricity.py +++ b/pydis_site/apps/api/models/bot/metricity.py @@ -46,12 +46,14 @@ class Metricity: self.cursor.execute( """ SELECT - message_count - FROM user_has_approx_message_count + COUNT(*) + FROM messages WHERE author_id = '%s' + AND NOT is_deleted + AND channel_id NOT IN %s """, - [user_id] + [user_id, EXCLUDE_CHANNELS] ) values = self.cursor.fetchone() -- cgit v1.2.3 From 59e4a5c8316464e116630a329064decac4c2a075 Mon Sep 17 00:00:00 2001 From: Chris Lovering Date: Thu, 16 Dec 2021 22:29:11 +0000 Subject: Always include metricity message blocks Thanks to a recent database maintenance (https://pythondiscord.freshstatus.io/incident/139811) querying out metricity message data is far cheaper. So there is no longer a reason to only fetch blocks if the member has a low message count. --- pydis_site/apps/api/tests/test_users.py | 21 +-------------------- pydis_site/apps/api/viewsets/bot/user.py | 6 +----- 2 files changed, 2 insertions(+), 25 deletions(-) (limited to 'pydis_site/apps/api') diff --git a/pydis_site/apps/api/tests/test_users.py b/pydis_site/apps/api/tests/test_users.py index 81bfd43b..9b91380b 100644 --- a/pydis_site/apps/api/tests/test_users.py +++ b/pydis_site/apps/api/tests/test_users.py @@ -408,7 +408,7 @@ class UserMetricityTests(AuthenticatedAPITestCase): in_guild=True, ) - def test_get_metricity_data_under_1k(self): + def test_get_metricity_data(self): # Given joined_at = "foo" total_messages = 1 @@ -428,25 +428,6 @@ class UserMetricityTests(AuthenticatedAPITestCase): "activity_blocks": total_blocks }) - def test_get_metricity_data_over_1k(self): - # Given - joined_at = "foo" - total_messages = 1001 - total_blocks = 1001 - self.mock_metricity_user(joined_at, total_messages, total_blocks, []) - - # When - url = reverse('api:bot:user-metricity-data', args=[0]) - response = self.client.get(url) - - # Then - self.assertEqual(response.status_code, 200) - self.assertCountEqual(response.json(), { - "joined_at": joined_at, - "total_messages": total_messages, - "voice_banned": False, - }) - def test_no_metricity_user(self): # Given self.mock_no_metricity_user() diff --git a/pydis_site/apps/api/viewsets/bot/user.py b/pydis_site/apps/api/viewsets/bot/user.py index ed661323..1a5e79f8 100644 --- a/pydis_site/apps/api/viewsets/bot/user.py +++ b/pydis_site/apps/api/viewsets/bot/user.py @@ -273,11 +273,7 @@ class UserViewSet(ModelViewSet): data = metricity.user(user.id) data["total_messages"] = metricity.total_messages(user.id) - if data["total_messages"] < 1000: - # Only calculate and return activity_blocks if the user has a small amount - # of messages, as calculating activity_blocks is expensive. - # 1000 message chosen as an arbitrarily large number. - data["activity_blocks"] = metricity.total_message_blocks(user.id) + data["activity_blocks"] = metricity.total_message_blocks(user.id) data["voice_banned"] = voice_banned return Response(data, status=status.HTTP_200_OK) -- cgit v1.2.3