explain.depesz.com

PostgreSQL's explain analyze made readable

Result: HIwv

Settings
# exclusive inclusive rows x rows loops node
1. 32.886 32.886 ↓ 10.0 10 1

CTE Scan on view (cost=238.43..238.45 rows=1 width=296) (actual time=32.405..32.886 rows=10 loops=1)

2.          

CTE base

3. 0.055 31.414 ↓ 156.0 156 1

Nested Loop (cost=124.83..141.24 rows=1 width=16) (actual time=6.403..31.414 rows=156 loops=1)

4.          

CTE pit

5. 0.020 30.851 ↓ 156.0 156 1

Nested Loop (cost=81.91..124.27 rows=1 width=16) (actual time=6.391..30.851 rows=156 loops=1)

6. 0.038 30.675 ↓ 156.0 156 1

Nested Loop (cost=81.77..116.09 rows=1 width=32) (actual time=6.386..30.675 rows=156 loops=1)

7. 1.173 30.481 ↓ 156.0 156 1

Nested Loop (cost=81.62..107.91 rows=1 width=32) (actual time=6.382..30.481 rows=156 loops=1)

  • Join Filter: (organizational_unit.id = person_assignment.organizational_unit_id)
  • Rows Removed by Join Filter: 1716
8. 0.223 21.820 ↓ 1,872.0 1,872 1

Nested Loop (cost=81.62..106.47 rows=1 width=48) (actual time=0.806..21.820 rows=1,872 loops=1)

9. 0.017 0.017 ↓ 26.0 26 1

Index Scan using idx_organizational_unit_state on organizational_unit (cost=0.15..8.17 rows=1 width=64) (actual time=0.008..0.017 rows=26 loops=1)

  • Index Cond: (state = 'ACTIVE'::text)
10. 0.364 21.580 ↓ 72.0 72 26

Nested Loop Anti Join (cost=81.47..98.29 rows=1 width=16) (actual time=0.577..0.830 rows=72 loops=26)

11. 0.442 17.472 ↓ 72.0 72 26

Subquery Scan on alias_755097 (cost=81.18..81.28 rows=1 width=16) (actual time=0.574..0.672 rows=72 loops=26)

  • Filter: (alias_755097.r = 1)
  • Rows Removed by Filter: 76
12. 1.742 17.030 ↓ 49.3 148 26

WindowAgg (cost=81.18..81.24 rows=3 width=20) (actual time=0.574..0.655 rows=148 loops=26)

13. 1.456 15.288 ↓ 49.3 148 26

Sort (cost=81.18..81.19 rows=3 width=20) (actual time=0.571..0.588 rows=148 loops=26)

  • Sort Key: employee.person_id, employee.sort_code
  • Sort Method: quicksort Memory: 90kB
14. 0.000 13.832 ↓ 49.3 148 26

Nested Loop (cost=20.17..81.16 rows=3 width=20) (actual time=0.032..0.532 rows=148 loops=26)

15. 1.086 4.030 ↓ 49.5 396 26

Nested Loop (cost=19.90..78.12 rows=8 width=16) (actual time=0.029..0.155 rows=396 loops=26)

16. 0.312 0.312 ↓ 4.0 4 26

Index Scan using idx_organizational_unit_state on organizational_unit ou2 (cost=0.15..8.17 rows=1 width=16) (actual time=0.005..0.012 rows=4 loops=26)

  • Index Cond: (state = 'ACTIVE'::text)
  • Filter: (materialized_path @> organizational_unit.materialized_path)
  • Rows Removed by Filter: 22
17. 1.786 2.632 ↑ 4.1 110 94

Bitmap Heap Scan on organization_assignment (cost=19.75..65.47 rows=448 width=32) (actual time=0.010..0.028 rows=110 loops=94)

  • Recheck Cond: ((organizational_unit_id = ou2.id) AND (state = 'ACTIVE'::text))
  • Heap Blocks: exact=897
