1869: Course program api performance improvements #1872

collinpreston · 2023-09-10T14:01:24Z

What are the relevant tickets?

#1869

Description (What does it do?)

Improves the performance of courses REST APIs with the following changes:

Remove the course page from the CourseRunSerializer since the Course serializer already contains the course page and is also defined in the CourseRunSerializer. PR updates the front end to utilize this change.
Select_related and prefetch_related where possible on the Programs, Courses, and CourseRun viewsets.
Use iterator() when a filter query exists within a loop.
Set the live attribute as an index field on the Program and Course models.
Add REST API filtering to the Course and Program viewsets for id, live, and readable_id.
Create a DepartmentSerializer to more easily handle departments related to courses and programs. Department serialization was previously being handled the same way across many serializers using serializer methods.
Cache properties where possible.

How can this be tested?

Attempt making API calls to the REST endpoints listed in the Testing Results section. Verify that you achieve a lower response time than the current main branch, and that all data expected is provided in the results.
Ensure that front-end pages function as they previously did.

Additional Context

Testing results

Testing was performed via GET requests using Postman. The database records used for testing may not be extensive and do not contain all possible related records, such as a course, product, flexible price record, and flexible price request.

Recorded times indicate the lowest response time achieved for each set of GET requests for REST endpoint. The set of GET requests was roughly 5-10 requests performed immediately after each other.

`/courses` (32 records)

-- Before -------
9.24s

-- Now -------
516ms

`/courses?id=4&live=true` (32 records)

-- Before ------- (filtering by readable_id)
78ms

mitxonline-web-1      | [2023-09-10 14:13:41] DEBUG 2339 [django.db.backends] utils.py:123 - [c18a6235ffb0] - (0.002) SELECT "courses_course"."id", "courses_course"."created_on", "courses_course"."updated_on", "courses_course"."title", "courses_course"."readable_id", "courses_course"."live" FROM "courses_course" WHERE ("courses_course"."live" AND "courses_course"."readable_id" = 'course1') ORDER BY "courses_course"."id" ASC; args=('course1',)
mitxonline-web-1      | [2023-09-10 14:13:41] ERROR 2339 [nplusone] notifiers.py:40 - [c18a6235ffb0] - Potential n+1 query detected on `Course.courseruns`
mitxonline-web-1      | [2023-09-10 14:13:41] ERROR 2339 [nplusone] notifiers.py:40 - [c18a6235ffb0] - Potential n+1 query detected on `Course.courseruns`
mitxonline-web-1      | [2023-09-10 14:13:41] DEBUG 2339 [django.db.backends] utils.py:123 - [c18a6235ffb0] - (0.001) SELECT "courses_courserun"."id", "courses_courserun"."created_on", "courses_courserun"."updated_on", "courses_courserun"."course_id", "courses_courserun"."title", "courses_courserun"."courseware_id", "courses_courserun"."run_tag", "courses_courserun"."courseware_url_path", "courses_courserun"."start_date", "courses_courserun"."end_date", "courses_courserun"."certificate_available_date", "courses_courserun"."enrollment_start", "courses_courserun"."enrollment_end", "courses_courserun"."expiration_date", "courses_courserun"."upgrade_deadline", "courses_courserun"."live", "courses_courserun"."is_self_paced" FROM "courses_courserun" WHERE "courses_courserun"."course_id" = 3 ORDER BY "courses_courserun"."id" ASC; args=(3,)
mitxonline-web-1      | [2023-09-10 14:13:41] ERROR 2339 [nplusone] notifiers.py:40 - [c18a6235ffb0] - Potential n+1 query detected on `Course.courseruns`
mitxonline-web-1      | [2023-09-10 14:13:41] ERROR 2339 [nplusone] notifiers.py:40 - [c18a6235ffb0] - Potential n+1 query detected on `Course.courseruns`
mitxonline-web-1      | [2023-09-10 14:13:41] DEBUG 2339 [django.db.backends] utils.py:123 - [c18a6235ffb0] - (0.001) SELECT "courses_courserun"."id", "courses_courserun"."created_on", "courses_courserun"."updated_on", "courses_courserun"."course_id", "courses_courserun"."title", "courses_courserun"."courseware_id", "courses_courserun"."run_tag", "courses_courserun"."courseware_url_path", "courses_courserun"."start_date", "courses_courserun"."end_date", "courses_courserun"."certificate_available_date", "courses_courserun"."enrollment_start", "courses_courserun"."enrollment_end", "courses_courserun"."expiration_date", "courses_courserun"."upgrade_deadline", "courses_courserun"."live", "courses_courserun"."is_self_paced" FROM "courses_courserun" WHERE "courses_courserun"."course_id" = 3; args=(3,)
mitxonline-web-1      | [2023-09-10 14:13:41] ERROR 2339 [nplusone] notifiers.py:40 - [c18a6235ffb0] - Potential n+1 query detected on `Course.department_set`
mitxonline-web-1      | [2023-09-10 14:13:41] ERROR 2339 [nplusone] notifiers.py:40 - [c18a6235ffb0] - Potential n+1 query detected on `Course.department_set`
mitxonline-web-1      | [2023-09-10 14:13:41] DEBUG 2339 [django.db.backends] utils.py:123 - [c18a6235ffb0] - (0.001) SELECT "courses_department"."id", "courses_department"."created_on", "courses_department"."updated_on", "courses_department"."name" FROM "courses_department" INNER JOIN "courses_course_departments" ON ("courses_department"."id" = "courses_course_departments"."department_id") WHERE "courses_course_departments"."course_id" = 3; args=(3,)
mitxonline-web-1      | [2023-09-10 14:13:41] DEBUG 2339 [django.db.backends] utils.py:123 - [c18a6235ffb0] - (0.001) SELECT (1) AS "a" FROM "cms_coursepage" WHERE "cms_coursepage"."course_id" = 3 LIMIT 1; args=(3,)
mitxonline-web-1      | [2023-09-10 14:13:41] DEBUG 2339 [django.db.backends] utils.py:123 - [c18a6235ffb0] - (0.001) SELECT DISTINCT ON ("courses_programrequirement"."program_id") "courses_programrequirement"."program_id" FROM "courses_programrequirement" WHERE ("courses_programrequirement"."course_id" = 3 AND "courses_programrequirement"."node_type" = 'course') ORDER BY "courses_programrequirement"."program_id" ASC; args=(3, <ProgramRequirementNodeType.COURSE: 'course'>)
mitxonline-web-1      | [2023-09-10 14:13:41] DEBUG 2339 [django.db.backends] utils.py:123 - [c18a6235ffb0] - (0.002) SELECT "courses_program"."id", "courses_program"."created_on", "courses_program"."updated_on", "courses_program"."title", "courses_program"."readable_id", "courses_program"."live", "courses_program"."program_type" FROM "courses_program" WHERE "courses_program"."id" IN (1) ORDER BY "courses_program"."id" ASC; args=(1,)

