explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 8bl5

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

CTE Scan on view (cost=49,035,924.46..49,035,925.46 rows=50 width=296) (actual time=161.243..161.243 rows=0 loops=1)

2.          

CTE base

3. 1.001 160.751 ↑ 19,110.0 1 1

Hash Join (cost=49,026,820.62..49,027,948.11 rows=19,110 width=16) (actual time=160.751..160.751 rows=1 loops=1)

  • Hash Cond: (pit."PERSON_ID" = person_1.id)
4.          

CTE pit

5. 0.002 41.607 ↑ 38,220.0 1 1

Nested Loop (cost=49,102.06..49,003,579.90 rows=38,220 width=16) (actual time=41.607..41.607 rows=1 loops=1)

6. 0.012 41.596 ↑ 50,400.0 1 1

Hash Anti Join (cost=49,101.64..48,588,038.05 rows=50,400 width=16) (actual time=41.596..41.596 rows=1 loops=1)

  • Hash Cond: (alias_755097.person_id = person_to_person_type.person_id)
7. 0.196 41.082 ↑ 137,760.0 1 1

Nested Loop (cost=42,601.10..48,580,574.16 rows=137,760 width=16) (actual time=41.082..41.082 rows=1 loops=1)

8. 0.073 0.590 ↑ 2.0 552 1

Nested Loop (cost=27.70..93.21 rows=1,120 width=103) (actual time=0.250..0.590 rows=552 loops=1)

  • Join Filter: (organizational_unit.organizational_hierarchy_id = organizational_hierarchy.id)
9. 0.005 0.005 ↑ 1.0 1 1

Seq Scan on organizational_hierarchy (cost=0.00..1.01 rows=1 width=16) (actual time=0.005..0.005 rows=1 loops=1)

  • Filter: (state = 'ACTIVE'::text)
10. 0.195 0.512 ↑ 2.0 552 1

Hash Right Join (cost=27.70..78.20 rows=1,120 width=119) (actual time=0.243..0.512 rows=552 loops=1)

  • Hash Cond: (person_assignment.organizational_unit_id = organizational_unit.id)
11. 0.086 0.086 ↑ 2.2 562 1

Seq Scan on person_assignment (cost=0.00..47.25 rows=1,225 width=39) (actual time=0.004..0.086 rows=562 loops=1)

12. 0.086 0.231 ↑ 1.0 447 1

