explain.depesz.com

PostgreSQL's explain analyze made readable

Result: QmRL : a2

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 0.000 ↓ 0.0

Unique (cost=6,918.07..6,918.62 rows=11 width=1,005) (actual rows= loops=)

2. 0.000 0.000 ↓ 0.0

Sort (cost=6,918.07..6,918.09 rows=11 width=1,005) (actual rows= loops=)

  • Sort Key: v.id, c.id, c.clientnm, v.visitstartdt, v.visitenddt, ou.codedetaildesc, vst.codedetaildesc, og.ogdesc, ind.industrydesc, (((SubPlan 2))::character varying), vc.codedetaildesc, v.createdby, v.createddttm, (((SubPlan 4))::character varying), (((SubPlan 6))::character varying), (((SubPlan 8))::character varying), (((SubPlan 10))::character varying), (((SubPlan 12))::character varying), (((SubPlan 14))::character varying)
3. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=1,798.01..6,917.87 rows=11 width=1,005) (actual rows= loops=)

  • Hash Cond: ((v.visitclassification)::text = (vc.id)::text)
4. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=1,792.95..1,898.59 rows=11 width=691) (actual rows= loops=)

  • Hash Cond: ((v.industry)::bigint = ind.id)
5. 0.000 0.000 ↓ 0.0

Hash Right Join (cost=1,791.63..1,897.24 rows=11 width=475) (actual rows= loops=)

  • Hash Cond: (vd.visitid = v.id)
6. 0.000 0.000 ↓ 0.0

Seq Scan on visit_domains vd (cost=0.00..92.80 rows=3,384 width=8) (actual rows= loops=)

  • Filter: (NOT statusind)
7. 0.000 0.000 ↓ 0.0

Hash (cost=1,791.53..1,791.53 rows=8 width=475) (actual rows= loops=)

8. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=1,529.67..1,791.53 rows=8 width=475) (actual rows= loops=)

9. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=1,529.39..1,745.37 rows=5 width=475) (actual rows= loops=)

  • Hash Cond: ((v.operatinggroup)::bigint = og.id)
10. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=1,528.27..1,744.23 rows=5 width=259) (actual rows= loops=)

  • Hash Cond: ((v.visittype)::text = (vst.id)::text)
11. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=1,523.21..1,739.16 rows=5 width=169) (actual rows= loops=)

  • Join Filter: (NOT v.statusind)
  • Filter: (SubPlan 15)
12. 0.000 0.000 ↓ 0.0

Hash Join (cost=1,522.92..1,649.56 rows=10 width=145) (actual rows= loops=)

  • Hash Cond: ((v.organizationunit)::text = (ou.id)::text)
13. 0.000 0.000 ↓ 0.0

Hash Right Join (cost=1,517.87..1,644.46 rows=16 width=56) (actual rows= loops=)

  • Hash Cond: (vt.visitid = v.id)
14. 0.000 0.000 ↓ 0.0

Seq Scan on visitor vt (cost=0.00..119.98 rows=2,516 width=8) (actual rows= loops=)

  • Filter: (NOT statusind)
15. 0.000 0.000 ↓ 0.0

Hash (cost=1,517.69..1,517.69 rows=14 width=56) (actual rows= loops=)

16. 0.000 0.000 ↓ 0.0

Seq Scan on visit v (cost=0.00..1,517.69 rows=14 width=56) (actual rows= loops=)

  • Filter: ((visitstartdt >= COALESCE(visitstartdt)) AND (visitstartdt <= COALESCE(visitenddt)) AND (visitenddt >= COALESCE(visitstartdt)) AND (visitenddt <= COALESCE(visitenddt)) AND (SubPlan 16))
17.          

SubPlan (for Seq Scan)

18. 0.000 0.000 ↓ 0.0

Function Scan on unnest unnest_1 (cost=0.01..1.00 rows=100 width=32) (actual rows= loops=)

19. 0.000 0.000 ↓ 0.0

Hash (cost=3.92..3.92 rows=91 width=94) (actual rows= loops=)

20. 0.000 0.000 ↓ 0.0

Seq Scan on codeattribdetail ou (cost=0.00..3.92 rows=91 width=94) (actual rows= loops=)

  • Filter: ((NOT statusind) AND (codedetaildesc IS NOT NULL))
21. 0.000 0.000 ↓ 0.0

Index Scan using pk_client_id_1982630106 on client c (cost=0.29..8.31 rows=1 width=29) (actual rows= loops=)

  • Index Cond: (v.clientid = id)
  • Filter: (NOT statusind)
22.          

SubPlan (for Nested Loop Left Join)

23. 0.000 0.000 ↓ 0.0

Function Scan on unnest (cost=0.01..1.01 rows=100 width=32) (actual rows= loops=)

24. 0.000 0.000 ↓ 0.0

Hash (cost=3.92..3.92 rows=91 width=94) (actual rows= loops=)

25. 0.000 0.000 ↓ 0.0

Seq Scan on codeattribdetail vst (cost=0.00..3.92 rows=91 width=94) (actual rows= loops=)

  • Filter: (NOT statusind)
26. 0.000 0.000 ↓ 0.0

Hash (cost=1.07..1.07 rows=4 width=226) (actual rows= loops=)

27. 0.000 0.000 ↓ 0.0

Seq Scan on operatinggroup og (cost=0.00..1.07 rows=4 width=226) (actual rows= loops=)

  • Filter: (NOT statusind)
28. 0.000 0.000 ↓ 0.0

Index Scan using IX_visitpersonnel_idx_visitpersonnel_4008_4007 on visitpersonnel vp (cost=0.28..9.20 rows=3 width=10) (actual rows= loops=)

  • Index Cond: (v.id = visitid)
  • Filter: (NOT statusind)
29. 0.000 0.000 ↓ 0.0

Hash (cost=1.19..1.19 rows=10 width=226) (actual rows= loops=)

30. 0.000 0.000 ↓ 0.0

Seq Scan on industry ind (cost=0.00..1.19 rows=10 width=226) (actual rows= loops=)

  • Filter: (NOT statusind)
31. 0.000 0.000 ↓ 0.0

Hash (cost=3.92..3.92 rows=91 width=94) (actual rows= loops=)

32. 0.000 0.000 ↓ 0.0

Seq Scan on codeattribdetail vc (cost=0.00..3.92 rows=91 width=94) (actual rows= loops=)

  • Filter: (NOT statusind)
33.          

SubPlan (for Hash Left Join)

34. 0.000 0.000 ↓ 0.0

Result (cost=114.77..114.78 rows=1 width=32) (actual rows= loops=)

35.          

Initplan (for Result)

36. 0.000 0.000 ↓ 0.0

Unique (cost=114.76..114.77 rows=1 width=16) (actual rows= loops=)

37. 0.000 0.000 ↓ 0.0

Sort (cost=114.76..114.77 rows=1 width=16) (actual rows= loops=)

  • Sort Key: cd2.domaindesc
38. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=0.28..114.75 rows=1 width=16) (actual rows= loops=)

  • Join Filter: (cd2.id = vd2.domainid)
39. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=0.28..110.32 rows=1 width=4) (actual rows= loops=)

  • Join Filter: (v2.id = vd2.visitid)
40. 0.000 0.000 ↓ 0.0

Index Only Scan using pk_visit_id_2014630220 on visit v2 (cost=0.28..8.30 rows=1 width=8) (actual rows= loops=)

  • Index Cond: (id = v.id)
41. 0.000 0.000 ↓ 0.0

Seq Scan on visit_domains vd2 (cost=0.00..102.00 rows=2 width=8) (actual rows= loops=)

  • Filter: (visitid = v.id)
42. 0.000 0.000 ↓ 0.0

Seq Scan on domains cd2 (cost=0.00..3.08 rows=108 width=24) (actual rows= loops=)

43. 0.000 0.000 ↓ 0.0

Result (cost=16.24..16.25 rows=1 width=32) (actual rows= loops=)

44.          

Initplan (for Result)

45. 0.000 0.000 ↓ 0.0

Unique (cost=16.23..16.24 rows=1 width=17) (actual rows= loops=)

46. 0.000 0.000 ↓ 0.0

Sort (cost=16.23..16.24 rows=1 width=17) (actual rows= loops=)

  • Sort Key: vp2.displayname
47. 0.000 0.000 ↓ 0.0

Merge Join (cost=4.72..16.22 rows=1 width=17) (actual rows= loops=)

  • Merge Cond: ((vp2.positiondesignation)::text = (pd2.id)::text)
48. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.56..23.52 rows=4 width=23) (actual rows= loops=)

49. 0.000 0.000 ↓ 0.0

Index Scan using IX_visitpersonnel_idx_visitpersonnel_5787_5786 on visitpersonnel vp2 (cost=0.28..15.17 rows=4 width=27) (actual rows= loops=)

  • Index Cond: (visitid = v.id)
50. 0.000 0.000 ↓ 0.0

Materialize (cost=0.28..8.30 rows=1 width=8) (actual rows= loops=)

51. 0.000 0.000 ↓ 0.0

Index Scan using pk_visit_id_2014630220 on visit v2_1 (cost=0.28..8.30 rows=1 width=8) (actual rows= loops=)

  • Index Cond: (id = v.id)
  • Filter: (NOT statusind)
52. 0.000 0.000 ↓ 0.0

Sort (cost=4.16..4.17 rows=1 width=2) (actual rows= loops=)

  • Sort Key: pd2.id
53. 0.000 0.000 ↓ 0.0

Seq Scan on codeattribdetail pd2 (cost=0.00..4.15 rows=1 width=2) (actual rows= loops=)

  • Filter: ((codedetaildesc)::text = 'Visit Sponsor/Client Partner/CAL'::text)
54. 0.000 0.000 ↓ 0.0

Result (cost=16.24..16.25 rows=1 width=32) (actual rows= loops=)

55.          

Initplan (for Result)

56. 0.000 0.000 ↓ 0.0

Unique (cost=16.23..16.24 rows=1 width=17) (actual rows= loops=)

57. 0.000 0.000 ↓ 0.0

Sort (cost=16.23..16.24 rows=1 width=17) (actual rows= loops=)

  • Sort Key: vp2_1.displayname
58. 0.000 0.000 ↓ 0.0

Merge Join (cost=4.72..16.22 rows=1 width=17) (actual rows= loops=)

  • Merge Cond: ((vp2_1.positiondesignation)::text = (pd2_1.id)::text)
59. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.56..23.52 rows=4 width=23) (actual rows= loops=)

60. 0.000 0.000 ↓ 0.0

Index Scan using IX_visitpersonnel_idx_visitpersonnel_5787_5786 on visitpersonnel vp2_1 (cost=0.28..15.17 rows=4 width=27) (actual rows= loops=)

  • Index Cond: (visitid = v.id)
61. 0.000 0.000 ↓ 0.0

Materialize (cost=0.28..8.30 rows=1 width=8) (actual rows= loops=)

62. 0.000 0.000 ↓ 0.0

Index Scan using pk_visit_id_2014630220 on visit v2_2 (cost=0.28..8.30 rows=1 width=8) (actual rows= loops=)

  • Index Cond: (id = v.id)
  • Filter: (NOT statusind)
63. 0.000 0.000 ↓ 0.0

Sort (cost=4.16..4.17 rows=1 width=2) (actual rows= loops=)

  • Sort Key: pd2_1.id
64. 0.000 0.000 ↓ 0.0

Seq Scan on codeattribdetail pd2_1 (cost=0.00..4.15 rows=1 width=2) (actual rows= loops=)

  • Filter: ((codedetaildesc)::text = 'Visit Manager'::text)
65. 0.000 0.000 ↓ 0.0

Result (cost=16.24..16.25 rows=1 width=32) (actual rows= loops=)

66.          

Initplan (for Result)

67. 0.000 0.000 ↓ 0.0

Unique (cost=16.23..16.24 rows=1 width=17) (actual rows= loops=)

68. 0.000 0.000 ↓ 0.0

Sort (cost=16.23..16.24 rows=1 width=17) (actual rows= loops=)

  • Sort Key: vp2_2.displayname
69. 0.000 0.000 ↓ 0.0

Merge Join (cost=4.72..16.22 rows=1 width=17) (actual rows= loops=)

  • Merge Cond: ((vp2_2.positiondesignation)::text = (pd2_2.id)::text)
70. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.56..23.52 rows=4 width=23) (actual rows= loops=)

71. 0.000 0.000 ↓ 0.0

Index Scan using IX_visitpersonnel_idx_visitpersonnel_5787_5786 on visitpersonnel vp2_2 (cost=0.28..15.17 rows=4 width=27) (actual rows= loops=)

  • Index Cond: (visitid = v.id)
72. 0.000 0.000 ↓ 0.0

Materialize (cost=0.28..8.30 rows=1 width=8) (actual rows= loops=)

73. 0.000 0.000 ↓ 0.0

Index Scan using pk_visit_id_2014630220 on visit v2_3 (cost=0.28..8.30 rows=1 width=8) (actual rows= loops=)

  • Index Cond: (id = v.id)
  • Filter: (NOT statusind)
74. 0.000 0.000 ↓ 0.0

Sort (cost=4.16..4.17 rows=1 width=2) (actual rows= loops=)

  • Sort Key: pd2_2.id
75. 0.000 0.000 ↓ 0.0

Seq Scan on codeattribdetail pd2_2 (cost=0.00..4.15 rows=1 width=2) (actual rows= loops=)

  • Filter: ((codedetaildesc)::text = 'Visit Host'::text)
76. 0.000 0.000 ↓ 0.0

Result (cost=16.24..16.25 rows=1 width=32) (actual rows= loops=)

77.          

Initplan (for Result)

78. 0.000 0.000 ↓ 0.0

Unique (cost=16.23..16.24 rows=1 width=17) (actual rows= loops=)

79. 0.000 0.000 ↓ 0.0

Sort (cost=16.23..16.24 rows=1 width=17) (actual rows= loops=)

  • Sort Key: vp2_3.displayname
80. 0.000 0.000 ↓ 0.0

Merge Join (cost=4.72..16.22 rows=1 width=17) (actual rows= loops=)

  • Merge Cond: ((vp2_3.positiondesignation)::text = (pd2_3.id)::text)
81. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.56..23.52 rows=4 width=23) (actual rows= loops=)

82. 0.000 0.000 ↓ 0.0

Index Scan using IX_visitpersonnel_idx_visitpersonnel_5787_5786 on visitpersonnel vp2_3 (cost=0.28..15.17 rows=4 width=27) (actual rows= loops=)

  • Index Cond: (visitid = v.id)
83. 0.000 0.000 ↓ 0.0

Materialize (cost=0.28..8.30 rows=1 width=8) (actual rows= loops=)

84. 0.000 0.000 ↓ 0.0

Index Scan using pk_visit_id_2014630220 on visit v2_4 (cost=0.28..8.30 rows=1 width=8) (actual rows= loops=)

  • Index Cond: (id = v.id)
  • Filter: (NOT statusind)
85. 0.000 0.000 ↓ 0.0

Sort (cost=4.16..4.17 rows=1 width=2) (actual rows= loops=)

  • Sort Key: pd2_3.id
86. 0.000 0.000 ↓ 0.0

Seq Scan on codeattribdetail pd2_3 (cost=0.00..4.15 rows=1 width=2) (actual rows= loops=)

  • Filter: ((codedetaildesc)::text = 'Visit Anchor'::text)
87. 0.000 0.000 ↓ 0.0

Result (cost=140.30..140.31 rows=1 width=32) (actual rows= loops=)

88.          

Initplan (for Result)

89. 0.000 0.000 ↓ 0.0

Unique (cost=140.29..140.30 rows=1 width=32) (actual rows= loops=)

90. 0.000 0.000 ↓ 0.0

Sort (cost=140.29..140.29 rows=1 width=32) (actual rows= loops=)

  • Sort Key: (concat(vt2.visitornm, ' - ', vt2.role))
91. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.28..140.28 rows=1 width=32) (actual rows= loops=)

92. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.00..131.97 rows=1 width=30) (actual rows= loops=)

  • Join Filter: ((vt2.visitortype)::text = (vtrt2.id)::text)
93. 0.000 0.000 ↓ 0.0

Seq Scan on codeattribdetail vtrt2 (cost=0.00..4.15 rows=3 width=2) (actual rows= loops=)

  • Filter: ((NOT statusind) AND ((codedetaildesc)::text = 'Internal'::text))
94. 0.000 0.000 ↓ 0.0

Materialize (cost=0.00..127.73 rows=2 width=34) (actual rows= loops=)

95. 0.000 0.000 ↓ 0.0

Seq Scan on visitor vt2 (cost=0.00..127.72 rows=2 width=34) (actual rows= loops=)

  • Filter: ((NOT statusind) AND (visitid = v.id))
96. 0.000 0.000 ↓ 0.0

Index Scan using pk_visit_id_2014630220 on visit v2_5 (cost=0.28..8.30 rows=1 width=0) (actual rows= loops=)

  • Index Cond: (id = v.id)
  • Filter: (NOT statusind)
97. 0.000 0.000 ↓ 0.0

Result (cost=135.72..135.73 rows=1 width=32) (actual rows= loops=)

98.          

Initplan (for Result)

99. 0.000 0.000 ↓ 0.0

Unique (cost=135.71..135.72 rows=1 width=32) (actual rows= loops=)

100. 0.000 0.000 ↓ 0.0

Sort (cost=135.71..135.72 rows=1 width=32) (actual rows= loops=)

  • Sort Key: (concat(vt2_1.visitornm, ' - ', vt2_1.role))
101. 0.000 0.000 ↓ 0.0

Nested Loop (cost=25.21..135.70 rows=1 width=32) (actual rows= loops=)

102. 0.000 0.000 ↓ 0.0

Nested Loop (cost=24.93..127.39 rows=1 width=30) (actual rows= loops=)

103. 0.000 0.000 ↓ 0.0

Seq Scan on codeattribdetail vtrt2_1 (cost=0.00..4.15 rows=1 width=2) (actual rows= loops=)

  • Filter: ((NOT statusind) AND ((codedetaildesc)::text = 'External'::text))
104. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on visitor vt2_1 (cost=24.93..123.23 rows=1 width=34) (actual rows= loops=)

  • Recheck Cond: ((visitortype)::text = (vtrt2_1.id)::text)
  • Filter: ((NOT statusind) AND (visitid = v.id))
105. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on IX_visitor_idx_visitor_5838_5837 (cost=0.00..24.93 rows=620 width=0) (actual rows= loops=)

  • Index Cond: ((visitortype)::text = (vtrt2_1.id)::text)
106. 0.000 0.000 ↓ 0.0

Index Scan using pk_visit_id_2014630220 on visit v2_6 (cost=0.28..8.30 rows=1 width=0) (actual rows= loops=)

  • Index Cond: (id = v.id)
  • Filter: (NOT statusind)