-- Now -------
42ms

mitxonline-web-1      | [2023-09-10 13:52:56] DEBUG 1288 [django.db.backends] utils.py:123 - [c18a6235ffb0] - (0.006) SELECT "courses_course"."id", "courses_course"."created_on", "courses_course"."updated_on", "courses_course"."title", "courses_course"."readable_id", "courses_course"."live", "wagtailcore_page"."id", "wagtailcore_page"."path", "wagtailcore_page"."depth", "wagtailcore_page"."numchild", "wagtailcore_page"."translation_key", "wagtailcore_page"."locale_id", "wagtailcore_page"."latest_revision_id", "wagtailcore_page"."live", "wagtailcore_page"."has_unpublished_changes", "wagtailcore_page"."first_published_at", "wagtailcore_page"."last_published_at", "wagtailcore_page"."live_revision_id", "wagtailcore_page"."go_live_at", "wagtailcore_page"."expire_at", "wagtailcore_page"."expired", "wagtailcore_page"."locked", "wagtailcore_page"."locked_at", "wagtailcore_page"."locked_by_id", "wagtailcore_page"."title", "wagtailcore_page"."draft_title", "wagtailcore_page"."slug", "wagtailcore_page"."content_type_id", "wagtailcore_page"."url_path", "wagtailcore_page"."owner_id", "wagtailcore_page"."seo_title", "wagtailcore_page"."show_in_menus", "wagtailcore_page"."search_description", "wagtailcore_page"."latest_revision_created_at", "wagtailcore_page"."alias_of_id", "cms_coursepage"."page_ptr_id", "cms_coursepage"."description", "cms_coursepage"."length", "cms_coursepage"."effort", "cms_coursepage"."price", "cms_coursepage"."prerequisites", "cms_coursepage"."about", "cms_coursepage"."faq_url", "cms_coursepage"."video_url", "cms_coursepage"."what_you_learn", "cms_coursepage"."feature_image_id", "cms_coursepage"."faculty_section_title", "cms_coursepage"."course_id" FROM "courses_course" LEFT OUTER JOIN "cms_coursepage" ON ("courses_course"."id" = "cms_coursepage"."course_id") LEFT OUTER JOIN "wagtailcore_page" ON ("cms_coursepage"."page_ptr_id" = "wagtailcore_page"."id") WHERE ("courses_course"."id" = 1 AND "courses_course"."live") ORDER BY "courses_course"."id" ASC; args=(1,)
mitxonline-web-1      | [2023-09-10 13:52:56] DEBUG 1288 [django.db.backends] utils.py:123 - [c18a6235ffb0] - (0.002) SELECT "courses_courserun"."id", "courses_courserun"."created_on", "courses_courserun"."updated_on", "courses_courserun"."course_id", "courses_courserun"."title", "courses_courserun"."courseware_id", "courses_courserun"."run_tag", "courses_courserun"."courseware_url_path", "courses_courserun"."start_date", "courses_courserun"."end_date", "courses_courserun"."certificate_available_date", "courses_courserun"."enrollment_start", "courses_courserun"."enrollment_end", "courses_courserun"."expiration_date", "courses_courserun"."upgrade_deadline", "courses_courserun"."live", "courses_courserun"."is_self_paced" FROM "courses_courserun" WHERE "courses_courserun"."course_id" IN (1); args=(1,)
mitxonline-web-1      | [2023-09-10 13:52:56] DEBUG 1288 [django.db.backends] utils.py:123 - [c18a6235ffb0] - (0.001) SELECT ("courses_course_departments"."course_id") AS "_prefetch_related_val_course_id", "courses_department"."id", "courses_department"."created_on", "courses_department"."updated_on", "courses_department"."name" FROM "courses_department" INNER JOIN "courses_course_departments" ON ("courses_department"."id" = "courses_course_departments"."department_id") WHERE "courses_course_departments"."course_id" IN (1); args=(1,)
mitxonline-web-1      | [2023-09-10 13:52:56] DEBUG 1288 [django.db.backends] utils.py:123 - [c18a6235ffb0] - (0.001) SELECT "ecommerce_product"."id", "ecommerce_product"."created_on", "ecommerce_product"."updated_on", "ecommerce_product"."content_type_id", "ecommerce_product"."object_id", "ecommerce_product"."price", "ecommerce_product"."description", "ecommerce_product"."is_active" FROM "ecommerce_product" WHERE ("ecommerce_product"."is_active" AND "ecommerce_product"."content_type_id" = 60 AND "ecommerce_product"."object_id" = 1); args=(60, 1)
mitxonline-web-1      | [2023-09-10 13:52:56] DEBUG 1288 [django.db.backends] utils.py:123 - [c18a6235ffb0] - (0.002) SELECT DISTINCT ON ("courses_programrequirement"."program_id") "courses_programrequirement"."program_id" FROM "courses_programrequirement" WHERE ("courses_programrequirement"."course_id" = 1 AND "courses_programrequirement"."node_type" = 'course') ORDER BY "courses_programrequirement"."program_id" ASC; args=(1, <ProgramRequirementNodeType.COURSE: 'course'>)
mitxonline-web-1      | [2023-09-10 13:52:56] DEBUG 1288 [django.db.backends] utils.py:123 - [c18a6235ffb0] - (0.001) SELECT "wagtailcore_page"."id", "wagtailcore_page"."path", "wagtailcore_page"."depth", "wagtailcore_page"."numchild", "wagtailcore_page"."translation_key", "wagtailcore_page"."locale_id", "wagtailcore_page"."latest_revision_id", "wagtailcore_page"."live", "wagtailcore_page"."has_unpublished_changes", "wagtailcore_page"."first_published_at", "wagtailcore_page"."last_published_at", "wagtailcore_page"."live_revision_id", "wagtailcore_page"."go_live_at", "wagtailcore_page"."expire_at", "wagtailcore_page"."expired", "wagtailcore_page"."locked", "wagtailcore_page"."locked_at", "wagtailcore_page"."locked_by_id", "wagtailcore_page"."title", "wagtailcore_page"."draft_title", "wagtailcore_page"."slug", "wagtailcore_page"."content_type_id", "wagtailcore_page"."url_path", "wagtailcore_page"."owner_id", "wagtailcore_page"."seo_title", "wagtailcore_page"."show_in_menus", "wagtailcore_page"."search_description", "wagtailcore_page"."latest_revision_created_at", "wagtailcore_page"."alias_of_id", "cms_flexiblepricingrequestform"."page_ptr_id", "cms_flexiblepricingrequestform"."intro", "cms_flexiblepricingrequestform"."guest_text", "cms_flexiblepricingrequestform"."selected_course_id", "cms_flexiblepricingrequestform"."selected_program_id", "cms_flexiblepricingrequestform"."application_processing_text", "cms_flexiblepricingrequestform"."application_approved_text", "cms_flexiblepricingrequestform"."application_approved_no_discount_text", "cms_flexiblepricingrequestform"."application_denied_text" FROM "cms_flexiblepricingrequestform" INNER JOIN "wagtailcore_page" ON ("cms_flexiblepricingrequestform"."page_ptr_id" = "wagtailcore_page"."id") WHERE "cms_flexiblepricingrequestform"."selected_course_id" = 1 ORDER BY "wagtailcore_page"."path" ASC LIMIT 1; args=(1,)
mitxonline-web-1      | [2023-09-10 13:52:56] DEBUG 1288 [django.db.backends] utils.py:123 - [c18a6235ffb0] - (0.001) SELECT "cms_instructorpagelink"."id", "cms_instructorpagelink"."page_id", "cms_instructorpagelink"."linked_instructor_page_id", "cms_instructorpagelink"."order" FROM "cms_instructorpagelink" WHERE "cms_instructorpagelink"."page_id" = 15; args=(15,)