Hash (cost=22.11..22.11 rows=447 width=112) (actual time=0.231..0.231 rows=447 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 72kB
13. 0.145 0.145 ↑ 1.0 447 1

Seq Scan on organizational_unit (cost=0.00..22.11 rows=447 width=112) (actual time=0.004..0.145 rows=447 loops=1)

  • Filter: (state = 'ACTIVE'::text)
  • Rows Removed by Filter: 42
14. 0.000 40.296 ↓ 0.0 0 552

Subquery Scan on alias_755097 (cost=42,573.40..43,374.20 rows=123 width=16) (actual time=0.073..0.073 rows=0 loops=552)

  • Filter: (alias_755097.r = 1)
15. 0.000 40.296 ↓ 0.0 0 552

WindowAgg (cost=42,573.40..43,066.20 rows=24,640 width=44) (actual time=0.073..0.073 rows=0 loops=552)

16. 3.864 40.296 ↓ 0.0 0 552

Sort (cost=42,573.40..42,635.00 rows=24,640 width=20) (actual time=0.073..0.073 rows=0 loops=552)

  • Sort Key: employee.person_id, employee.sort_code
  • Sort Method: quicksort Memory: 1167kB
17. 1.219 36.432 ↑ 1,368.9 18 552

Result (cost=216.17..40,776.07 rows=24,640 width=20) (actual time=0.002..0.066 rows=18 loops=552)

  • One-Time Filter: ((person_assignment.state = 'ACTIVE'::text) AND (person_assignment.person_id = '2af3cd7b-7636-4205-a299-8b8fa8081d96'::uuid))
18. 1.939 35.213 ↑ 2.5 10,011 1

Nested Loop (cost=216.17..40,776.07 rows=24,640 width=20) (actual time=1.148..35.213 rows=10,011 loops=1)

19. 0.557 7.750 ↑ 2.3 4,254 1

Nested Loop (cost=215.74..33,555.68 rows=9,585 width=16) (actual time=1.135..7.750 rows=4,254 loops=1)

20. 0.141 0.141 ↑ 2.0 1 1

Seq Scan on organizational_unit ou2 (cost=0.00..23.34 rows=2 width=16) (actual time=0.112..0.141 rows=1 loops=1)

  • Filter: ((materialized_path @> organizational_unit.materialized_path) AND (state = 'ACTIVE'::text))
  • Rows Removed by Filter: 488
21. 6.538 7.052 ↑ 1.6 4,254 1

Bitmap Heap Scan on organization_assignment (cost=215.74..16,699.97 rows=6,620 width=32) (actual time=1.021..7.052 rows=4,254 loops=1)

  • Recheck Cond: ((organizational_unit_id = ou2.id) AND (state = 'ACTIVE'::text))
  • Heap Blocks: exact=4014
22. 0.514 0.514 ↑ 1.6 4,254 1

Bitmap Index Scan on organizational_assignment_org_unit_id_org_id_idx (cost=0.00..214.08 rows=6,620 width=0) (actual time=0.514..0.514 rows=4,254 loops=1)

  • Index Cond: (organizational_unit_id = ou2.id)
23. 25.524 25.524 ↑ 5.0 2 4,254

Index Scan using employee_organization_id_state_idx on employee (cost=0.42..0.65 rows=10 width=36) (actual time=0.004..0.006 rows=2 loops=4,254)

  • Index Cond: ((organization_id = organization_assignment.organization_id) AND (state = 'ACTIVE'::text))
24. 0.084 0.502 ↑ 11.9 627 1

Hash (cost=6,407.45..6,407.45 rows=7,447 width=16) (actual time=0.501..0.502 rows=627 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 94kB
25. 0.072 0.418 ↑ 11.9 627 1

Nested Loop (cost=276.76..6,407.45 rows=7,447 width=16) (actual time=0.070..0.418 rows=627 loops=1)

26. 0.019 0.019 ↑ 1.0 1 1

Seq Scan on person_type (cost=0.00..2.60 rows=1 width=16) (actual time=0.014..0.019 rows=1 loops=1)

  • Filter: (base_type = 'INTERNAL'::text)
  • Rows Removed by Filter: 47
27. 0.293 0.327 ↑ 19.0 627 1

Bitmap Heap Scan on person_to_person_type (cost=276.76..6,285.70 rows=11,915 width=32) (actual time=0.052..0.327 rows=627 loops=1)

  • Recheck Cond: (person_type_id = person_type.id)
  • Heap Blocks: exact=170
28. 0.034 0.034 ↑ 19.0 627 1

Bitmap Index Scan on person_to_person_type_person_type_idx (cost=0.00..273.79 rows=11,915 width=0) (actual time=0.034..0.034 rows=627 loops=1)

  • Index Cond: (person_type_id = person_type.id)
29. 0.009 0.009 ↑ 1.0 1 1

Index Only Scan using person_id_active_idx on person (cost=0.42..8.24 rows=1 width=16) (actual time=0.009..0.009 rows=1 loops=1)

  • Index Cond: (id = alias_755097.person_id)
  • Heap Fetches: 1
30. 0.007 41.984 ↑ 19,110.0 1 1

Hash Anti Join (cost=6,500.54..7,577.86 rows=19,110 width=16) (actual time=41.983..41.984 rows=1 loops=1)

  • Hash Cond: (pit."PERSON_ID" = person_to_person_type_1.person_id)
31. 41.608 41.608 ↑ 38,220.0 1 1

CTE Scan on pit (cost=0.00..764.40 rows=38,220 width=16) (actual time=41.608..41.608 rows=1 loops=1)

32. 0.086 0.369 ↑ 11.9 627 1

Hash (cost=6,407.45..6,407.45 rows=7,447 width=16) (actual time=0.369..0.369 rows=627 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 94kB
33. 0.071 0.283 ↑ 11.9 627 1

Nested Loop (cost=276.76..6,407.45 rows=7,447 width=16) (actual time=0.046..0.283 rows=627 loops=1)

34. 0.008 0.008 ↑ 1.0 1 1

Seq Scan on person_type person_type_1 (cost=0.00..2.60 rows=1 width=16) (actual time=0.004..0.008 rows=1 loops=1)

  • Filter: (base_type = 'INTERNAL'::text)
  • Rows Removed by Filter: 47
35. 0.178 0.204 ↑ 19.0 627 1

Bitmap Heap Scan on person_to_person_type person_to_person_type_1 (cost=276.76..6,285.70 rows=11,915 width=32) (actual time=0.041..0.204 rows=627 loops=1)

  • Recheck Cond: (person_type_id = person_type_1.id)
  • Heap Blocks: exact=170
36. 0.026 0.026 ↑ 19.0 627 1

Bitmap Index Scan on person_to_person_type_person_type_idx (cost=0.00..273.79 rows=11,915 width=0) (actual time=0.026..0.026 rows=627 loops=1)

  • Index Cond: (person_type_id = person_type_1.id)
37. 65.801 117.766 ↑ 1.0 358,808 1

Hash (cost=12,255.08..12,255.08 rows=358,808 width=16) (actual time=117.766..117.766 rows=358,808 loops=1)

  • Buckets: 524288 Batches: 1 Memory Usage: 20916kB
38. 51.965 51.965 ↑ 1.0 358,808 1

Seq Scan on person person_1 (cost=0.00..12,255.08 rows=358,808 width=16) (actual time=0.007..51.965 rows=358,808 loops=1)

39.          

CTE list-condition-0

40. 0.014 161.228 ↓ 0.0 0 1

Hash Join (cost=1,395.45..1,849.32 rows=3,623 width=16) (actual time=161.228..161.228 rows=0 loops=1)

  • Hash Cond: (base.id = person_2.id)
41. 160.753 160.753 ↑ 19,110.0 1 1

CTE Scan on base (cost=0.00..382.20 rows=19,110 width=16) (actual time=160.753..160.753 rows=1 loops=1)

42. 0.001 0.461 ↓ 0.0 0 1

Hash (cost=1,395.44..1,395.44 rows=1 width=48) (actual time=0.461..0.461 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
43. 0.000 0.460 ↓ 0.0 0 1

Nested Loop Semi Join (cost=1,374.48..1,395.44 rows=1 width=48) (actual time=0.460..0.460 rows=0 loops=1)

44. 0.001 0.460 ↓ 0.0 0 1

Nested Loop (cost=1,373.93..1,390.40 rows=2 width=32) (actual time=0.460..0.460 rows=0 loops=1)

45. 0.000 0.459 ↓ 0.0 0 1

Unique (cost=1,373.51..1,373.52 rows=2 width=16) (actual time=0.459..0.459 rows=0 loops=1)

46. 0.010 0.459 ↓ 0.0 0 1

Sort (cost=1,373.51..1,373.51 rows=2 width=16) (actual time=0.459..0.459 rows=0 loops=1)

  • Sort Key: attribute_value.target_id
  • Sort Method: quicksort Memory: 25kB
47. 0.449 0.449 ↓ 0.0 0 1

Index Scan using idx_attribute_value_1 on attribute_value (cost=0.43..1,373.50 rows=2 width=16) (actual time=0.449..0.449 rows=0 loops=1)

  • Index Cond: (attribute_id = 'ba8d4720-ef02-4dc2-8c6f-683d16d63c5e'::uuid)
  • Filter: ((type = 'PERSON'::text) AND (lower(value) = 'true'::text))
48. 0.000 0.000 ↓ 0.0 0

Index Only Scan using person_id_state_idx on person person_2 (cost=0.42..8.44 rows=1 width=16) (never executed)

  • Index Cond: (id = attribute_value.target_id)
  • Heap Fetches: 0
49. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_attribute_value on attribute_value attribute_value_1 (cost=0.56..1.87 rows=1 width=16) (never executed)

  • Index Cond: ((target_id = person_2.id) AND (type = 'PERSON'::text) AND (attribute_id = 'da99ffba-6349-09bf-4ebf-43073faf1431'::uuid))
  • Filter: (lower(value) = 'false'::text)
50.          

CTE match

51. 0.005 161.238 ↓ 0.0 0 1

Sort (cost=1,781.16..1,781.66 rows=200 width=48) (actual time=161.238..161.238 rows=0 loops=1)

  • Sort Key: ((SubPlan 4))
  • Sort Method: quicksort Memory: 25kB
52. 0.001 161.233 ↓ 0.0 0 1

Subquery Scan on alias_87432128 (cost=81.52..1,773.52 rows=200 width=48) (actual time=161.233..161.233 rows=0 loops=1)

53. 0.004 161.232 ↓ 0.0 0 1

HashAggregate (cost=81.52..83.52 rows=200 width=16) (actual time=161.232..161.232 rows=0 loops=1)

  • Group Key: "list-condition-0".id
54. 161.228 161.228 ↓ 0.0 0 1

CTE Scan on "list-condition-0" (cost=0.00..72.46 rows=3,623 width=16) (actual time=161.228..161.228 rows=0 loops=1)

55.          

SubPlan (for Subquery Scan)

56. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_person on person person_3 (cost=0.42..8.44 rows=1 width=8) (never executed)

  • Index Cond: (id = alias_87432128.record_id)
57.          

CTE view

58. 0.001 161.242 ↓ 0.0 0 1

Limit (cost=0.00..4,345.36 rows=50 width=296) (actual time=161.242..161.242 rows=0 loops=1)

59. 0.002 161.241 ↓ 0.0 0 1

WindowAgg (cost=0.00..17,381.45 rows=200 width=296) (actual time=161.241..161.241 rows=0 loops=1)

60. 161.239 161.239 ↓ 0.0 0 1

CTE Scan on match (cost=0.00..4.00 rows=200 width=48) (actual time=161.239..161.239 rows=0 loops=1)

61.          

SubPlan (for WindowAgg)

62. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_person on person person_4 (cost=0.42..8.44 rows=1 width=7) (never executed)

  • Index Cond: (id = match.record_id)
63. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_person on person person_5 (cost=0.42..8.44 rows=1 width=13) (never executed)

  • Index Cond: (id = match.record_id)
64. 0.000 0.000 ↓ 0.0 0

Limit (cost=16.89..16.90 rows=1 width=23) (never executed)

65. 0.000 0.000 ↓ 0.0 0

Sort (cost=16.89..16.90 rows=1 width=23) (never executed)

  • Sort Key: employee_1.sort_code
66. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.84..16.88 rows=1 width=23) (never executed)

67. 0.000 0.000 ↓ 0.0 0

Index Scan using employee_person_id_state_idx on employee employee_1 (cost=0.42..8.45 rows=1 width=20) (never executed)

  • Index Cond: ((person_id = match.record_id) AND (state = 'ACTIVE'::text))
68. 0.000 0.000 ↓ 0.0 0

Index Scan using organization_id_state_idx on organization (cost=0.42..8.44 rows=1 width=35) (never executed)

  • Index Cond: (id = employee_1.organization_id)
69. 0.000 0.000 ↓ 0.0 0

Limit (cost=17.97..17.97 rows=1 width=16) (never executed)

70. 0.000 0.000 ↓ 0.0 0

Sort (cost=17.97..17.97 rows=1 width=16) (never executed)

  • Sort Key: employee_2.sort_code
71. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=1.68..17.96 rows=1 width=16) (never executed)

72. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=1.26..17.44 rows=1 width=20) (never executed)

73. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.84..16.88 rows=1 width=36) (never executed)

