Alter message query to leverage index

Previously this query would convert each row to an array just to check if it matched or not. By changing EXCLUDE_CHANNELS to a tuple instead of a list, it doesn't get passed as an array, so we can do a simple NOT IN check.

This will also allow us to add an index with this condition to speed it up further.

1 files changed, 7 insertions, 7 deletions

diff --git a/pydis_site/apps/api/models/bot/metricity.py b/pydis_site/apps/api/models/bot/metricity.py
index 33fb7ad7..901f191a 100644
--- a/pydis_site/apps/api/models/bot/metricity.py
+++ b/pydis_site/apps/api/models/bot/metricity.py
@@ -4,10 +4,10 @@ from django.db import connections
 
 BLOCK_INTERVAL = 10 * 60  # 10 minute blocks
 
-EXCLUDE_CHANNELS = [
+EXCLUDE_CHANNELS = (
     "267659945086812160",  # Bot commands
     "607247579608121354"  # SeasonalBot commands
-]
+)
 
 
 class NotFoundError(Exception):
@@ -46,12 +46,12 @@ class Metricity:
         self.cursor.execute(
             """
             SELECT
-              COUNT(*)
+                COUNT(*)
             FROM messages
             WHERE
-              author_id = '%s'
-              AND NOT is_deleted
-              AND NOT %s::varchar[] @> ARRAY[channel_id]
+                author_id = '%s'
+                AND NOT is_deleted
+                AND channel_id NOT IN %s
             """,
             [user_id, EXCLUDE_CHANNELS]
         )
@@ -79,7 +79,7 @@ class Metricity:
                 WHERE
                     author_id='%s'
                     AND NOT is_deleted
-                    AND NOT %s::varchar[] @> ARRAY[channel_id]
+                    AND channel_id NOT IN %s
                 GROUP BY interval
             ) block_query;
             """,