PostgreSQL Question posted in
The official documentation can be found here.

postgresql query taking 3 min to execute ,how can i reduce the time to within 60 seconds

I need help optimize below postgresql query.
Currently its taking more than 3 min to get the data , due to that my API got timeout ,can you please help me to get the result set within 59 seconds.
Thanks

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

Please find the attached execution plan for this .
its currently taking 3 min 57 seconds due to that my API timeout.is there any way to increase the query cost and get it with in 59 seconds
https://explain.depesz.com/s/ly8c

explain plan without verbose
https://explain.depesz.com/s/KBto

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    I solved this performance issue by changing the query .Now its taking 26seconds .

    Updated query is below

    explain (analyze, buffers)
WITH group_path as 
(
    select
        group_path_resources.id,
        group_path_resources.created_at::timestamp with time zone,
        group_path_resources.context_data ->> 'xapi_activity_id'::text AS xapi_activity_id,
        group_path_resources.resourceable_id ,
        learning_record_store_user_activities.user_id,
        learning_record_store_user_activities.assigned_through_id ,
        learning_record_store_user_activities.registration::Text,
        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 group_path_resources
    LEFT JOIN learning_record_store_user_activities ON group_path_resources.resourceable_id = learning_record_store_user_activities.activity_instance_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
    AND learning_record_store_user_activities.deleted_at IS null 
),
assessment_path_resource AS 
(
 SELECT group_path.registration AS registration_id,
        group_path.id AS group_path_resource_id,
        group_path.created_at,
        group_path.user_id,
        group_path.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.xapi_activity_id AS xapi_activity_id,
        concat(COALESCE(members.first_name, ''::character varying), ' ', COALESCE(members.last_name, ''::character varying)) AS participant_full_name
   from group_path
   JOIN members ON members.user_id = group_path.user_id
   JOIN spaces ON spaces.id = group_path.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 duplicate_registration_id_row_number =1
),
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
) 
 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
            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,
        array_position
        (
            ARRAY['Not Started'::text, 'Self-assessment pending'::text, 'Feedback pending'::text, 'Eligible for release'::text, 'Released'::text], 
            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_order_index
   FROM assessment_path_resource
   LEFT JOIN as_form_response_group ON as_form_response_group.registration_id = assessment_path_resource.registration_id
   order by stage_order_index

    Updated explain plan

    https://explain.depesz.com/s/hbhI#stats


  2. 0

    I cannot give a complete answer (your query is complicated), but here is a list of things that could be improved:

    1. You have a couple of foreign table scans in the query.

      Statistics for foreign tables are not collected automatically. You can either explicitly ANALYZE the foreign tables, or you can set the use_remote_estimate option on the foreign server (assuming it is postgres_fdw), so that PostgreSQL asks the remote server for estimates.

    2. The foreign scan on assessment_service.form_response cannot push down the condition on form_response_status_type and submitter_type, because they are of a user-defined data type. Don’t use such data types for better performance.

    3. Your foreign scans are really slooooow. Investigate the cause. One idea to mitigate that would be not to join a couple of foreign tables locally. Instead, you might be able to define a view on the remote server that joins these tables and declare a single foreign table for that view.

    4. The estimate on public.learning_record_store_user_activities is very bad. Perhaps an ANALYZE on that table can help.

    5. The estimate on public.group_path_resources is horrible. The reason is probably the condition (group_path_resources.context_data ->> 'xapi_activity_id') LIKE 'https://assessment.%360%'. Extended statistics on the expression (available from v14 on) might help:

      CREATE STATISTICS group_path_resources_xapi_activity_id_stats
   ON ((context_data ->> 'xapi_activity_id'))
   FROM public.group_path_resources;

ANALYZE public.group_path_resources;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search