`/programs?live=true` (1 record)

http://mitxonline.odl.local:8013/api/programs?live=true

-- Before -------
113ms

mitxonline-web-1      | [2023-09-10 14:09:09] DEBUG 2339 [django.db.backends] utils.py:123 - [c18a6235ffb0] - (0.002) SELECT "courses_program"."id", "courses_program"."created_on", "courses_program"."updated_on", "courses_program"."title", "courses_program"."readable_id", "courses_program"."live", "courses_program"."program_type" FROM "courses_program" WHERE "courses_program"."live" ORDER BY "courses_program"."id" ASC; args=()
mitxonline-web-1      | [2023-09-10 14:09:09] DEBUG 2339 [django.db.backends] utils.py:123 - [c18a6235ffb0] - (0.002) SELECT "courses_programrequirement"."id", "courses_programrequirement"."path", "courses_programrequirement"."depth", "courses_programrequirement"."numchild", "courses_programrequirement"."node_type", "courses_programrequirement"."operator", "courses_programrequirement"."operator_value", "courses_programrequirement"."program_id", "courses_programrequirement"."course_id", "courses_programrequirement"."title", "courses_programrequirement"."elective_flag" FROM "courses_programrequirement" WHERE ("courses_programrequirement"."depth" = 2 AND "courses_programrequirement"."program_id" = 1) ORDER BY "courses_programrequirement"."path" ASC; args=(2, 1)
mitxonline-web-1      | [2023-09-10 14:09:09] DEBUG 2339 [django.db.backends] utils.py:123 - [c18a6235ffb0] - (0.004) SELECT "courses_programrequirement"."id", "courses_programrequirement"."path", "courses_programrequirement"."depth", "courses_programrequirement"."numchild", "courses_programrequirement"."node_type", "courses_programrequirement"."operator", "courses_programrequirement"."operator_value", "courses_programrequirement"."program_id", "courses_programrequirement"."course_id", "courses_programrequirement"."title", "courses_programrequirement"."elective_flag", "courses_course"."id", "courses_course"."created_on", "courses_course"."updated_on", "courses_course"."title", "courses_course"."readable_id", "courses_course"."live", "wagtailcore_page"."id", "wagtailcore_page"."path", "wagtailcore_page"."depth", "wagtailcore_page"."numchild", "wagtailcore_page"."translation_key", "wagtailcore_page"."locale_id", "wagtailcore_page"."latest_revision_id", "wagtailcore_page"."live", "wagtailcore_page"."has_unpublished_changes", "wagtailcore_page"."first_published_at", "wagtailcore_page"."last_published_at", "wagtailcore_page"."live_revision_id", "wagtailcore_page"."go_live_at", "wagtailcore_page"."expire_at", "wagtailcore_page"."expired", "wagtailcore_page"."locked", "wagtailcore_page"."locked_at", "wagtailcore_page"."locked_by_id", "wagtailcore_page"."title", "wagtailcore_page"."draft_title", "wagtailcore_page"."slug", "wagtailcore_page"."content_type_id", "wagtailcore_page"."url_path", "wagtailcore_page"."owner_id", "wagtailcore_page"."seo_title", "wagtailcore_page"."show_in_menus", "wagtailcore_page"."search_description", "wagtailcore_page"."latest_revision_created_at", "wagtailcore_page"."alias_of_id", "cms_coursepage"."page_ptr_id", "cms_coursepage"."description", "cms_coursepage"."length", "cms_coursepage"."effort", "cms_coursepage"."price", "cms_coursepage"."prerequisites", "cms_coursepage"."about", "cms_coursepage"."faq_url", "cms_coursepage"."video_url", "cms_coursepage"."what_you_learn", "cms_coursepage"."feature_image_id", "cms_coursepage"."faculty_section_title", "cms_coursepage"."course_id" FROM "courses_programrequirement" LEFT OUTER JOIN "courses_course" ON ("courses_programrequirement"."course_id" = "courses_course"."id") LEFT OUTER JOIN "cms_coursepage" ON ("courses_course"."id" = "cms_coursepage"."course_id") LEFT OUTER JOIN "wagtailcore_page" ON ("cms_coursepage"."page_ptr_id" = "wagtailcore_page"."id") WHERE ("courses_programrequirement"."node_type" = 'course' AND "courses_programrequirement"."path"::text LIKE '00010001%' AND "courses_programrequirement"."program_id" = 1) ORDER BY "courses_programrequirement"."path" ASC; args=(<ProgramRequirementNodeType.COURSE: 'course'>, '00010001%', 1)
mitxonline-web-1      | [2023-09-10 14:09:09] DEBUG 2339 [django.db.backends] utils.py:123 - [c18a6235ffb0] - (0.001) SELECT "courses_courserun"."id", "courses_courserun"."created_on", "courses_courserun"."updated_on", "courses_courserun"."course_id", "courses_courserun"."title", "courses_courserun"."courseware_id", "courses_courserun"."run_tag", "courses_courserun"."courseware_url_path", "courses_courserun"."start_date", "courses_courserun"."end_date", "courses_courserun"."certificate_available_date", "courses_courserun"."enrollment_start", "courses_courserun"."enrollment_end", "courses_courserun"."expiration_date", "courses_courserun"."upgrade_deadline", "courses_courserun"."live", "courses_courserun"."is_self_paced" FROM "courses_courserun" WHERE "courses_courserun"."course_id" = 3 ORDER BY "courses_courserun"."id" ASC; args=(3,)
mitxonline-web-1      | [2023-09-10 14:09:09] DEBUG 2339 [django.db.backends] utils.py:123 - [c18a6235ffb0] - (0.001) SELECT "courses_courserun"."id", "courses_courserun"."created_on", "courses_courserun"."updated_on", "courses_courserun"."course_id", "courses_courserun"."title", "courses_courserun"."courseware_id", "courses_courserun"."run_tag", "courses_courserun"."courseware_url_path", "courses_courserun"."start_date", "courses_courserun"."end_date", "courses_courserun"."certificate_available_date", "courses_courserun"."enrollment_start", "courses_courserun"."enrollment_end", "courses_courserun"."expiration_date", "courses_courserun"."upgrade_deadline", "courses_courserun"."live", "courses_courserun"."is_self_paced" FROM "courses_courserun" WHERE "courses_courserun"."course_id" = 3; args=(3,)
mitxonline-web-1      | [2023-09-10 14:09:09] DEBUG 2339 [django.db.backends] utils.py:123 - [c18a6235ffb0] - (0.001) SELECT "courses_department"."id", "courses_department"."created_on", "courses_department"."updated_on", "courses_department"."name" FROM "courses_department" INNER JOIN "courses_course_departments" ON ("courses_department"."id" = "courses_course_departments"."department_id") WHERE "courses_course_departments"."course_id" = 3; args=(3,)
mitxonline-web-1      | [2023-09-10 14:09:09] DEBUG 2339 [django.db.backends] utils.py:123 - [c18a6235ffb0] - (0.001) SELECT (1) AS "a" FROM "cms_coursepage" WHERE "cms_coursepage"."course_id" = 3 LIMIT 1; args=(3,)
mitxonline-web-1      | [2023-09-10 14:09:09] DEBUG 2339 [django.db.backends] utils.py:123 - [c18a6235ffb0] - (0.001) SELECT "courses_programrequirement"."id", "courses_programrequirement"."path", "courses_programrequirement"."depth", "courses_programrequirement"."numchild", "courses_programrequirement"."node_type", "courses_programrequirement"."operator", "courses_programrequirement"."operator_value", "courses_programrequirement"."program_id", "courses_programrequirement"."course_id", "courses_programrequirement"."title", "courses_programrequirement"."elective_flag" FROM "courses_programrequirement" WHERE ("courses_programrequirement"."depth" = 2 AND "courses_programrequirement"."program_id" = 1) ORDER BY "courses_programrequirement"."path" ASC; args=(2, 1)
mitxonline-web-1      | [2023-09-10 14:09:09] DEBUG 2339 [django.db.backends] utils.py:123 - [c18a6235ffb0] - (0.002) SELECT "courses_programrequirement"."id", "courses_programrequirement"."path", "courses_programrequirement"."depth", "courses_programrequirement"."numchild", "courses_programrequirement"."node_type", "courses_programrequirement"."operator", "courses_programrequirement"."operator_value", "courses_programrequirement"."program_id", "courses_programrequirement"."course_id", "courses_programrequirement"."title", "courses_programrequirement"."elective_flag", "courses_course"."id", "courses_course"."created_on", "courses_course"."updated_on", "courses_course"."title", "courses_course"."readable_id", "courses_course"."live", "wagtailcore_page"."id", "wagtailcore_page"."path", "wagtailcore_page"."depth", "wagtailcore_page"."numchild", "wagtailcore_page"."translation_key", "wagtailcore_page"."locale_id", "wagtailcore_page"."latest_revision_id", "wagtailcore_page"."live", "wagtailcore_page"."has_unpublished_changes", "wagtailcore_page"."first_published_at", "wagtailcore_page"."last_published_at", "wagtailcore_page"."live_revision_id", "wagtailcore_page"."go_live_at", "wagtailcore_page"."expire_at", "wagtailcore_page"."expired", "wagtailcore_page"."locked", "wagtailcore_page"."locked_at", "wagtailcore_page"."locked_by_id", "wagtailcore_page"."title", "wagtailcore_page"."draft_title", "wagtailcore_page"."slug", "wagtailcore_page"."content_type_id", "wagtailcore_page"."url_path", "wagtailcore_page"."owner_id", "wagtailcore_page"."seo_title", "wagtailcore_page"."show_in_menus", "wagtailcore_page"."search_description", "wagtailcore_page"."latest_revision_created_at", "wagtailcore_page"."alias_of_id", "cms_coursepage"."page_ptr_id", "cms_coursepage"."description", "cms_coursepage"."length", "cms_coursepage"."effort", "cms_coursepage"."price", "cms_coursepage"."prerequisites", "cms_coursepage"."about", "cms_coursepage"."faq_url", "cms_coursepage"."video_url", "cms_coursepage"."what_you_learn", "cms_coursepage"."feature_image_id", "cms_coursepage"."faculty_section_title", "cms_coursepage"."course_id" FROM "courses_programrequirement" LEFT OUTER JOIN "courses_course" ON ("courses_programrequirement"."course_id" = "courses_course"."id") LEFT OUTER JOIN "cms_coursepage" ON ("courses_course"."id" = "cms_coursepage"."course_id") LEFT OUTER JOIN "wagtailcore_page" ON ("cms_coursepage"."page_ptr_id" = "wagtailcore_page"."id") WHERE ("courses_programrequirement"."node_type" = 'course' AND "courses_programrequirement"."path"::text LIKE '00010001%' AND "courses_programrequirement"."program_id" = 1) ORDER BY "courses_programrequirement"."path" ASC; args=(<ProgramRequirementNodeType.COURSE: 'course'>, '00010001%', 1)
mitxonline-web-1      | [2023-09-10 14:09:09] DEBUG 2339 [django.db.backends] utils.py:123 - [c18a6235ffb0] - (0.001) SELECT "courses_programrequirement"."id", "courses_programrequirement"."path", "courses_programrequirement"."depth", "courses_programrequirement"."numchild", "courses_programrequirement"."node_type", "courses_programrequirement"."operator", "courses_programrequirement"."operator_value", "courses_programrequirement"."program_id", "courses_programrequirement"."course_id", "courses_programrequirement"."title", "courses_programrequirement"."elective_flag" FROM "courses_programrequirement" WHERE ("courses_programrequirement"."depth" = 2 AND "courses_programrequirement"."program_id" = 1) ORDER BY "courses_programrequirement"."path" ASC; args=(2, 1)
mitxonline-web-1      | [2023-09-10 14:09:09] DEBUG 2339 [django.db.backends] utils.py:123 - [c18a6235ffb0] - (0.002) SELECT "courses_programrequirement"."id", "courses_programrequirement"."path", "courses_programrequirement"."depth", "courses_programrequirement"."numchild", "courses_programrequirement"."node_type", "courses_programrequirement"."operator", "courses_programrequirement"."operator_value", "courses_programrequirement"."program_id", "courses_programrequirement"."course_id", "courses_programrequirement"."title", "courses_programrequirement"."elective_flag", "courses_course"."id", "courses_course"."created_on", "courses_course"."updated_on", "courses_course"."title", "courses_course"."readable_id", "courses_course"."live", "wagtailcore_page"."id", "wagtailcore_page"."path", "wagtailcore_page"."depth", "wagtailcore_page"."numchild", "wagtailcore_page"."translation_key", "wagtailcore_page"."locale_id", "wagtailcore_page"."latest_revision_id", "wagtailcore_page"."live", "wagtailcore_page"."has_unpublished_changes", "wagtailcore_page"."first_published_at", "wagtailcore_page"."last_published_at", "wagtailcore_page"."live_revision_id", "wagtailcore_page"."go_live_at", "wagtailcore_page"."expire_at", "wagtailcore_page"."expired", "wagtailcore_page"."locked", "wagtailcore_page"."locked_at", "wagtailcore_page"."locked_by_id", "wagtailcore_page"."title", "wagtailcore_page"."draft_title", "wagtailcore_page"."slug", "wagtailcore_page"."content_type_id", "wagtailcore_page"."url_path", "wagtailcore_page"."owner_id", "wagtailcore_page"."seo_title", "wagtailcore_page"."show_in_menus", "wagtailcore_page"."search_description", "wagtailcore_page"."latest_revision_created_at", "wagtailcore_page"."alias_of_id", "cms_coursepage"."page_ptr_id", "cms_coursepage"."description", "cms_coursepage"."length", "cms_coursepage"."effort", "cms_coursepage"."price", "cms_coursepage"."prerequisites", "cms_coursepage"."about", "cms_coursepage"."faq_url", "cms_coursepage"."video_url", "cms_coursepage"."what_you_learn", "cms_coursepage"."feature_image_id", "cms_coursepage"."faculty_section_title", "cms_coursepage"."course_id" FROM "courses_programrequirement" LEFT OUTER JOIN "courses_course" ON ("courses_programrequirement"."course_id" = "courses_course"."id") LEFT OUTER JOIN "cms_coursepage" ON ("courses_course"."id" = "cms_coursepage"."course_id") LEFT OUTER JOIN "wagtailcore_page" ON ("cms_coursepage"."page_ptr_id" = "wagtailcore_page"."id") WHERE ("courses_programrequirement"."node_type" = 'course' AND "courses_programrequirement"."path"::text LIKE '00010001%' AND "courses_programrequirement"."program_id" = 1) ORDER BY "courses_programrequirement"."path" ASC; args=(<ProgramRequirementNodeType.COURSE: 'course'>, '00010001%', 1)
mitxonline-web-1      | [2023-09-10 14:09:09] DEBUG 2339 [django.db.backends] utils.py:123 - [c18a6235ffb0] - (0.001) SELECT "courses_programrequirement"."id", "courses_programrequirement"."path", "courses_programrequirement"."depth", "courses_programrequirement"."numchild", "courses_programrequirement"."node_type", "courses_programrequirement"."operator", "courses_programrequirement"."operator_value", "courses_programrequirement"."program_id", "courses_programrequirement"."course_id", "courses_programrequirement"."title", "courses_programrequirement"."elective_flag" FROM "courses_programrequirement" WHERE ("courses_programrequirement"."depth" = 1 AND "courses_programrequirement"."program_id" = 1) ORDER BY "courses_programrequirement"."path" ASC LIMIT 1; args=(1, 1)
mitxonline-web-1      | [2023-09-10 14:09:09] DEBUG 2339 [django.db.backends] utils.py:123 - [c18a6235ffb0] - (0.001) SELECT "courses_programrequirement"."id", "courses_programrequirement"."path", "courses_programrequirement"."depth", "courses_programrequirement"."numchild", "courses_programrequirement"."node_type", "courses_programrequirement"."operator", "courses_programrequirement"."operator_value", "courses_programrequirement"."program_id", "courses_programrequirement"."course_id", "courses_programrequirement"."title", "courses_programrequirement"."elective_flag" FROM "courses_programrequirement" WHERE "courses_programrequirement"."path"::text LIKE '0001%' ORDER BY "courses_programrequirement"."path" ASC; args=('0001%',)
mitxonline-web-1      | [2023-09-10 14:09:09] DEBUG 2339 [django.db.backends] utils.py:123 - [c18a6235ffb0] - (0.001) SELECT (1) AS "a" FROM "cms_programpage" WHERE "cms_programpage"."program_id" = 1 LIMIT 1; args=(1,)
mitxonline-web-1      | [2023-09-10 14:09:09] DEBUG 2339 [django.db.backends] utils.py:123 - [c18a6235ffb0] - (0.001) SELECT "wagtailcore_page"."id", "wagtailcore_page"."path", "wagtailcore_page"."depth", "wagtailcore_page"."numchild", "wagtailcore_page"."translation_key", "wagtailcore_page"."locale_id", "wagtailcore_page"."latest_revision_id", "wagtailcore_page"."live", "wagtailcore_page"."has_unpublished_changes", "wagtailcore_page"."first_published_at", "wagtailcore_page"."last_published_at", "wagtailcore_page"."live_revision_id", "wagtailcore_page"."go_live_at", "wagtailcore_page"."expire_at", "wagtailcore_page"."expired", "wagtailcore_page"."locked", "wagtailcore_page"."locked_at", "wagtailcore_page"."locked_by_id", "wagtailcore_page"."title", "wagtailcore_page"."draft_title", "wagtailcore_page"."slug", "wagtailcore_page"."content_type_id", "wagtailcore_page"."url_path", "wagtailcore_page"."owner_id", "wagtailcore_page"."seo_title", "wagtailcore_page"."show_in_menus", "wagtailcore_page"."search_description", "wagtailcore_page"."latest_revision_created_at", "wagtailcore_page"."alias_of_id", "cms_programpage"."page_ptr_id", "cms_programpage"."description", "cms_programpage"."length", "cms_programpage"."effort", "cms_programpage"."price", "cms_programpage"."prerequisites", "cms_programpage"."about", "cms_programpage"."faq_url", "cms_programpage"."video_url", "cms_programpage"."what_you_learn", "cms_programpage"."feature_image_id", "cms_programpage"."faculty_section_title", "cms_programpage"."program_id" FROM "cms_programpage" INNER JOIN "wagtailcore_page" ON ("cms_programpage"."page_ptr_id" = "wagtailcore_page"."id") WHERE "cms_programpage"."program_id" = 1 LIMIT 21; args=(1,)
mitxonline-web-1      | [2023-09-10 14:09:09] DEBUG 2339 [django.db.backends] utils.py:123 - [c18a6235ffb0] - (0.001) SELECT "courses_program"."id", "courses_program"."created_on", "courses_program"."updated_on", "courses_program"."title", "courses_program"."readable_id", "courses_program"."live", "courses_program"."program_type" FROM "courses_program" WHERE "courses_program"."id" = 1 LIMIT 21; args=(1,)
mitxonline-web-1      | [2023-09-10 14:09:09] DEBUG 2339 [django.db.backends] utils.py:123 - [c18a6235ffb0] - (0.001) SELECT "courses_department"."id", "courses_department"."created_on", "courses_department"."updated_on", "courses_department"."name" FROM "courses_department" INNER JOIN "courses_program_departments" ON ("courses_department"."id" = "courses_program_departments"."department_id") WHERE "courses_program_departments"."program_id" = 1; args=(1,)
mitxonline-web-1      | [2023-09-10 14:09:09] ERROR 2339 [nplusone] notifiers.py:40 - [c18a6235ffb0] - Potential unnecessary eager load detected on `Course.page`
mitxonline-web-1      | [2023-09-10 14:09:09] ERROR 2339 [nplusone] notifiers.py:40 - [c18a6235ffb0] - Potential unnecessary eager load detected on `Course.page`