18. 0.846 0.846 ↑ 4.1 110 94

Bitmap Index Scan on organizational_assignment_org_unit_id_org_id_idx (cost=0.00..19.64 rows=448 width=0) (actual time=0.009..0.009 rows=110 loops=94)

  • Index Cond: (organizational_unit_id = ou2.id)
19. 10.304 10.304 ↓ 0.0 0 10,304

Index Scan using employee_organization_id_state_idx on employee (cost=0.27..0.35 rows=3 width=36) (actual time=0.001..0.001 rows=0 loops=10,304)

  • Index Cond: ((organization_id = organization_assignment.organization_id) AND (state = 'ACTIVE'::text))
20. 0.000 3.744 ↓ 0.0 0 1,872

Nested Loop (cost=0.29..8.65 rows=1 width=16) (actual time=0.002..0.002 rows=0 loops=1,872)

21. 1.872 1.872 ↑ 1.0 1 1,872

Index Scan using person_to_person_type_person_idx on person_to_person_type (cost=0.14..8.16 rows=1 width=32) (actual time=0.001..0.001 rows=1 loops=1,872)

  • Index Cond: (person_id = alias_755097.person_id)
22. 1.872 1.872 ↓ 0.0 0 1,872

Index Scan using person_type_pkey on person_type (cost=0.15..0.48 rows=1 width=16) (actual time=0.001..0.001 rows=0 loops=1,872)

  • Index Cond: (id = person_to_person_type.person_type_id)
  • Filter: (base_type = 'INTERNAL'::text)
  • Rows Removed by Filter: 1
23. 7.488 7.488 ↑ 1.0 1 1,872

Seq Scan on person_assignment (cost=0.00..1.44 rows=1 width=16) (actual time=0.003..0.004 rows=1 loops=1,872)

  • Filter: ((state = 'ACTIVE'::text) AND (person_id = '8e459ecd-2208-44b9-836a-b9bbf182ec03'::uuid))
  • Rows Removed by Filter: 28
24. 0.156 0.156 ↑ 1.0 1 156

Index Scan using person_id_active_idx on person (cost=0.14..8.16 rows=1 width=16) (actual time=0.001..0.001 rows=1 loops=156)

  • Index Cond: (id = alias_755097.person_id)
25. 0.156 0.156 ↑ 1.0 1 156

Index Scan using pk_organizational_hierarchy on organizational_hierarchy (cost=0.15..8.17 rows=1 width=16) (actual time=0.001..0.001 rows=1 loops=156)

  • Index Cond: (id = organizational_unit.organizational_hierarchy_id)
  • Filter: (state = 'ACTIVE'::text)
26. 0.003 31.203 ↓ 156.0 156 1

Nested Loop Anti Join (cost=0.29..8.68 rows=1 width=16) (actual time=6.398..31.203 rows=156 loops=1)

27. 30.888 30.888 ↓ 156.0 156 1

CTE Scan on pit (cost=0.00..0.02 rows=1 width=16) (actual time=6.392..30.888 rows=156 loops=1)

28. 0.000 0.312 ↓ 0.0 0 156

Nested Loop (cost=0.29..8.65 rows=1 width=16) (actual time=0.002..0.002 rows=0 loops=156)

29. 0.156 0.156 ↑ 1.0 1 156

Index Scan using person_to_person_type_person_idx on person_to_person_type person_to_person_type_1 (cost=0.14..8.16 rows=1 width=32) (actual time=0.001..0.001 rows=1 loops=156)

  • Index Cond: (person_id = pit."PERSON_ID")
30. 0.156 0.156 ↓ 0.0 0 156

Index Scan using person_type_pkey on person_type person_type_1 (cost=0.15..0.48 rows=1 width=16) (actual time=0.001..0.001 rows=0 loops=156)

  • Index Cond: (id = person_to_person_type_1.person_type_id)
  • Filter: (base_type = 'INTERNAL'::text)
  • Rows Removed by Filter: 1
