explain.depesz.com

PostgreSQL's explain analyze made readable

Result: hYNh

Settings
# exclusive inclusive rows x rows loops node
1. 54.624 19,753.507 ↓ 5,557.5 11,115 1

GroupAggregate (cost=1,319,996.80..1,319,996.98 rows=2 width=121) (actual time=19,693.230..19,753.507 rows=11,115 loops=1)

  • Group Key: ((xl2.changed_datetime)::date), (CASE WHEN (c.clinic_id = ANY ('{2,6}'::numeric[])) THEN 'Cork'::text WHEN (c.clinic_id = '3'::numeric) THEN 'Dundrum'::text WHEN (c.clinic_id = '4'::numeric) THEN 'Northwood'::text WHEN (c.clinic_id = '5'::numeric) THEN 'Kilkenny'::text WHEN (c.clinic_id = '7'::numeric) THEN 'Meath'::text WHEN (c.clinic_id = '8'::numeric) THEN 'Vista'::text WHEN (c.clinic_id = ANY ('{50,51,52,53,54,55,56,57,58,59,60}'::numeric[])) THEN 'HSE'::text ELSE replace((c.clinic_name_short)::text, 'Affidea '::text, ''::text) END), s.scan_name_short, ((((u.first_name)::text || ' '::text) || (u.last_name)::text)), (CASE WHEN (u.team_id = '2'::numeric) THEN 'Agent'::text WHEN (u.user_id = '282'::numeric) THEN 'Agent'::text WHEN (u.user_id = ANY ('{33,34}'::numeric[])) THEN 'Supervisor'::text WHEN (u.user_id = '130'::numeric) THEN 'RIS Originated'::text ELSE 'Other'::text END)
2. 61.692 19,698.883 ↓ 18,390.0 36,780 1

Sort (cost=1,319,996.80..1,319,996.81 rows=2 width=119) (actual time=19,693.175..19,698.883 rows=36,780 loops=1)

  • Sort Key: ((xl2.changed_datetime)::date), (CASE WHEN (c.clinic_id = ANY ('{2,6}'::numeric[])) THEN 'Cork'::text WHEN (c.clinic_id = '3'::numeric) THEN 'Dundrum'::text WHEN (c.clinic_id = '4'::numeric) THEN 'Northwood'::text WHEN (c.clinic_id = '5'::numeric) THEN 'Kilkenny'::text WHEN (c.clinic_id = '7'::numeric) THEN 'Meath'::text WHEN (c.clinic_id = '8'::numeric) THEN 'Vista'::text WHEN (c.clinic_id = ANY ('{50,51,52,53,54,55,56,57,58,59,60}'::numeric[])) THEN 'HSE'::text ELSE replace((c.clinic_name_short)::text, 'Affidea '::text, ''::text) END), s.scan_name_short, ((((u.first_name)::text || ' '::text) || (u.last_name)::text)), (CASE WHEN (u.team_id = '2'::numeric) THEN 'Agent'::text WHEN (u.user_id = '282'::numeric) THEN 'Agent'::text WHEN (u.user_id = ANY ('{33,34}'::numeric[])) THEN 'Supervisor'::text WHEN (u.user_id = '130'::numeric) THEN 'RIS Originated'::text ELSE 'Other'::text END)
  • Sort Method: quicksort Memory: 6120kB
3. 61.170 19,637.191 ↓ 18,390.0 36,780 1

Nested Loop (cost=1,158,784.87..1,319,996.79 rows=2 width=119) (actual time=13,576.278..19,637.191 rows=36,780 loops=1)

4. 40.250 19,539.241 ↓ 18,390.0 36,780 1

Nested Loop (cost=1,158,784.74..1,319,996.36 rows=2 width=83) (actual time=13,576.237..19,539.241 rows=36,780 loops=1)

5. 247.811 19,425.431 ↓ 18,390.0 36,780 1

Merge Right Join (cost=1,158,784.46..1,319,985.53 rows=2 width=62) (actual time=13,576.202..19,425.431 rows=36,780 loops=1)

  • Merge Cond: (xl1.appointment_id = a.appointment_id)
6. 206.175 12,339.900 ↓ 112.0 1,400,329 1

Subquery Scan on xl1 (cost=723,763.62..805,012.52 rows=12,500 width=14) (actual time=9,687.677..12,339.900 rows=1,400,329 loops=1)

  • Filter: (xl1.row_id = 1)
  • Rows Removed by Filter: 1067361
7. 1,353.523 12,133.725 ↑ 1.0 2,467,690 1

WindowAgg (cost=723,763.62..773,762.94 rows=2,499,966 width=102) (actual time=9,687.674..12,133.725 rows=2,467,690 loops=1)