-- Now -------
49ms

mitxonline-web-1      | [2023-09-10 14:04:44] DEBUG 2128 [django.db.backends] utils.py:123 - [c18a6235ffb0] - (0.001) SELECT "courses_program"."id", "courses_program"."created_on", "courses_program"."updated_on", "courses_program"."title", "courses_program"."readable_id", "courses_program"."live", "courses_program"."program_type" FROM "courses_program" WHERE "courses_program"."live" ORDER BY "courses_program"."id" ASC; args=()
mitxonline-web-1      | [2023-09-10 14:04:44] DEBUG 2128 [django.db.backends] utils.py:123 - [c18a6235ffb0] - (0.001) SELECT ("courses_program_departments"."program_id") AS "_prefetch_related_val_program_id", "courses_department"."id", "courses_department"."created_on", "courses_department"."updated_on", "courses_department"."name" FROM "courses_department" INNER JOIN "courses_program_departments" ON ("courses_department"."id" = "courses_program_departments"."department_id") WHERE "courses_program_departments"."program_id" IN (1); args=(1,)
mitxonline-web-1      | [2023-09-10 14:04:44] DEBUG 2128 [django.db.backends] utils.py:123 - [c18a6235ffb0] - (0.002) SELECT "courses_programrequirement"."id", "courses_programrequirement"."path", "courses_programrequirement"."depth", "courses_programrequirement"."numchild", "courses_programrequirement"."node_type", "courses_programrequirement"."operator", "courses_programrequirement"."operator_value", "courses_programrequirement"."program_id", "courses_programrequirement"."course_id", "courses_programrequirement"."title", "courses_programrequirement"."elective_flag" FROM "courses_programrequirement" WHERE ("courses_programrequirement"."depth" = 2 AND "courses_programrequirement"."program_id" = 1) ORDER BY "courses_programrequirement"."path" ASC; args=(2, 1)
mitxonline-web-1      | [2023-09-10 14:04:44] DEBUG 2128 [django.db.backends] utils.py:123 - [c18a6235ffb0] - (0.002) SELECT "courses_programrequirement"."id", "courses_programrequirement"."path", "courses_programrequirement"."depth", "courses_programrequirement"."numchild", "courses_programrequirement"."node_type", "courses_programrequirement"."operator", "courses_programrequirement"."operator_value", "courses_programrequirement"."program_id", "courses_programrequirement"."course_id", "courses_programrequirement"."title", "courses_programrequirement"."elective_flag", "courses_course"."id", "courses_course"."created_on", "courses_course"."updated_on", "courses_course"."title", "courses_course"."readable_id", "courses_course"."live" FROM "courses_programrequirement" LEFT OUTER JOIN "courses_course" ON ("courses_programrequirement"."course_id" = "courses_course"."id") WHERE ("courses_programrequirement"."node_type" = 'course' AND "courses_programrequirement"."path"::text LIKE '00010001%' AND "courses_programrequirement"."program_id" = 1) ORDER BY "courses_programrequirement"."path" ASC; args=(<ProgramRequirementNodeType.COURSE: 'course'>, '00010001%', 1)
mitxonline-web-1      | [2023-09-10 14:04:44] DEBUG 2128 [django.db.backends] utils.py:123 - [c18a6235ffb0] - (0.002) SELECT "courses_courserun"."id", "courses_courserun"."created_on", "courses_courserun"."updated_on", "courses_courserun"."course_id", "courses_courserun"."title", "courses_courserun"."courseware_id", "courses_courserun"."run_tag", "courses_courserun"."courseware_url_path", "courses_courserun"."start_date", "courses_courserun"."end_date", "courses_courserun"."certificate_available_date", "courses_courserun"."enrollment_start", "courses_courserun"."enrollment_end", "courses_courserun"."expiration_date", "courses_courserun"."upgrade_deadline", "courses_courserun"."live", "courses_courserun"."is_self_paced" FROM "courses_courserun" WHERE "courses_courserun"."course_id" = 3; args=(3,)
mitxonline-web-1      | [2023-09-10 14:04:44] DEBUG 2128 [django.db.backends] utils.py:123 - [c18a6235ffb0] - (0.001) SELECT "courses_courserun"."id", "courses_courserun"."created_on", "courses_courserun"."updated_on", "courses_courserun"."course_id", "courses_courserun"."title", "courses_courserun"."courseware_id", "courses_courserun"."run_tag", "courses_courserun"."courseware_url_path", "courses_courserun"."start_date", "courses_courserun"."end_date", "courses_courserun"."certificate_available_date", "courses_courserun"."enrollment_start", "courses_courserun"."enrollment_end", "courses_courserun"."expiration_date", "courses_courserun"."upgrade_deadline", "courses_courserun"."live", "courses_courserun"."is_self_paced" FROM "courses_courserun" WHERE "courses_courserun"."course_id" = 3; args=(3,)
mitxonline-web-1      | [2023-09-10 14:04:44] DEBUG 2128 [django.db.backends] utils.py:123 - [c18a6235ffb0] - (0.001) SELECT "courses_department"."id", "courses_department"."created_on", "courses_department"."updated_on", "courses_department"."name" FROM "courses_department" INNER JOIN "courses_course_departments" ON ("courses_department"."id" = "courses_course_departments"."department_id") WHERE "courses_course_departments"."course_id" = 3; args=(3,)
mitxonline-web-1      | [2023-09-10 14:04:44] DEBUG 2128 [django.db.backends] utils.py:123 - [c18a6235ffb0] - (0.002) SELECT "wagtailcore_page"."id", "wagtailcore_page"."path", "wagtailcore_page"."depth", "wagtailcore_page"."numchild", "wagtailcore_page"."translation_key", "wagtailcore_page"."locale_id", "wagtailcore_page"."latest_revision_id", "wagtailcore_page"."live", "wagtailcore_page"."has_unpublished_changes", "wagtailcore_page"."first_published_at", "wagtailcore_page"."last_published_at", "wagtailcore_page"."live_revision_id", "wagtailcore_page"."go_live_at", "wagtailcore_page"."expire_at", "wagtailcore_page"."expired", "wagtailcore_page"."locked", "wagtailcore_page"."locked_at", "wagtailcore_page"."locked_by_id", "wagtailcore_page"."title", "wagtailcore_page"."draft_title", "wagtailcore_page"."slug", "wagtailcore_page"."content_type_id", "wagtailcore_page"."url_path", "wagtailcore_page"."owner_id", "wagtailcore_page"."seo_title", "wagtailcore_page"."show_in_menus", "wagtailcore_page"."search_description", "wagtailcore_page"."latest_revision_created_at", "wagtailcore_page"."alias_of_id", "cms_coursepage"."page_ptr_id", "cms_coursepage"."description", "cms_coursepage"."length", "cms_coursepage"."effort", "cms_coursepage"."price", "cms_coursepage"."prerequisites", "cms_coursepage"."about", "cms_coursepage"."faq_url", "cms_coursepage"."video_url", "cms_coursepage"."what_you_learn", "cms_coursepage"."feature_image_id", "cms_coursepage"."faculty_section_title", "cms_coursepage"."course_id" FROM "cms_coursepage" INNER JOIN "wagtailcore_page" ON ("cms_coursepage"."page_ptr_id" = "wagtailcore_page"."id") WHERE "cms_coursepage"."course_id" = 3 LIMIT 21; args=(3,)
mitxonline-web-1      | [2023-09-10 14:04:44] DEBUG 2128 [django.db.backends] utils.py:123 - [c18a6235ffb0] - (0.001) SELECT "courses_programrequirement"."id", "courses_programrequirement"."path", "courses_programrequirement"."depth", "courses_programrequirement"."numchild", "courses_programrequirement"."node_type", "courses_programrequirement"."operator", "courses_programrequirement"."operator_value", "courses_programrequirement"."program_id", "courses_programrequirement"."course_id", "courses_programrequirement"."title", "courses_programrequirement"."elective_flag" FROM "courses_programrequirement" WHERE ("courses_programrequirement"."depth" = 1 AND "courses_programrequirement"."program_id" = 1) ORDER BY "courses_programrequirement"."path" ASC LIMIT 1; args=(1, 1)
mitxonline-web-1      | [2023-09-10 14:04:44] DEBUG 2128 [django.db.backends] utils.py:123 - [c18a6235ffb0] - (0.001) SELECT "courses_programrequirement"."id", "courses_programrequirement"."path", "courses_programrequirement"."depth", "courses_programrequirement"."numchild", "courses_programrequirement"."node_type", "courses_programrequirement"."operator", "courses_programrequirement"."operator_value", "courses_programrequirement"."program_id", "courses_programrequirement"."course_id", "courses_programrequirement"."title", "courses_programrequirement"."elective_flag" FROM "courses_programrequirement" WHERE "courses_programrequirement"."path"::text LIKE '0001%' ORDER BY "courses_programrequirement"."path" ASC; args=('0001%',)
mitxonline-web-1      | [2023-09-10 14:04:44] ERROR 2128 [nplusone] notifiers.py:40 - [c18a6235ffb0] - Potential n+1 query detected on `Program.page`
mitxonline-web-1      | [2023-09-10 14:04:44] ERROR 2128 [nplusone] notifiers.py:40 - [c18a6235ffb0] - Potential n+1 query detected on `Program.page`