31. 0.156 0.156 ↑ 1.0 1 156

Index Only Scan using pk_person on person person_1 (cost=0.27..8.29 rows=1 width=16) (actual time=0.001..0.001 rows=1 loops=156)

  • Index Cond: (id = pit."PERSON_ID")
  • Heap Fetches: 156
32.          

CTE list-condition-0

33. 0.026 32.128 ↓ 10.0 10 1

Nested Loop Semi Join (cost=0.70..12.23 rows=1 width=16) (actual time=6.772..32.128 rows=10 loops=1)

  • Join Filter: (base.id = attribute_value_1.target_id)
34. 0.084 32.000 ↓ 51.0 51 1

Nested Loop Semi Join (cost=0.42..10.21 rows=1 width=48) (actual time=6.436..32.000 rows=51 loops=1)

  • Join Filter: (base.id = attribute_value.target_id)
35. 0.000 31.604 ↓ 156.0 156 1

Nested Loop (cost=0.14..8.19 rows=1 width=32) (actual time=6.405..31.604 rows=156 loops=1)

36. 31.452 31.452 ↓ 156.0 156 1

CTE Scan on base (cost=0.00..0.02 rows=1 width=16) (actual time=6.403..31.452 rows=156 loops=1)

37. 0.156 0.156 ↑ 1.0 1 156

Index Scan using person_id_active_idx on person person_2 (cost=0.14..8.16 rows=1 width=16) (actual time=0.001..0.001 rows=1 loops=156)

  • Index Cond: (id = base.id)
38. 0.312 0.312 ↓ 0.0 0 156

Index Scan using pk_attribute_value on attribute_value (cost=0.28..2.00 rows=1 width=16) (actual time=0.002..0.002 rows=0 loops=156)

  • Index Cond: ((target_id = person_2.id) AND (type = 'PERSON'::text) AND (attribute_id = 'ba8d4720-ef02-4dc2-8c6f-683d16d63c5e'::uuid))
  • Filter: (lower(value) = 'true'::text)
39. 0.102 0.102 ↓ 0.0 0 51

Index Scan using pk_attribute_value on attribute_value attribute_value_1 (cost=0.28..2.00 rows=1 width=16) (actual time=0.002..0.002 rows=0 loops=51)

  • 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)
  • Rows Removed by Filter: 1
40.          

CTE match

41. 0.016 32.297 ↓ 10.0 10 1

Sort (cost=8.38..8.38 rows=1 width=16) (actual time=32.297..32.297 rows=10 loops=1)

  • Sort Key: ((SubPlan 4))
  • Sort Method: quicksort Memory: 25kB
42. 0.009 32.281 ↓ 10.0 10 1

Subquery Scan on alias_87432128 (cost=0.02..8.37 rows=1 width=16) (actual time=32.168..32.281 rows=10 loops=1)

43. 0.010 32.142 ↓ 10.0 10 1

HashAggregate (cost=0.02..0.03 rows=1 width=16) (actual time=32.141..32.142 rows=10 loops=1)

  • Group Key: "list-condition-0".id
44. 32.132 32.132 ↓ 10.0 10 1

CTE Scan on "list-condition-0" (cost=0.00..0.02 rows=1 width=16) (actual time=6.774..32.132 rows=10 loops=1)

45.          

SubPlan (for Subquery Scan)

46. 0.130 0.130 ↑ 1.0 1 10

Seq Scan on person person_3 (cost=0.00..8.32 rows=1 width=8) (actual time=0.007..0.013 rows=1 loops=10)

  • Filter: (id = alias_87432128.record_id)
  • Rows Removed by Filter: 185
47.          

CTE view

48. 0.002 32.881 ↓ 10.0 10 1

Limit (cost=0.00..76.58 rows=1 width=48) (actual time=32.404..32.881 rows=10 loops=1)

