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
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