`/course_runs` (31 records)

http://mitxonline.odl.local:8013/api/course_runs

-- Time Before -------
896ms

-- Time Now -------
672ms

[
    {
        "title": "Demonstration Course",
        "start_date": null,
        "end_date": null,
        "enrollment_start": null,
        "enrollment_end": null,
        "expiration_date": null,
        "courseware_url": "http://edx.odl.local:18000/courses/course-v1:edX+DemoX+Demo_Course/",
        "courseware_id": "course-v1:edX+DemoX+Demo_Course",
        "certificate_available_date": null,
        "upgrade_deadline": null,
        "is_upgradable": true,
        "is_self_paced": false,
        "run_tag": "Demo_Course",
        "id": 1,
        "live": true,
        "course_number": "DemoX",
        "products": [
            {
                "id": 2,
                "price": "999.00",
                "description": "course-v1:edX+DemoX+Demo_Course",
                "is_active": true,
                "product_flexible_price": {
                    "amount": null,
                    "automatic": false,
                    "discount_type": null,
                    "redemption_type": null,
                    "max_redemptions": null,
                    "discount_code": "",
                    "payment_type": null,
                    "activation_date": null,
                    "expiration_date": null
                }
            }
        ],
        "approved_flexible_price_exists": false,
        "course": {
            "id": 1,
            "title": "Demonstration Course",
            "readable_id": "course-v1:edX+DemoX",
            "next_run_id": null,
            "departments": [],
            "page": {
                "feature_image_src": "/static/images/mit-dome.png",
                "page_url": "/courses/course-v1:edX+DemoX/",
                "financial_assistance_form_url": "",
                "description": "Demonstration Course",
                "current_price": null,
                "instructors": [],
                "live": false,
                "length": "No Data",
                "effort": null
            },
            "programs": null
        }
    }
]