8. 2,963.798 10,780.202 ↑ 1.0 2,467,690 1

Sort (cost=723,763.62..730,013.54 rows=2,499,966 width=28) (actual time=9,687.663..10,780.202 rows=2,467,690 loops=1)

  • Sort Key: l1.appointment_id, l1.appointment_status_change_log_id
  • Sort Method: external merge Disk: 96696kB
9. 2,263.566 7,816.404 ↑ 1.0 2,470,245 1

Hash Join (cost=203,790.78..398,283.77 rows=2,499,966 width=28) (actual time=1,795.309..7,816.404 rows=2,470,245 loops=1)

  • Hash Cond: (l1.appointment_id = a1.appointment_id)
10. 3,789.203 4,127.099 ↑ 1.0 2,470,245 1

Bitmap Heap Scan on tbl_appointment_status_change_log l1 (cost=46,687.17..189,584.75 rows=2,499,966 width=28) (actual time=364.435..4,127.099 rows=2,470,245 loops=1)

  • Recheck Cond: ((to_appointment_status_code)::text = 'N'::text)
  • Heap Blocks: exact=110921
11. 337.896 337.896 ↑ 1.0 2,470,246 1

Bitmap Index Scan on i_tbl_ascl_to_appointment_status_code (cost=0.00..46,062.18 rows=2,499,966 width=0) (actual time=337.896..337.896 rows=2,470,246 loops=1)

  • Index Cond: ((to_appointment_status_code)::text = 'N'::text)
12. 608.021 1,425.739 ↓ 1.0 2,227,641 1

Hash (cost=118,467.58..118,467.58 rows=2,222,642 width=14) (actual time=1,425.739..1,425.739 rows=2,227,641 loops=1)

  • Buckets: 1048576 Batches: 8 Memory Usage: 20949kB
13. 817.718 817.718 ↓ 1.0 2,227,641 1

Index Only Scan using pk_tbl_a_appointment_id on tbl_appointment a1 (cost=0.43..118,467.58 rows=2,222,642 width=14) (actual time=0.080..817.718 rows=2,227,641 loops=1)

  • Heap Fetches: 243223
14. 13.744 6,837.720 ↓ 18,390.0 36,780 1

Materialize (cost=435,020.84..514,941.75 rows=2 width=62) (actual time=3,827.833..6,837.720 rows=36,780 loops=1)

15. 38.059 6,823.976 ↓ 18,390.0 36,780 1

Nested Loop (cost=435,020.84..514,941.74 rows=2 width=62) (actual time=3,827.825..6,823.976 rows=36,780 loops=1)

16. 22.269 6,748.995 ↓ 12,307.3 36,922 1

Nested Loop (cost=435,020.70..514,941.23 rows=3 width=62) (actual time=3,827.813..6,748.995 rows=36,922 loops=1)

17. 11.928 6,652.882 ↓ 12,307.3 36,922 1

Nested Loop (cost=435,020.42..514,940.34 rows=3 width=62) (actual time=3,827.793..6,652.882 rows=36,922 loops=1)

18. 34.029 6,447.712 ↓ 10,735.7 32,207 1

Nested Loop (cost=435,020.00..514,938.38 rows=3 width=71) (actual time=3,827.753..6,447.712 rows=32,207 loops=1)

19. 6.634 6,381.476 ↓ 1,150.2 32,207 1

Nested Loop (cost=435,019.86..514,933.97 rows=28 width=57) (actual time=3,827.730..6,381.476 rows=32,207 loops=1)

20. 790.794 5,430.328 ↓ 813.5 34,982 1

Subquery Scan on xl2 (cost=435,019.43..514,570.35 rows=43 width=33) (actual time=3,827.692..5,430.328 rows=34,982 loops=1)

  • Filter: ((xl2.row_id = 1) AND ((((xl2.changed_datetime)::date >= (date_trunc('year'::text, (CURRENT_DATE)::timestamp with time zone))::date) AND ((xl2.changed_datetime)::date <= ((CURRENT_TIMESTAMP)::date - '1 day'::interval))) OR (((xl2.changed_datetime)::date >= ((date_trunc('year'::text, (CURRENT_DATE)::timestamp with time zone))::date - '1 year'::interval)) AND ((xl2.changed_datetime)::date <= (((CURRENT_TIMESTAMP)::date - '1 year'::interval) - '00:00:00'::interval)))))
  • Rows Removed by Filter: 799608
21. 515.223 4,639.534 ↑ 1.0 834,590 1

WindowAgg (cost=435,019.43..452,219.63 rows=860,010 width=87) (actual time=3,787.646..4,639.534 rows=834,590 loops=1)

22. 1,072.472 4,124.311 ↑ 1.0 834,590 1

