explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 29bR

Settings
# exclusive inclusive rows x rows loops node
1. 3.363 47,737.254 ↓ 65.0 455 1

GroupAggregate (cost=1,255,888.91..1,255,889.51 rows=7 width=125) (actual time=47,733.775..47,737.254 rows=455 loops=1)

  • Group Key: ((xl1_1.changed_datetime)::date), a.appointment_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 (hashed SubPlan 1) THEN 'Supervisor'::text WHEN (u.user_id = '130'::numeric) THEN 'RIS Originated'::text WHEN (u.team_id = '2'::numeric) THEN 'Agent'::text ELSE 'Other'::text END)
2. 2.337 47,733.891 ↓ 110.1 771 1

Sort (cost=1,255,854.85..1,255,854.87 rows=7 width=123) (actual time=47,733.735..47,733.891 rows=771 loops=1)

  • Sort Key: a.appointment_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 (hashed SubPlan 1) THEN 'Supervisor'::text WHEN (u.user_id = '130'::numeric) THEN 'RIS Originated'::text WHEN (u.team_id = '2'::numeric) THEN 'Agent'::text ELSE 'Other'::text END)
  • Sort Method: quicksort Memory: 133kB
3. 2.492 47,731.554 ↓ 110.1 771 1

Nested Loop (cost=1,138,472.23..1,255,854.75 rows=7 width=123) (actual time=46,319.189..47,731.554 rows=771 loops=1)

4. 0.697 47,727.102 ↓ 110.1 771 1

Nested Loop (cost=1,138,438.04..1,255,819.22 rows=7 width=87) (actual time=46,318.712..47,727.102 rows=771 loops=1)

5. 0.715 47,722.550 ↓ 110.1 771 1

Hash Join (cost=1,138,437.77..1,255,773.14 rows=7 width=66) (actual time=46,318.655..47,722.550 rows=771 loops=1)

  • Hash Cond: (p.scan_id = s.scan_id)
6. 226.070 47,721.794 ↓ 55.3 774 1

Merge Right Join (cost=1,138,436.45..1,255,771.78 rows=14 width=66) (actual time=46,318.595..47,721.794 rows=774 loops=1)

  • Merge Cond: (xl1.appointment_id = a.appointment_id)
7. 219.150 42,402.886 ↓ 112.3 1,396,578 1

Subquery Scan on xl1 (cost=712,745.48..793,543.89 rows=12,431 width=14) (actual time=39,674.502..42,402.886 rows=1,396,578 loops=1)

  • Filter: (xl1.row_id = 1)
  • Rows Removed by Filter: 1065079
8. 1,398.627 42,183.736 ↑ 1.0 2,461,657 1

WindowAgg (cost=712,745.48..762,467.58 rows=2,486,105 width=102) (actual time=39,674.499..42,183.736 rows=2,461,657 loops=1)

9. 3,057.840 40,785.109 ↑ 1.0 2,461,657 1

Sort (cost=712,745.48..718,960.74 rows=2,486,105 width=28) (actual time=39,674.489..40,785.109 rows=2,461,657 loops=1)

  • Sort Key: l1.appointment_id, l1.appointment_status_change_log_id
  • Sort Method: external merge Disk: 96360kB
10. 2,157.375 37,727.269 ↑ 1.0 2,461,684 1

Hash Join (cost=195,703.46..389,170.81 rows=2,486,105 width=28) (actual time=34,185.468..37,727.269 rows=2,461,684 loops=1)

  • Hash Cond: (l1.appointment_id = a1.appointment_id)
11. 1,417.222 1,786.173 ↑ 1.0 2,461,684 1

Bitmap Heap Scan on tbl_appointment_status_change_log l1 (cost=46,443.75..188,549.06 rows=2,486,105 width=28) (actual time=396.983..1,786.173 rows=2,461,684 loops=1)

  • Recheck Cond: ((to_appointment_status_code)::text = 'N'::text)
  • Heap Blocks: exact=110361
12. 368.951 368.951 ↑ 1.0 2,461,685 1

Bitmap Index Scan on i_tbl_ascl_to_appointment_status_code (cost=0.00..45,822.22 rows=2,486,105 width=0) (actual time=368.951..368.951 rows=2,461,685 loops=1)

  • Index Cond: ((to_appointment_status_code)::text = 'N'::text)
13. 1,776.901 33,783.721 ↓ 1.0 2,222,155 1

Hash (cost=110,650.35..110,650.35 rows=2,221,069 width=14) (actual time=33,783.720..33,783.721 rows=2,222,155 loops=1)

  • Buckets: 1048576 Batches: 8 Memory Usage: 20916kB
14. 32,006.820 32,006.820 ↓ 1.0 2,222,155 1

Index Only Scan using pk_tbl_a_appointment_id on tbl_appointment a1 (cost=0.43..110,650.35 rows=2,221,069 width=14) (actual time=0.088..32,006.820 rows=2,222,155 loops=1)

  • Heap Fetches: 221077
15. 0.530 5,092.838 ↓ 55.3 774 1

Materialize (cost=425,658.46..462,196.76 rows=14 width=66) (actual time=4,430.354..5,092.838 rows=774 loops=1)

16. 0.965 5,092.308 ↓ 55.3 774 1

Nested Loop (cost=425,658.46..462,196.73 rows=14 width=66) (actual time=4,430.349..5,092.308 rows=774 loops=1)

17. 0.805 5,088.247 ↓ 55.3 774 1

Nested Loop (cost=425,658.18..462,192.59 rows=14 width=66) (actual time=4,430.311..5,088.247 rows=774 loops=1)

18. 0.798 4,711.514 ↓ 57.2 686 1