rachellougee

Nice. I did see performance improvement with courses/programs/course_runs APIs, I didn't notice any other issues.

rachellougee · 2023-09-11T18:29:08Z

courses/serializers.py

+class DepartmentSerializer(serializers.ModelSerializer):
+    """Department model serializer"""
+
+    name = CharField(max_length=128)


I think this line isn't needed as its a standard model field that included in serializer meta field list

This reverts commit f16b217.

* Revert "Department REST API (#1877)" This reverts commit f49e9c6. * Revert "1869: Course program api performance improvements (#1872)" This reverts commit f16b217.

collinpreston added 8 commits September 7, 2023 15:18

This works but is just POC

c4efe0d

Add department serializer and prefetch

39680bd

improvement for courses API

7c6b618

program improvements

0b18cea

Fix course run serializer, migration, enrollment api

eaf39ad

Add live filter to front end API call

cad0f38

Fix tests, format, add filtering to front end query

9cc6a9d

select related course on course_run API

59c9c21

collinpreston linked an issue Sep 10, 2023 that may be closed by this pull request

Courses API performance issues #1869

Closed

collinpreston added 6 commits September 10, 2023 10:32

Javascript formatting

51966b7

Fix serializers

b6bf110

Fix Course serializer

90e4d19

Format

4e18f8a

