PostgreSQL查询优化请求：将3分钟执行时长压缩至59秒内

当前PostgreSQL查询执行耗时超过3分钟，导致API出现超时问题，需要将查询结果返回时长控制在59秒内。

WITH assessment_path_resource AS 
(
         SELECT group_path_resources.*,
            learning_record_store_user_activities.registration::text AS registration_id,
            /*group_path_resources.id AS group_path_resource_id,
            group_path_resources.created_at,*/
            learning_record_store_user_activities.user_id,
            learning_record_store_user_activities.assigned_through_id AS path_id,
            spaces.title AS path_name,
            programs_cohorts.id AS cohort_id,
            programs_cohorts.title AS cohort_name,
            programs_programs.id AS program_id,
            programs_programs.title AS program_name,
            programs_programs.tenant_id,
            --group_path_resources.context_data ->> 'xapi_activity_id'::text AS xapi_activity_id,
            concat(COALESCE(members.first_name, ''::character varying), ' ', COALESCE(members.last_name, ''::character varying)) AS participant_full_name,
            row_number() OVER (PARTITION BY learning_record_store_user_activities.registration 
            ORDER BY group_path_resources.created_at DESC) AS duplicate_registration_id_row_number 
         
         from 
         (
            select group_path_resources.id AS group_path_resource_id,
                      group_path_resources.created_at,
                      group_path_resources.context_data ->> 'xapi_activity_id'::text AS xapi_activity_id,
                      resourceable_id
            FROM group_path_resources
            WHERE group_path_resources.resourceable_type::text = 'LearningRecordStore::ActivityInstance'::text 
            AND (group_path_resources.context_data ->> 'xapi_activity_id'::text) ~~ 'https://assessment.%360%'::text
          )group_path_resources
             LEFT JOIN learning_record_store_user_activities ON group_path_resources.resourceable_id = learning_record_store_user_activities.activity_instance_id
             JOIN members ON members.user_id = learning_record_store_user_activities.user_id
             JOIN spaces ON spaces.id = learning_record_store_user_activities.assigned_through_id
             JOIN programs_cohorts ON programs_cohorts.id = spaces.cohort_id
             JOIN programs_programs ON programs_programs.id = programs_cohorts.program_id
       --   WHERE group_path_resources.resourceable_type::text = 'LearningRecordStore::ActivityInstance'::text 
        --  AND (group_path_resources.context_data ->> 'xapi_activity_id'::text) ~~ 'https://assessment.%360%'::text
),
as_subject_form_response AS 
(
         SELECT 
         as_form_response.form_response_group_id,
            as_form_response.updated_date
           FROM assessment_service.form_response as_form_response
          WHERE as_form_response.form_response_status_type = 'complete'::form_response_status_type 
          AND as_form_response.submitter_type = 'subject'::audience_type_enum
),
as_invitee_form_response AS 
(
         SELECT as_form_response.form_response_group_id,
            count(as_form_response.id) AS total,
            array_agg(as_form_response.updated_date ORDER BY as_form_response.updated_date) AS updated_dates
           FROM assessment_service.form_response as_form_response
          WHERE as_form_response.form_response_status_type = 'complete'::form_response_status_type 
          AND as_form_response.submitter_type = 'invitee'::audience_type_enum
          GROUP BY as_form_response.form_response_group_id
),
as_feedback_requests AS 
(
         SELECT feedback_request.form_response_group_id,
            sum(
                CASE
                    WHEN feedback_request.is_fulfilled AND (feedback_request.is_declined IS NULL OR NOT feedback_request.is_declined) AND (feedback_request.is_archived IS NULL OR NOT feedback_request.is_archived) THEN 1
                    ELSE 0
                END) AS total_completed,
            max(EXTRACT(day FROM CURRENT_TIMESTAMP - feedback_request.created_date)::integer) AS days_passed_since_first_fbr,
            min(feedback_request.created_date) AS first_fbr_created_date
           FROM assessment_service.feedback_request
          GROUP BY feedback_request.form_response_group_id
),
as_form_response_group AS 
(
         SELECT form_response_group.id,
            form_response_group.registration_id::text,
            form_response_group.subject_id AS user_id,
            form_response_group.tenant_id,
            form_response_group.path_id,
            form_response_group.created_date,
            form_response_group.released_date,
            form_response_group.subject_viewable AS is_released,
            COALESCE(as_subject_form_response.form_response_group_id IS NOT NULL AND (COALESCE(as_feedback_requests.total_completed, 0::bigint) >= COALESCE((form_response_group.form_configuration ->> 'min_feedback_requests'::text)::integer, 0) OR COALESCE(as_feedback_requests.days_passed_since_first_fbr, 0) >= COALESCE((form_response_group.form_configuration ->> 'minimum_release_timeline'::text)::integer, 21)), false) AS is_releasable,
                CASE
                    WHEN as_subject_form_response.form_response_group_id IS NOT NULL AND COALESCE(as_feedback_requests.total_completed, 0::bigint) >= COALESCE((form_response_group.form_configuration ->> 'min_feedback_requests'::text)::integer, 0) THEN 'min_feedback_requests_threshold_met'::text
                    WHEN as_subject_form_response.form_response_group_id IS NOT NULL AND COALESCE(as_feedback_requests.days_passed_since_first_fbr, 0) >= COALESCE((form_response_group.form_configuration ->> 'minimum_release_timeline'::text)::integer, 21) THEN 'minimum_release_timeline_threshold_met'::text
                    ELSE 'not_eligible_for_release'::text
                END AS is_releasable_reason,
            COALESCE((form_response_group.form_configuration ->> 'min_feedback_requests'::text)::integer, 0) AS minimum_feedback_requests,
            COALESCE((form_response_group.form_configuration ->> 'minimum_release_timeline'::text)::integer, 21) AS minimum_release_timeline,
            COALESCE(as_feedback_requests.total_completed, 0::bigint) AS feedback_requests_completed,
            COALESCE(as_feedback_requests.days_passed_since_first_fbr, 0) AS days_passed_since_first_fbr,
            as_subject_form_response.form_response_group_id IS NOT NULL AS is_self_assessment_completed,
                CASE
                    WHEN COALESCE(as_feedback_requests.total_completed, 0::bigint) >= COALESCE((form_response_group.form_configuration ->> 'min_feedback_requests'::text)::integer, 0) THEN as_invitee_form_response.updated_dates[(form_response_group.form_configuration ->> 'min_feedback_requests'::text)::integer]
                    ELSE NULL::timestamp with time zone
                END AS minimum_feedback_requests_completed_threshold_met_date,
            as_feedback_requests.first_fbr_created_date,
            as_subject_form_response.updated_date AS self_response_updated_date,
            as_feedback_requests.total_completed AS total_fbrs_completed
           FROM assessment_service.form_response_group
             LEFT JOIN as_feedback_requests ON as_feedback_requests.form_response_group_id = form_response_group.id
             LEFT JOIN as_subject_form_response ON as_subject_form_response.form_response_group_id = form_response_group.id
             LEFT JOIN as_invitee_form_response ON as_invitee_form_response.form_response_group_id = form_response_group.id
),
assessments_with_stage_data AS 
(
         SELECT as_form_response_group.id AS as_form_response_group_id,
            assessment_path_resource.participant_full_name,
            assessment_path_resource.path_id,
            assessment_path_resource.path_name,
            assessment_path_resource.cohort_id,
            assessment_path_resource.cohort_name,
            assessment_path_resource.program_id,
            assessment_path_resource.program_name,
            assessment_path_resource.tenant_id,
            assessment_path_resource.xapi_activity_id,
            assessment_path_resource.registration_id,
                CASE
                    WHEN as_form_response_group.id IS NULL THEN 'Not Started'::text
                    WHEN as_form_response_group.id IS NOT NULL AND NOT as_form_response_group.is_self_assessment_completed THEN 'Self-assessment pending'::text
                    WHEN as_form_response_group.id IS NOT NULL AND as_form_response_group.is_self_assessment_completed AND NOT as_form_response_group.is_released AND NOT as_form_response_group.is_releasable THEN 'Feedback pending'::text
                    WHEN as_form_response_group.id IS NOT NULL AND as_form_response_group.is_self_assessment_completed AND as_form_response_group.is_releasable AND NOT as_form_response_group.is_released THEN 'Eligible for release'::text
                    WHEN as_form_response_group.id IS NOT NULL AND as_form_response_group.is_released THEN 'Released'::text
                    ELSE 'UNKNOWN STAGE'::text
                END AS stage_label,
                CASE
                    WHEN as_form_response_group.id IS NULL THEN assessment_path_resource.created_at::timestamp with time zone
                    WHEN as_form_response_group.id IS NOT NULL AND NOT as_form_response_group.is_self_assessment_completed THEN as_form_response_group.created_date
                    WHEN as_form_response_group.id IS NOT NULL AND as_form_response_group.is_self_assessment_completed AND NOT as_form_response_group.is_released AND NOT as_form_response_group.is_releasable THEN as_form_response_group.self_response_updated_date
                    WHEN as_form_response_group.id IS NOT NULL AND as_form_response_group.is_self_assessment_completed AND as_form_response_group.is_releasable AND NOT as_form_response_group.is_released THEN
                    CASE
                        WHEN as_form_response_group.minimum_feedback_requests = 0 OR as_form_response_group.minimum_release_timeline = 0 THEN as_form_response_group.self_response_updated_date
                        WHEN COALESCE(as_form_response_group.minimum_feedback_requests, 0) > 0 AND COALESCE(as_form_response_group.minimum_release_timeline, 21) > 0 THEN
                        CASE
                            WHEN as_form_response_group.feedback_requests_completed >= as_form_response_group.minimum_feedback_requests AND as_form_response_group.days_passed_since_first_fbr < as_form_response_group.minimum_release_timeline THEN as_form_response_group.minimum_feedback_requests_completed_threshold_met_date
                            WHEN as_form_response_group.feedback_requests_completed < as_form_response_group.minimum_feedback_requests AND as_form_response_group.days_passed_since_first_fbr >= as_form_response_group.minimum_release_timeline THEN as_form_response_group.first_fbr_created_date + make_interval(days => as_form_response_group.minimum_release_timeline)
                            ELSE
                            CASE
                                WHEN as_form_response_group.first_fbr_created_date < as_form_response_group.minimum_feedback_requests_completed_threshold_met_date THEN as_form_response_group.first_fbr_created_date + make_interval(days => as_form_response_group.minimum_release_timeline)
                                ELSE as_form_response_group.minimum_feedback_requests_completed_threshold_met_date
                            END
                        END
                        WHEN COALESCE(as_form_response_group.minimum_feedback_requests, 0) > 0 THEN as_form_response_group.minimum_feedback_requests_completed_threshold_met_date
                        WHEN COALESCE(as_form_response_group.minimum_release_timeline, 21) > 0 THEN as_form_response_group.first_fbr_created_date + make_interval(days => as_form_response_group.minimum_release_timeline)
                        ELSE NULL::timestamp with time zone
                    END
                    WHEN as_form_response_group.id IS NOT NULL AND as_form_response_group.is_released THEN as_form_response_group.released_date
                    ELSE NULL::timestamp with time zone
                END AS stage_started_at,
            as_form_response_group.released_date,
            as_form_response_group.is_releasable_reason,
            as_form_response_group.self_response_updated_date,
            as_form_response_group.days_passed_since_first_fbr,
            as_form_response_group.minimum_release_timeline,
            as_form_response_group.first_fbr_created_date,
            as_form_response_group.minimum_feedback_requests,
            as_form_response_group.total_fbrs_completed,
            as_form_response_group.minimum_feedback_requests_completed_threshold_met_date
           FROM assessment_path_resource
             LEFT JOIN as_form_response_group ON as_form_response_group.registration_id = assessment_path_resource.registration_id
          WHERE assessment_path_resource.duplicate_registration_id_row_number = 1
)
 SELECT assessments_with_stage_data.as_form_response_group_id,
    assessments_with_stage_data.participant_full_name,
    assessments_with_stage_data.path_id,
    assessments_with_stage_data.path_name,
    assessments_with_stage_data.cohort_id,
    assessments_with_stage_data.cohort_name,
    assessments_with_stage_data.program_id,
    assessments_with_stage_data.program_name,
    assessments_with_stage_data.tenant_id,
    assessments_with_stage_data.xapi_activity_id,
    assessments_with_stage_data.registration_id,
    assessments_with_stage_data.stage_label,
    assessments_with_stage_data.stage_started_at,
    assessments_with_stage_data.released_date,
    assessments_with_stage_data.is_releasable_reason,
    assessments_with_stage_data.self_response_updated_date,
    assessments_with_stage_data.days_passed_since_first_fbr,
    assessments_with_stage_data.minimum_release_timeline,
    assessments_with_stage_data.first_fbr_created_date,
    assessments_with_stage_data.minimum_feedback_requests,
    assessments_with_stage_data.total_fbrs_completed,
    assessments_with_stage_data.minimum_feedback_requests_completed_threshold_met_date,
    array_position(ARRAY['Not Started'::text, 'Self-assessment pending'::text, 'Feedback pending'::text, 'Eligible for release'::text, 'Released'::text], assessments_with_stage_data.stage_label) AS stage_order_index
   FROM assessments_with_stage_data
 -- ORDER BY (array_position(ARRAY['Not Started'::text, 'Self-assessment pending'::text, 'Feedback pending'::text, 'Eligible for release'::text, 'Released'::text], assessments_with_stage_data.stage_label)) desc
    order by stage_order_index desc

执行计划信息 #

• 详细执行计划：包含全量查询执行步骤、资源消耗与节点耗时
• 精简执行计划：聚焦关键执行节点与性能瓶颈

当前查询耗时约3分57秒，已导致API超时，需优化至59秒内完成。

内容的提问来源于stack exchange，提问作者Ankit Agrawal