49. 0.050 32.879 ↓ 10.0 10 1

WindowAgg (cost=0.00..76.58 rows=1 width=48) (actual time=32.404..32.879 rows=10 loops=1)

50. 32.299 32.299 ↓ 10.0 10 1

CTE Scan on match (cost=0.00..0.02 rows=1 width=48) (actual time=32.298..32.299 rows=10 loops=1)

51.          

SubPlan (for WindowAgg)

52. 0.130 0.130 ↑ 1.0 1 10

Seq Scan on person person_4 (cost=0.00..8.32 rows=1 width=7) (actual time=0.006..0.013 rows=1 loops=10)

  • Filter: (id = match.record_id)
  • Rows Removed by Filter: 185
53. 0.120 0.120 ↑ 1.0 1 10

Seq Scan on person person_5 (cost=0.00..8.32 rows=1 width=12) (actual time=0.006..0.012 rows=1 loops=10)

  • Filter: (id = match.record_id)
  • Rows Removed by Filter: 185
54. 0.000 0.060 ↑ 1.0 1 10

Limit (cost=16.60..16.61 rows=1 width=60) (actual time=0.006..0.006 rows=1 loops=10)

55. 0.020 0.060 ↑ 1.0 1 10

Sort (cost=16.60..16.61 rows=1 width=60) (actual time=0.006..0.006 rows=1 loops=10)

  • Sort Key: employee_1.sort_code
  • Sort Method: quicksort Memory: 25kB
56. 0.000 0.040 ↑ 1.0 1 10

Nested Loop (cost=0.54..16.59 rows=1 width=60) (actual time=0.004..0.004 rows=1 loops=10)

57. 0.020 0.020 ↑ 1.0 1 10

Index Scan using employee_person_id_state_idx on employee employee_1 (cost=0.27..8.29 rows=1 width=20) (actual time=0.002..0.002 rows=1 loops=10)

  • Index Cond: ((person_id = match.record_id) AND (state = 'ACTIVE'::text))
58. 0.022 0.022 ↑ 1.0 1 11

Index Scan using organization_id_state_idx on organization (cost=0.27..8.29 rows=1 width=72) (actual time=0.002..0.002 rows=1 loops=11)

  • Index Cond: ((id = employee_1.organization_id) AND (state = 'ACTIVE'::text))
59. 0.000 0.090 ↑ 1.0 1 10

Limit (cost=13.35..13.36 rows=1 width=12) (actual time=0.009..0.009 rows=1 loops=10)

60. 0.020 0.090 ↑ 1.0 1 10

Sort (cost=13.35..13.36 rows=1 width=12) (actual time=0.009..0.009 rows=1 loops=10)

  • Sort Key: employee_2.sort_code
  • Sort Method: quicksort Memory: 25kB
61. 0.000 0.070 ↑ 1.0 1 10

Nested Loop (cost=0.96..13.34 rows=1 width=12) (actual time=0.006..0.007 rows=1 loops=10)

62. 0.008 0.060 ↑ 1.0 1 10

Nested Loop (cost=0.81..13.05 rows=1 width=20) (actual time=0.005..0.006 rows=1 loops=10)

63. 0.009 0.030 ↑ 1.0 1 10

Nested Loop (cost=0.54..12.59 rows=1 width=36) (actual time=0.003..0.003 rows=1 loops=10)

64. 0.010 0.010 ↑ 1.0 1 10

Index Scan using employee_person_id_state_idx on employee employee_2 (cost=0.27..8.29 rows=1 width=20) (actual time=0.001..0.001 rows=1 loops=10)

  • Index Cond: ((person_id = match.record_id) AND (state = 'ACTIVE'::text))
65. 0.011 0.011 ↑ 1.0 1 11