py test passing

d8ff663

Format and repair tests

1892532

collinpreston marked this pull request as ready for review September 10, 2023 16:46

collinpreston added the Needs Review label Sep 10, 2023

collinpreston added 2 commits September 11, 2023 10:05

Clean up department serializer

e7301b9

Add class doc

48a56f0

rachellougee self-assigned this Sep 11, 2023

collinpreston mentioned this pull request Sep 11, 2023

Department REST API #1877

Merged

rachellougee approved these changes Sep 11, 2023

View reviewed changes

Cleanup department serializer

815ef09

collinpreston merged commit f16b217 into main Sep 11, 2023

collinpreston deleted the course-program-api-performance-improvements branch September 11, 2023 18:49

odlbot mentioned this pull request Sep 11, 2023

Release 0.73.1 #1879

Merged

2 tasks

collinpreston added a commit that referenced this pull request Sep 12, 2023

Revert "1869: Course program api performance improvements (#1872)"

b506f4a

This reverts commit f16b217.

collinpreston mentioned this pull request Sep 12, 2023

Repair cart page including reinstated course api improvements #1885

Merged

jkachel mentioned this pull request Sep 18, 2023

Updates the code behind the More Dates selector to work without the course page #1900

Merged

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

1869: Course program api performance improvements #1872

1869: Course program api performance improvements #1872

collinpreston commented Sep 10, 2023 •

edited

Loading

rachellougee left a comment

rachellougee Sep 11, 2023

1869: Course program api performance improvements #1872

1869: Course program api performance improvements #1872

Conversation

collinpreston commented Sep 10, 2023 • edited Loading

What are the relevant tickets?

Description (What does it do?)

How can this be tested?

Additional Context

Testing results

/courses (32 records)

/courses?id=4&live=true (32 records)

/programs?live=true (1 record)

/course_runs (31 records)

rachellougee left a comment

Choose a reason for hiding this comment

rachellougee Sep 11, 2023

Choose a reason for hiding this comment

collinpreston commented Sep 10, 2023 •

edited

Loading

`/courses` (32 records)

`/courses?id=4&live=true` (32 records)

`/programs?live=true` (1 record)

`/course_runs` (31 records)