74. 0.000 0.000 ↓ 0.0 0

Index Scan using employee_person_id_state_idx on employee employee_2 (cost=0.42..8.45 rows=1 width=20) (never executed)

  • Index Cond: ((person_id = match.record_id) AND (state = 'ACTIVE'::text))
75. 0.000 0.000 ↓ 0.0 0

Index Only Scan using organization_id_state_idx on organization organization_1 (cost=0.42..8.44 rows=1 width=16) (never executed)

  • Index Cond: (id = employee_2.organization_id)
  • Heap Fetches: 0
76. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_organization_address on organization_address (cost=0.42..0.55 rows=1 width=32) (never executed)

  • Index Cond: ((organization_id = organization_1.id) AND (address_type = 'STANDARD'::text))
77. 0.000 0.000 ↓ 0.0 0

Index Scan using address_pkey on address (cost=0.42..0.52 rows=1 width=28) (never executed)

  • Index Cond: (id = organization_address.address_id)
78. 0.000 0.000 ↓ 0.0 0

Limit (cost=17.96..17.96 rows=1 width=22) (never executed)

79. 0.000 0.000 ↓ 0.0 0

Sort (cost=17.96..17.96 rows=1 width=22) (never executed)

  • Sort Key: employee_3.sort_code
80. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=1.40..17.95 rows=1 width=22) (never executed)

81. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=1.27..17.62 rows=2 width=38) (never executed)

82. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.84..16.88 rows=1 width=36) (never executed)

83. 0.000 0.000 ↓ 0.0 0

Index Scan using employee_person_id_state_idx on employee employee_3 (cost=0.42..8.45 rows=1 width=20) (never executed)

  • Index Cond: ((person_id = match.record_id) AND (state = 'ACTIVE'::text))
84. 0.000 0.000 ↓ 0.0 0

Index Only Scan using organization_id_state_idx on organization organization_2 (cost=0.42..8.44 rows=1 width=16) (never executed)

  • Index Cond: (id = employee_3.organization_id)
  • Heap Fetches: 0
85. 0.000 0.000 ↓ 0.0 0

Index Scan using organization_communication_data_organization_id_idx on organization_communication_data (cost=0.42..0.72 rows=2 width=50) (never executed)

  • Index Cond: (organization_id = organization_2.id)
86. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_communication_data_type on communication_data_type (cost=0.14..0.16 rows=1 width=16) (never executed)

  • Index Cond: (id = organization_communication_data.communication_data_type_id)
  • Filter: (base_type = 'PHONE'::text)
87. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_attribute_value on attribute_value attribute_value_2 (cost=0.56..8.58 rows=1 width=17) (never executed)

  • Index Cond: ((target_id = match.record_id) AND (type = 'PERSON'::text) AND (attribute_id = 'ba8d4720-ef02-4dc2-8c6f-683d16d63c5e'::uuid))
88. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_attribute_value on attribute_value attribute_value_3 (cost=0.56..8.58 rows=1 width=17) (never executed)

  • Index Cond: ((target_id = match.record_id) AND (type = 'PERSON'::text) AND (attribute_id = 'da99ffba-6349-09bf-4ebf-43073faf1431'::uuid))
Planning time : 9.714 ms
Execution time : 161.659 ms