Nested Loop (cost=425,657.75..462,184.76 rows=12 width=75) (actual time=4,430.258..4,711.514 rows=686 loops=1)

19. 0.572 4,709.344 ↓ 49.0 686 1

Nested Loop (cost=425,657.62..462,182.55 rows=14 width=56) (actual time=4,430.245..4,709.344 rows=686 loops=1)

20. 249.443 4,702.328 ↓ 34.1 716 1

Subquery Scan on xl1_1 (cost=425,657.19..462,004.97 rows=21 width=33) (actual time=4,430.183..4,702.328 rows=716 loops=1)

  • Filter: ((xl1_1.row_id = 1) AND ((xl1_1.changed_datetime)::date = ('now'::cstring)::date))
  • Rows Removed by Filter: 830904
21. 516.209 4,452.885 ↑ 1.0 831,620 1

WindowAgg (cost=425,657.19..442,762.03 rows=855,242 width=87) (actual time=3,591.752..4,452.885 rows=831,620 loops=1)

22. 1,093.501 3,936.676 ↑ 1.0 831,620 1

Sort (cost=425,657.19..427,795.29 rows=855,242 width=47) (actual time=3,591.740..3,936.676 rows=831,620 loops=1)

  • Sort Key: l1_1.appointment_id, l1_1.appointment_status_change_log_id
  • Sort Method: external merge Disk: 52480kB
23. 1,143.035 2,843.175 ↑ 1.0 831,620 1

Hash Join (cost=165,236.27..315,080.81 rows=855,242 width=47) (actual time=1,249.086..2,843.175 rows=831,620 loops=1)

  • Hash Cond: (l1_1.appointment_id = a1_1.appointment_id)
24. 482.426 571.866 ↑ 1.0 831,620 1

Bitmap Heap Scan on tbl_appointment_status_change_log l1_1 (cost=15,976.56..137,696.09 rows=855,242 width=47) (actual time=118.074..571.866 rows=831,620 loops=1)

  • Recheck Cond: ((to_appointment_status_code)::text = 'C'::text)
  • Heap Blocks: exact=106788
25. 89.440 89.440 ↑ 1.0 831,620 1

Bitmap Index Scan on i_tbl_ascl_to_appointment_status_code (cost=0.00..15,762.75 rows=855,242 width=0) (actual time=89.440..89.440 rows=831,620 loops=1)

  • Index Cond: ((to_appointment_status_code)::text = 'C'::text)
26. 595.502 1,128.274 ↓ 1.0 2,222,155 1

Hash (cost=110,650.35..110,650.35 rows=2,221,069 width=14) (actual time=1,128.274..1,128.274 rows=2,222,155 loops=1)

  • Buckets: 1048576 Batches: 8 Memory Usage: 20916kB
27. 532.772 532.772 ↓ 1.0 2,222,155 1

Index Only Scan using pk_tbl_a_appointment_id on tbl_appointment a1_1 (cost=0.43..110,650.35 rows=2,221,069 width=14) (actual time=0.030..532.772 rows=2,222,155 loops=1)

  • Heap Fetches: 220783
28. 6.444 6.444 ↑ 1.0 1 716

Index Scan using pk_tbl_a_appointment_id on tbl_appointment a (cost=0.43..8.46 rows=1 width=23) (actual time=0.009..0.009 rows=1 loops=716)

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

Index Scan using pk_tbl_c_clinic_id on tbl_clinic c (cost=0.14..0.16 rows=1 width=24) (actual time=0.002..0.002 rows=1 loops=686)

  • Index Cond: (clinic_id = a.clinic_id)
  • Filter: (clinic_id <> ALL ('{1,9}'::numeric[]))
30. 375.928 375.928 ↑ 1.0 1 686

Index Scan using i_ap_appointment_id on tbl_appointment_procedure ap (cost=0.43..0.64 rows=1 width=20) (actual time=0.488..0.548 rows=1 loops=686)

  • 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. 3.096 3.096 ↑ 1.0 1 774

Index Scan using pk_tbl_p_procedure_id on tbl_procedure p (cost=0.28..0.30 rows=1 width=14) (actual time=0.004..0.004 rows=1 loops=774)

  • Index Cond: (procedure_id = ap.procedure_id)
32. 0.009 0.041 ↓ 1.1 8 1

Hash (cost=1.23..1.23 rows=7 width=10) (actual time=0.041..0.041 rows=8 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
33. 0.032 0.032 ↓ 1.1 8 1

Seq Scan on tbl_scan s (cost=0.00..1.23 rows=7 width=10) (actual time=0.015..0.032 rows=8 loops=1)

  • Filter: (scan_id <> ALL ('{8,9,10,11,12,14}'::numeric[]))
  • Rows Removed by Filter: 6
34. 3.855 3.855 ↑ 1.0 1 771

Index Scan using pk_tbl_u_user_id on tbl_user u (cost=0.28..6.58 rows=1 width=32) (actual time=0.005..0.005 rows=1 loops=771)

  • Index Cond: (user_id = xl1_1.changed_by_user_id)
35. 1.542 1.542 ↑ 1.0 1 771

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.002..0.002 rows=1 loops=771)

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

SubPlan (for Nested Loop)

37. 0.418 0.418 ↑ 1.0 7 1

Index Only Scan using pk_tbl_u_user_id on tbl_user (cost=0.28..34.04 rows=7 width=12) (actual time=0.388..0.418 rows=7 loops=1)

  • Index Cond: (user_id = ANY ('{33,14756728545001000000700,34,14757892747101000000704,95,139,14757889462701000000703}'::numeric[]))
  • Heap Fetches: 1