Sort (cost=435,019.43..437,169.45 rows=860,010 width=47) (actual time=3,787.638..4,124.311 rows=834,590 loops=1)

  • Sort Key: l2.appointment_id, l2.appointment_status_change_log_id
  • Sort Method: external merge Disk: 52664kB
23. 1,104.025 3,051.839 ↑ 1.0 834,590 1

Hash Join (cost=173,165.12..323,791.77 rows=860,010 width=47) (actual time=1,312.547..3,051.839 rows=834,590 loops=1)

  • Hash Cond: (l2.appointment_id = a2.appointment_id)
24. 663.210 747.803 ↑ 1.0 834,590 1

Bitmap Heap Scan on tbl_appointment_status_change_log l2 (cost=16,061.51..138,459.64 rows=860,010 width=47) (actual time=109.948..747.803 rows=834,590 loops=1)

  • Recheck Cond: ((to_appointment_status_code)::text = 'C'::text)
  • Heap Blocks: exact=107316
25. 84.593 84.593 ↑ 1.0 834,590 1

Bitmap Index Scan on i_tbl_ascl_to_appointment_status_code (cost=0.00..15,846.51 rows=860,010 width=0) (actual time=84.593..84.593 rows=834,590 loops=1)

  • Index Cond: ((to_appointment_status_code)::text = 'C'::text)
26. 593.204 1,200.011 ↓ 1.0 2,227,641 1

Hash (cost=118,467.58..118,467.58 rows=2,222,642 width=14) (actual time=1,200.011..1,200.011 rows=2,227,641 loops=1)

  • Buckets: 1048576 Batches: 8 Memory Usage: 20949kB
27. 606.807 606.807 ↓ 1.0 2,227,641 1

Index Only Scan using pk_tbl_a_appointment_id on tbl_appointment a2 (cost=0.43..118,467.58 rows=2,222,642 width=14) (actual time=0.025..606.807 rows=2,227,641 loops=1)

  • Heap Fetches: 242824
28. 944.514 944.514 ↑ 1.0 1 34,982

Index Scan using pk_tbl_a_appointment_id on tbl_appointment a (cost=0.43..8.46 rows=1 width=24) (actual time=0.027..0.027 rows=1 loops=34,982)

  • Index Cond: (appointment_id = xl2.appointment_id)
  • Filter: ((appointment_status_code)::text <> ALL ('{UE,UO,US,UW,X,N,AC}'::text[]))
  • Rows Removed by Filter: 0
29. 32.207 32.207 ↑ 1.0 1 32,207

Index Scan using pk_tbl_c_clinic_id on tbl_clinic c (cost=0.14..0.16 rows=1 width=24) (actual time=0.001..0.001 rows=1 loops=32,207)

  • Index Cond: (clinic_id = COALESCE(a.clinic_id, a.preferred_clinic_id))
  • Filter: (clinic_id <> ALL ('{1,9}'::numeric[]))
30. 193.242 193.242 ↑ 1.0 1 32,207

Index Scan using i_ap_appointment_id on tbl_appointment_procedure ap (cost=0.43..0.64 rows=1 width=20) (actual time=0.005..0.006 rows=1 loops=32,207)

  • Index Cond: (appointment_id = a.appointment_id)
  • Filter: ((appointment_procedure_status_code)::text <> ALL ('{NA,NS,X,XR}'::text[]))
  • Rows Removed by Filter: 0
31. 73.844 73.844 ↑ 1.0 1 36,922

Index Scan using pk_tbl_p_procedure_id on tbl_procedure p (cost=0.28..0.30 rows=1 width=14) (actual time=0.002..0.002 rows=1 loops=36,922)

  • Index Cond: (procedure_id = ap.procedure_id)
32. 36.922 36.922 ↑ 1.0 1 36,922

Index Scan using pk_tbl_s_scan_id on tbl_scan s (cost=0.14..0.17 rows=1 width=10) (actual time=0.001..0.001 rows=1 loops=36,922)

  • Index Cond: (scan_id = p.scan_id)
  • Filter: (scan_id <> ALL ('{8,9,10,11,12}'::numeric[]))
  • Rows Removed by Filter: 0
33. 73.560 73.560 ↑ 1.0 1 36,780

Index Scan using pk_tbl_u_user_id on tbl_user u (cost=0.28..5.41 rows=1 width=32) (actual time=0.002..0.002 rows=1 loops=36,780)

  • Index Cond: (user_id = xl2.changed_by_user_id)
34. 36.780 36.780 ↑ 1.0 1 36,780

Index Only Scan using pk_tbl_t_team_id on tbl_team t (cost=0.14..0.17 rows=1 width=5) (actual time=0.001..0.001 rows=1 loops=36,780)

  • Index Cond: (team_id = u.team_id)
  • Heap Fetches: 36780