Index Only Scan using organization_id_state_idx on organization organization_1 (cost=0.27..4.29 rows=1 width=16) (actual time=0.001..0.001 rows=1 loops=11)

  • Index Cond: ((id = employee_2.organization_id) AND (state = 'ACTIVE'::text))
  • Heap Fetches: 0
66. 0.022 0.022 ↑ 1.0 1 11

Index Scan using pk_organization_address on organization_address (cost=0.27..0.44 rows=1 width=32) (actual time=0.002..0.002 rows=1 loops=11)

  • Index Cond: ((organization_id = organization_1.id) AND (address_type = 'STANDARD'::text))
67. 0.011 0.011 ↑ 1.0 1 11

Index Scan using address_pkey on address (cost=0.14..0.29 rows=1 width=24) (actual time=0.001..0.001 rows=1 loops=11)

  • Index Cond: (id = organization_address.address_id)
68. 0.000 0.080 ↑ 1.0 1 10

Limit (cost=13.32..13.33 rows=1 width=16) (actual time=0.008..0.008 rows=1 loops=10)

69. 0.010 0.080 ↑ 1.0 1 10

Sort (cost=13.32..13.33 rows=1 width=16) (actual time=0.008..0.008 rows=1 loops=10)

  • Sort Key: employee_3.sort_code
  • Sort Method: quicksort Memory: 25kB
70. 0.009 0.070 ↑ 1.0 1 10

Nested Loop (cost=0.96..13.31 rows=1 width=16) (actual time=0.006..0.007 rows=1 loops=10)

71. 0.009 0.050 ↑ 1.0 1 10

Nested Loop (cost=0.81..13.03 rows=1 width=32) (actual time=0.005..0.005 rows=1 loops=10)

72. 0.009 0.030 ↑ 1.0 1 10

Nested Loop (cost=0.54..12.59 rows=1 width=36) (actual time=0.003..0.003 rows=1 loops=10)

73. 0.010 0.010 ↑ 1.0 1 10

Index Scan using employee_person_id_state_idx on employee employee_3 (cost=0.27..8.29 rows=1 width=20) (actual time=0.001..0.001 rows=1 loops=10)

  • Index Cond: ((person_id = match.record_id) AND (state = 'ACTIVE'::text))
74. 0.011 0.011 ↑ 1.0 1 11

Index Only Scan using organization_id_state_idx on organization organization_2 (cost=0.27..4.29 rows=1 width=16) (actual time=0.001..0.001 rows=1 loops=11)

  • Index Cond: ((id = employee_3.organization_id) AND (state = 'ACTIVE'::text))
  • Heap Fetches: 0
75. 0.011 0.011 ↑ 1.0 1 11

Index Scan using organization_communication_data_organization_id_idx on organization_communication_data (cost=0.27..0.42 rows=1 width=44) (actual time=0.001..0.001 rows=1 loops=11)

  • Index Cond: (organization_id = organization_2.id)
76. 0.011 0.011 ↑ 1.0 1 11

Index Scan using pk_communication_data_type on communication_data_type (cost=0.15..0.27 rows=1 width=16) (actual time=0.001..0.001 rows=1 loops=11)

  • Index Cond: (id = organization_communication_data.communication_data_type_id)
  • Filter: (base_type = 'PHONE'::text)
77. 0.030 0.030 ↑ 1.0 1 10

Index Scan using pk_attribute_value on attribute_value attribute_value_2 (cost=0.28..8.30 rows=1 width=28) (actual time=0.003..0.003 rows=1 loops=10)

  • Index Cond: ((target_id = match.record_id) AND (type = 'PERSON'::text) AND (attribute_id = 'ba8d4720-ef02-4dc2-8c6f-683d16d63c5e'::uuid))
78. 0.020 0.020 ↑ 1.0 1 10

Index Scan using pk_attribute_value on attribute_value attribute_value_3 (cost=0.28..8.30 rows=1 width=28) (actual time=0.002..0.002 rows=1 loops=10)

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