Skip to content

Instantly share code, notes, and snippets.

@AlanCoding
Created August 10, 2022 13:43
Show Gist options
  • Save AlanCoding/a4ed6f3847bb5aa2cee4695c188a7666 to your computer and use it in GitHub Desktop.
Save AlanCoding/a4ed6f3847bb5aa2cee4695c188a7666 to your computer and use it in GitHub Desktop.

Setup

  • run chatty tasks playbook
  • get a query the UI uses when streaming the job output
    • /api/v2/jobs/586/job_events/?not__stdout=&order_by=counter&page=1&page_size=50
  • Turn on Django debug toolbar
  • look at queries

With that scenario, we run different scenarios, and each scenario has 2 queries. The first query gets the count and the second query gets the objects.

Disclosure: we have a prefetch bug which makes queries hard to read, so this patch is applied

diff --git a/awx/api/views/__init__.py b/awx/api/views/__init__.py
index 9f0b8fb885..6c4536e789 100644
--- a/awx/api/views/__init__.py
+++ b/awx/api/views/__init__.py
@@ -3840,7 +3840,7 @@ class JobJobEventsList(BaseJobEventsList):
     def get_queryset(self):
         job = self.get_parent_object()
         self.check_parent_access(job)
-        return job.get_event_queryset().select_related('host').order_by('start_line')
+        return job.get_event_queryset().prefetch_related('job__job_template', 'host').order_by('start_line')
 
 
 class JobJobEventsChildrenSummary(APIView):

Normal

The count query:

SELECT Count(*)
FROM   (SELECT DISTINCT "main_jobevent"."id"          AS Col1,
                        "main_jobevent"."event"       AS Col2,
                        "main_jobevent"."event_data"  AS Col3,
                        "main_jobevent"."failed"      AS Col4,
                        "main_jobevent"."changed"     AS Col5,
                        "main_jobevent"."uuid"        AS Col6,
                        "main_jobevent"."playbook"    AS Col7,
                        "main_jobevent"."play"        AS Col8,
                        "main_jobevent"."role"        AS Col9,
                        "main_jobevent"."task"        AS Col10,
                        "main_jobevent"."counter"     AS Col11,
                        "main_jobevent"."stdout"      AS Col12,
                        "main_jobevent"."verbosity"   AS Col13,
                        "main_jobevent"."start_line"  AS Col14,
                        "main_jobevent"."end_line"    AS Col15,
                        "main_jobevent"."created"     AS Col16,
                        "main_jobevent"."modified"    AS Col17,
                        "main_jobevent"."job_id"      AS Col18,
                        "main_jobevent"."host_id"     AS Col19,
                        "main_jobevent"."host_name"   AS Col20,
                        "main_jobevent"."parent_uuid" AS Col21
        FROM   "main_jobevent"
        WHERE  ( "main_jobevent"."job_created" = '2022-08-10T13:15:24.484411+00:00' :: timestamptz
                 AND "main_jobevent"."job_id" = 586
                 AND NOT ( "main_jobevent"."stdout" = '' ) )) subquery 

objects query:

SELECT DISTINCT "main_jobevent"."id",
                "main_jobevent"."event",
                "main_jobevent"."event_data",
                "main_jobevent"."failed",
                "main_jobevent"."changed",
                "main_jobevent"."uuid",
                "main_jobevent"."playbook",
                "main_jobevent"."play",
                "main_jobevent"."role",
                "main_jobevent"."task",
                "main_jobevent"."counter",
                "main_jobevent"."stdout",
                "main_jobevent"."verbosity",
                "main_jobevent"."start_line",
                "main_jobevent"."end_line",
                "main_jobevent"."created",
                "main_jobevent"."modified",
                "main_jobevent"."job_id",
                "main_jobevent"."host_id",
                "main_jobevent"."host_name",
                "main_jobevent"."parent_uuid"
FROM   "main_jobevent"
WHERE  ( "main_jobevent"."job_created" = '2022-08-10T13:15:24.484411+00:00' :: timestamptz
         AND "main_jobevent"."job_id" = 586
         AND NOT ( "main_jobevent"."stdout" = '' ) )
ORDER  BY "main_jobevent"."counter" ASC
LIMIT  50 

With distinct removed

This makes this change

diff --git a/awx/api/filters.py b/awx/api/filters.py
index 90f499d671..b5545de78b 100644
--- a/awx/api/filters.py
+++ b/awx/api/filters.py
@@ -157,7 +157,7 @@ class FieldLookupBackend(BaseFilterBackend):
 
     # A list of fields that we know can be filtered on without the possiblity
     # of introducing duplicates
-    NO_DUPLICATES_ALLOW_LIST = (CharField, IntegerField, BooleanField)
+    NO_DUPLICATES_ALLOW_LIST = (CharField, IntegerField, BooleanField, TextField)
 
     def get_fields_from_lookup(self, model, lookup):

then the count

SELECT Count(*) AS "__count"
FROM   "main_jobevent"
WHERE  ( "main_jobevent"."job_created" = '2022-08-10T13:15:24.484411+00:00' :: timestamptz
        AND "main_jobevent"."job_id" = 586
        AND NOT ( "main_jobevent"."stdout" = '' ) ) 

now the objects

SELECT "main_jobevent"."id",
       "main_jobevent"."event",
       "main_jobevent"."event_data",
       "main_jobevent"."failed",
       "main_jobevent"."changed",
       "main_jobevent"."uuid",
       "main_jobevent"."playbook",
       "main_jobevent"."play",
       "main_jobevent"."role",
       "main_jobevent"."task",
       "main_jobevent"."counter",
       "main_jobevent"."stdout",
       "main_jobevent"."verbosity",
       "main_jobevent"."start_line",
       "main_jobevent"."end_line",
       "main_jobevent"."created",
       "main_jobevent"."modified",
       "main_jobevent"."job_id",
       "main_jobevent"."host_id",
       "main_jobevent"."host_name",
       "main_jobevent"."parent_uuid"
  FROM "main_jobevent"
 WHERE ("main_jobevent"."job_created" = '2022-08-10T13:15:24.484411+00:00'::timestamptz AND "main_jobevent"."job_id" = 586 AND NOT ("main_jobevent"."stdout" = ''))
 ORDER BY "main_jobevent"."counter" ASC
 LIMIT 50
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment