explain.depesz.com

PostgreSQL's explain analyze made readable

Result: WW3R : plan

Settings
# exclusive inclusive rows x rows loops node
1. 0.003 7.998 ↑ 5.0 1 1

Unique (cost=2,547.94..2,548.07 rows=5 width=348) (actual time=7.995..7.998 rows=1 loops=1)

2. 0.023 7.995 ↓ 1.2 6 1

Sort (cost=2,547.94..2,547.95 rows=5 width=348) (actual time=7.994..7.995 rows=6 loops=1)

  • Sort Key: (((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)
  • Sort Method: quicksort Memory: 25kB
3. 0.050 7.972 ↓ 1.2 6 1

Merge Right Join (cost=267.94..2,547.88 rows=5 width=348) (actual time=2.406..7.972 rows=6 loops=1)

  • Merge Cond: ((vc.id)::text = (v.visitclassification)::text)
4. 0.022 0.022 ↑ 22.8 4 1

Index Scan using pk_codeattribdetail_id_1934629935 on codeattribdetail vc (cost=0.14..15.52 rows=91 width=94) (actual time=0.020..0.022 rows=4 loops=1)

  • Filter: (NOT statusind)
5. 0.007 1.042 ↓ 1.2 6 1

Sort (cost=267.79..267.81 rows=5 width=34) (actual time=1.041..1.042 rows=6 loops=1)

  • Sort Key: v.visitclassification
  • Sort Method: quicksort Memory: 25kB
6. 0.001 1.035 ↓ 1.2 6 1

Nested Loop Left Join (cost=0.85..267.73 rows=5 width=34) (actual time=0.469..1.035 rows=6 loops=1)

  • Join Filter: (v.id = vp.visitid)
7. 0.005 1.010 ↑ 1.0 1 1

Nested Loop (cost=0.57..252.71 rows=1 width=34) (actual time=0.448..1.010 rows=1 loops=1)

  • Join Filter: ((v.organizationunit)::text = (ou.id)::text)
  • Rows Removed by Join Filter: 42
8. 0.000 0.998 ↑ 1.0 1 1

Nested Loop Left Join (cost=0.57..247.65 rows=1 width=37) (actual time=0.437..0.998 rows=1 loops=1)

  • Join Filter: (v.id = vt.visitid)
9. 0.002 0.447 ↑ 1.0 1 1

Nested Loop Left Join (cost=0.57..119.91 rows=1 width=37) (actual time=0.188..0.447 rows=1 loops=1)

  • Join Filter: (v.id = vd.visitid)
10. 0.002 0.041 ↑ 1.0 1 1

Nested Loop Left Join (cost=0.57..17.90 rows=1 width=37) (actual time=0.040..0.041 rows=1 loops=1)

  • Join Filter: (NOT v.statusind)
  • Filter: (SubPlan 15)
11. 0.013 0.023 ↑ 1.0 1 1

Index Scan using pk_visit_id_2014630220 on visit v (cost=0.28..8.94 rows=1 width=48) (actual time=0.022..0.023 rows=1 loops=1)

  • Index Cond: (id = '814'::bigint)
  • Filter: ((visitstartdt >= COALESCE(visitstartdt)) AND (visitstartdt <= COALESCE(visitenddt)) AND (visitenddt >= COALESCE(visitstartdt)) AND (visitenddt <= COALESCE(visitenddt)) AND (SubPlan 16))
12.          

SubPlan (for Index Scan)

13. 0.010 0.010 ↑ 100.0 1 1

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

14. 0.010 0.010 ↑ 1.0 1 1

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

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

SubPlan (for Nested Loop Left Join)

16. 0.006 0.006 ↑ 100.0 1 1

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

17. 0.404 0.404 ↑ 1.0 1 1

Seq Scan on visit_domains vd (cost=0.00..102.00 rows=1 width=8) (actual time=0.147..0.404 rows=1 loops=1)

  • Filter: ((NOT statusind) AND (visitid = '814'::bigint))
  • Rows Removed by Filter: 3,687
18. 0.551 0.551 ↑ 1.0 1 1

Seq Scan on visitor vt (cost=0.00..127.72 rows=1 width=8) (actual time=0.247..0.551 rows=1 loops=1)

  • Filter: ((NOT statusind) AND (visitid = '814'::bigint))
  • Rows Removed by Filter: 3,109
19. 0.007 0.007 ↑ 2.1 43 1

Seq Scan on codeattribdetail ou (cost=0.00..3.92 rows=91 width=2) (actual time=0.003..0.007 rows=43 loops=1)

  • Filter: ((NOT statusind) AND (codedetaildesc IS NOT NULL))
20. 0.024 0.024 ↑ 1.2 6 1

Index Scan using IX_visitpersonnel_idx_visitpersonnel_4008_4007 on visitpersonnel vp (cost=0.28..14.94 rows=7 width=10) (actual time=0.020..0.024 rows=6 loops=1)

  • Index Cond: (visitid = '814'::bigint)
  • Filter: (NOT statusind)
  • Rows Removed by Filter: 2
21.          

SubPlan (for Merge Right Join)

22. 0.024 1.830 ↑ 1.0 1 6

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

23.          

Initplan (for Result)

24. 0.006 1.806 ↑ 1.0 1 6

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

25. 0.018 1.800 ↑ 1.0 1 6

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

  • Sort Key: cd2.domaindesc
  • Sort Method: quicksort Memory: 25kB
26. 0.006 1.782 ↑ 1.0 1 6

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

  • Join Filter: (cd2.id = vd2.domainid)
  • Rows Removed by Join Filter: 3
27. 0.006 1.764 ↑ 1.0 1 6

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

  • Join Filter: (v2.id = vd2.visitid)
28. 0.036 0.036 ↑ 1.0 1 6

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

  • Index Cond: (id = v.id)
  • Heap Fetches: 6
29. 1.722 1.722 ↑ 2.0 1 6

Seq Scan on visit_domains vd2 (cost=0.00..102.00 rows=2 width=8) (actual time=0.098..0.287 rows=1 loops=6)

  • Filter: (visitid = v.id)
  • Rows Removed by Filter: 3,687
30. 0.012 0.012 ↑ 27.0 4 6

Seq Scan on domains cd2 (cost=0.00..3.08 rows=108 width=24) (actual time=0.001..0.002 rows=4 loops=6)

31. 0.012 0.216 ↑ 1.0 1 6

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

32.          

Initplan (for Result)

33. 0.006 0.204 ↓ 0.0 0 6

Unique (cost=16.23..16.24 rows=1 width=17) (actual time=0.034..0.034 rows=0 loops=6)

34. 0.012 0.198 ↓ 0.0 0 6

Sort (cost=16.23..16.24 rows=1 width=17) (actual time=0.033..0.033 rows=0 loops=6)

  • Sort Key: vp2.displayname
  • Sort Method: quicksort Memory: 25kB
35. 0.006 0.186 ↓ 0.0 0 6

Merge Join (cost=4.72..16.22 rows=1 width=17) (actual time=0.031..0.031 rows=0 loops=6)

  • Merge Cond: ((vp2.positiondesignation)::text = (pd2.id)::text)
36. 0.030 0.102 ↑ 4.0 1 6

Nested Loop (cost=0.56..23.52 rows=4 width=23) (actual time=0.017..0.017 rows=1 loops=6)

37. 0.042 0.042 ↑ 4.0 1 6

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

  • Index Cond: (visitid = v.id)
38. 0.012 0.030 ↑ 1.0 1 6

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

39. 0.018 0.018 ↑ 1.0 1 6

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

  • Index Cond: (id = v.id)
  • Filter: (NOT statusind)
40. 0.012 0.078 ↑ 1.0 1 6

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

  • Sort Key: pd2.id
  • Sort Method: quicksort Memory: 25kB
41. 0.066 0.066 ↑ 1.0 1 6

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

  • Filter: ((codedetaildesc)::text = 'Visit Sponsor/Client Partner/CAL'::text)
  • Rows Removed by Filter: 91
42. 0.012 0.180 ↑ 1.0 1 6

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

43.          

Initplan (for Result)

44. 0.006 0.168 ↑ 1.0 1 6

Unique (cost=16.23..16.24 rows=1 width=17) (actual time=0.027..0.028 rows=1 loops=6)

45. 0.012 0.162 ↑ 1.0 1 6

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

  • Sort Key: vp2_1.displayname
  • Sort Method: quicksort Memory: 25kB
46. 0.006 0.150 ↑ 1.0 1 6

Merge Join (cost=4.72..16.22 rows=1 width=17) (actual time=0.022..0.025 rows=1 loops=6)

  • Merge Cond: ((vp2_1.positiondesignation)::text = (pd2_1.id)::text)
47. 0.018 0.078 ↑ 2.0 2 6

Nested Loop (cost=0.56..23.52 rows=4 width=23) (actual time=0.011..0.013 rows=2 loops=6)

48. 0.036 0.036 ↑ 2.0 2 6

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

  • Index Cond: (visitid = v.id)
49. 0.006 0.024 ↑ 1.0 1 12

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

50. 0.018 0.018 ↑ 1.0 1 6

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

  • Index Cond: (id = v.id)
  • Filter: (NOT statusind)
51. 0.012 0.066 ↑ 1.0 1 6

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

  • Sort Key: pd2_1.id
  • Sort Method: quicksort Memory: 25kB
52. 0.054 0.054 ↑ 1.0 1 6

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

  • Filter: ((codedetaildesc)::text = 'Visit Manager'::text)
  • Rows Removed by Filter: 91
53. 0.006 0.150 ↑ 1.0 1 6

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

54.          

Initplan (for Result)

55. 0.000 0.144 ↓ 0.0 0 6

Unique (cost=16.23..16.24 rows=1 width=17) (actual time=0.024..0.024 rows=0 loops=6)

56. 0.012 0.144 ↓ 0.0 0 6

Sort (cost=16.23..16.24 rows=1 width=17) (actual time=0.024..0.024 rows=0 loops=6)

  • Sort Key: vp2_2.displayname
  • Sort Method: quicksort Memory: 25kB
57. 0.006 0.132 ↓ 0.0 0 6

Merge Join (cost=4.72..16.22 rows=1 width=17) (actual time=0.022..0.022 rows=0 loops=6)

  • Merge Cond: ((vp2_2.positiondesignation)::text = (pd2_2.id)::text)
58. 0.006 0.060 ↑ 4.0 1 6

Nested Loop (cost=0.56..23.52 rows=4 width=23) (actual time=0.010..0.010 rows=1 loops=6)

59. 0.030 0.030 ↑ 4.0 1 6

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

  • Index Cond: (visitid = v.id)
60. 0.012 0.024 ↑ 1.0 1 6

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

61. 0.012 0.012 ↑ 1.0 1 6

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

  • Index Cond: (id = v.id)
  • Filter: (NOT statusind)
62. 0.006 0.066 ↑ 1.0 1 6

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

  • Sort Key: pd2_2.id
  • Sort Method: quicksort Memory: 25kB
63. 0.060 0.060 ↑ 1.0 1 6

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

  • Filter: ((codedetaildesc)::text = 'Visit Host'::text)
  • Rows Removed by Filter: 91
64. 0.006 0.198 ↑ 1.0 1 6

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

65.          

Initplan (for Result)

66. 0.006 0.192 ↓ 0.0 0 6

Unique (cost=16.23..16.24 rows=1 width=17) (actual time=0.032..0.032 rows=0 loops=6)

67. 0.006 0.186 ↓ 0.0 0 6

Sort (cost=16.23..16.24 rows=1 width=17) (actual time=0.031..0.031 rows=0 loops=6)

  • Sort Key: vp2_3.displayname
  • Sort Method: quicksort Memory: 25kB
68. 0.012 0.180 ↓ 0.0 0 6

Merge Join (cost=4.72..16.22 rows=1 width=17) (actual time=0.030..0.030 rows=0 loops=6)

  • Merge Cond: ((vp2_3.positiondesignation)::text = (pd2_3.id)::text)
69. 0.006 0.060 ↑ 4.0 1 6

Nested Loop (cost=0.56..23.52 rows=4 width=23) (actual time=0.010..0.010 rows=1 loops=6)

70. 0.030 0.030 ↑ 4.0 1 6

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

  • Index Cond: (visitid = v.id)
71. 0.006 0.024 ↑ 1.0 1 6

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

72. 0.018 0.018 ↑ 1.0 1 6

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

  • Index Cond: (id = v.id)
  • Filter: (NOT statusind)
73. 0.006 0.108 ↑ 1.0 1 6

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

  • Sort Key: pd2_3.id
  • Sort Method: quicksort Memory: 25kB
74. 0.102 0.102 ↑ 1.0 1 6

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

  • Filter: ((codedetaildesc)::text = 'Visit Anchor'::text)
  • Rows Removed by Filter: 91
75. 0.012 2.484 ↑ 1.0 1 6

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

76.          

Initplan (for Result)

77. 0.000 2.472 ↓ 0.0 0 6

Unique (cost=140.29..140.30 rows=1 width=32) (actual time=0.412..0.412 rows=0 loops=6)

78. 0.012 2.472 ↓ 0.0 0 6

Sort (cost=140.29..140.29 rows=1 width=32) (actual time=0.412..0.412 rows=0 loops=6)

  • Sort Key: (concat(vt2.visitornm, ' - ', vt2.role))
  • Sort Method: quicksort Memory: 25kB
79. 0.000 2.460 ↓ 0.0 0 6

Nested Loop (cost=0.28..140.28 rows=1 width=32) (actual time=0.410..0.410 rows=0 loops=6)

80. 0.018 2.460 ↓ 0.0 0 6

Nested Loop (cost=0.00..131.97 rows=1 width=30) (actual time=0.410..0.410 rows=0 loops=6)

  • Join Filter: ((vt2.visitortype)::text = (vtrt2.id)::text)
  • Rows Removed by Join Filter: 3
81. 0.066 0.066 ↑ 1.0 3 6

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

  • Filter: ((NOT statusind) AND ((codedetaildesc)::text = 'Internal'::text))
  • Rows Removed by Filter: 89
82. 0.006 2.376 ↑ 2.0 1 18

Materialize (cost=0.00..127.73 rows=2 width=34) (actual time=0.055..0.132 rows=1 loops=18)

83. 2.370 2.370 ↑ 2.0 1 6

Seq Scan on visitor vt2 (cost=0.00..127.72 rows=2 width=34) (actual time=0.163..0.395 rows=1 loops=6)

  • Filter: ((NOT statusind) AND (visitid = v.id))
  • Rows Removed by Filter: 3,109
84. 0.000 0.000 ↓ 0.0 0

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

  • Index Cond: (id = v.id)
  • Filter: (NOT statusind)
85. 0.018 1.800 ↑ 1.0 1 6

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

86.          

Initplan (for Result)

87. 0.006 1.782 ↑ 1.0 1 6

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

88. 0.018 1.776 ↑ 1.0 1 6

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

  • Sort Key: (concat(vt2_1.visitornm, ' - ', vt2_1.role))
  • Sort Method: quicksort Memory: 25kB
89. 0.018 1.758 ↑ 1.0 1 6

Nested Loop (cost=25.21..135.70 rows=1 width=32) (actual time=0.173..0.293 rows=1 loops=6)

90. 0.012 1.716 ↑ 1.0 1 6

Nested Loop (cost=24.93..127.39 rows=1 width=30) (actual time=0.166..0.286 rows=1 loops=6)

91. 0.066 0.066 ↑ 1.0 1 6

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

  • Filter: ((NOT statusind) AND ((codedetaildesc)::text = 'External'::text))
  • Rows Removed by Filter: 91
92. 1.260 1.638 ↑ 1.0 1 6

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

  • Recheck Cond: ((visitortype)::text = (vtrt2_1.id)::text)
  • Filter: ((NOT statusind) AND (visitid = v.id))
  • Rows Removed by Filter: 1,112
  • Heap Blocks: exact=432
93. 0.378 0.378 ↓ 1.8 1,113 6

Bitmap Index Scan on IX_visitor_idx_visitor_5838_5837 (cost=0.00..24.93 rows=620 width=0) (actual time=0.063..0.063 rows=1,113 loops=6)

  • Index Cond: ((visitortype)::text = (vtrt2_1.id)::text)
94. 0.024 0.024 ↑ 1.0 1 6

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

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