explain.depesz.com

PostgreSQL's explain analyze made readable

Result: E5nZE

Settings
# exclusive inclusive rows x rows loops node
1. 1,561.236 1,561.236 ↓ 1,201.0 2,402 1

CTE Scan on output (cost=2,253.60..2,253.64 rows=2 width=264) (actual time=1,560.561..1,561.236 rows=2,402 loops=1)

2.          

CTE base

3. 49.306 595.252 ↓ 56,730.0 56,730 1

Nested Loop (cost=2,140.03..2,148.07 rows=1 width=16) (actual time=185.713..595.252 rows=56,730 loops=1)

4.          

CTE pit

5. 17.083 474.211 ↓ 56,730.0 56,730 1

Nested Loop (cost=2,062.09..2,139.74 rows=1 width=16) (actual time=185.701..474.211 rows=56,730 loops=1)

6. 5.301 342.918 ↓ 57,105.0 57,105 1

Nested Loop (cost=2,061.80..2,131.43 rows=1 width=16) (actual time=185.686..342.918 rows=57,105 loops=1)

7. 0.010 0.249 ↓ 4.0 4 1

Nested Loop (cost=0.00..6.62 rows=1 width=32) (actual time=0.029..0.249 rows=4 loops=1)

  • Join Filter: (organizational_unit.organizational_hierarchy_id = organizational_hierarchy.id)
8. 0.021 0.231 ↓ 4.0 4 1

Nested Loop (cost=0.00..5.59 rows=1 width=48) (actual time=0.024..0.231 rows=4 loops=1)

  • Join Filter: (organizational_unit.id = person_assignment.organizational_unit_id)
  • Rows Removed by Join Filter: 60
9. 0.018 0.018 ↓ 16.0 16 1

Seq Scan on organizational_unit (cost=0.00..1.35 rows=1 width=64) (actual time=0.011..0.018 rows=16 loops=1)

  • Filter: (state = 'ACTIVE'::text)
  • Rows Removed by Filter: 12
10. 0.192 0.192 ↓ 2.0 4 16

Seq Scan on person_assignment (cost=0.00..4.21 rows=2 width=16) (actual time=0.002..0.012 rows=4 loops=16)

  • Filter: ((state = 'ACTIVE'::text) AND (person_id = '811e14f3-24dc-4c08-a013-a6f1504c286b'::uuid))
  • Rows Removed by Filter: 78
11. 0.008 0.008 ↑ 1.0 1 4

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

  • Filter: (state = 'ACTIVE'::text)
12. 7.912 337.368 ↓ 1,427.6 14,276 4

Subquery Scan on alias_755097 (cost=2,061.80..2,124.72 rows=10 width=16) (actual time=64.916..84.342 rows=14,276 loops=4)

  • Filter: (alias_755097.r = 1)
  • Rows Removed by Filter: 4045
13. 57.664 329.456 ↓ 9.5 18,321 4

WindowAgg (cost=2,061.80..2,100.52 rows=1,936 width=44) (actual time=64.915..82.364 rows=18,321 loops=4)

14. 43.268 271.792 ↓ 9.5 18,321 4

Sort (cost=2,061.80..2,066.64 rows=1,936 width=20) (actual time=64.908..67.948 rows=18,321 loops=4)

  • Sort Key: employee.person_id, employee.sort_code
  • Sort Method: quicksort Memory: 71kB
15. 44.514 228.524 ↓ 9.5 18,321 4

Nested Loop (cost=68.89..1,956.10 rows=1,936 width=20) (actual time=0.183..57.131 rows=18,321 loops=4)

16. 6.965 26.132 ↓ 9.4 13,156 4

Nested Loop (cost=68.47..972.69 rows=1,406 width=16) (actual time=0.175..6.533 rows=13,156 loops=4)

17. 0.092 0.092 ↓ 6.0 6 4

Seq Scan on organizational_unit ou2 (cost=0.00..1.42 rows=1 width=16) (actual time=0.008..0.023 rows=6 loops=4)

  • Filter: ((materialized_path @> organizational_unit.materialized_path) AND (state = 'ACTIVE'::text))
  • Rows Removed by Filter: 22
18. 16.075 19.075 ↓ 1.0 2,105 25

Bitmap Heap Scan on organization_assignment (cost=68.47..950.55 rows=2,072 width=32) (actual time=0.136..0.763 rows=2,105 loops=25)

  • Recheck Cond: ((organizational_unit_id = ou2.id) AND (state = 'ACTIVE'::text))
  • Heap Blocks: exact=3299
19. 3.000 3.000 ↓ 1.0 2,105 25

Bitmap Index Scan on organizational_assignment_org_unit_id_org_id_idx (cost=0.00..67.95 rows=2,072 width=0) (actual time=0.120..0.120 rows=2,105 loops=25)

  • Index Cond: (organizational_unit_id = ou2.id)
20. 157.878 157.878 ↑ 3.0 1 52,626

Index Scan using employee_organization_id_idx on employee (cost=0.42..0.67 rows=3 width=36) (actual time=0.002..0.003 rows=1 loops=52,626)

  • Index Cond: (organization_id = organization_assignment.organization_id)
  • Filter: (state = 'ACTIVE'::text)
  • Rows Removed by Filter: 0
21. 114.210 114.210 ↑ 1.0 1 57,105

Index Only Scan using person_id_active_idx on person (cost=0.29..8.31 rows=1 width=16) (actual time=0.002..0.002 rows=1 loops=57,105)

  • Index Cond: (id = alias_755097.person_id)
  • Heap Fetches: 56730
22. 489.216 489.216 ↓ 56,730.0 56,730 1

CTE Scan on pit (cost=0.00..0.02 rows=1 width=16) (actual time=185.702..489.216 rows=56,730 loops=1)

23. 56.730 56.730 ↑ 1.0 1 56,730

Index Only Scan using pk_person on person person_1 (cost=0.29..8.31 rows=1 width=16) (actual time=0.001..0.001 rows=1 loops=56,730)

  • Index Cond: (id = pit."PERSON_ID")
  • Heap Fetches: 56730
24.          

CTE list-condition-0

25. 27.719 1,414.480 ↓ 4,922.0 4,922 1

Nested Loop Semi Join (cost=1.42..11.31 rows=1 width=16) (actual time=187.294..1,414.480 rows=4,922 loops=1)

  • Join Filter: (organization.id = attribute_value.target_id)
26. 44.942 1,136.843 ↓ 41,653.0 83,306 1

Nested Loop (cost=1.00..9.79 rows=2 width=48) (actual time=185.740..1,136.843 rows=83,306 loops=1)

27. 42.851 924.913 ↓ 41,747.0 83,494 1

Nested Loop (cost=0.71..8.99 rows=2 width=32) (actual time=185.730..924.913 rows=83,494 loops=1)

  • Join Filter: (base.id = employee_1.person_id)
28. 44.798 711.872 ↓ 56,730.0 56,730 1

Nested Loop (cost=0.29..8.33 rows=1 width=32) (actual time=185.719..711.872 rows=56,730 loops=1)

29. 610.344 610.344 ↓ 56,730.0 56,730 1

CTE Scan on base (cost=0.00..0.02 rows=1 width=16) (actual time=185.713..610.344 rows=56,730 loops=1)

30. 56.730 56.730 ↑ 1.0 1 56,730

Index Only Scan using person_id_active_idx on person person_2 (cost=0.29..8.31 rows=1 width=16) (actual time=0.001..0.001 rows=1 loops=56,730)

  • Index Cond: (id = base.id)
  • Heap Fetches: 56730
31. 170.190 170.190 ↑ 2.0 1 56,730

Index Scan using employee_person_id_sort_code_idx on employee employee_1 (cost=0.42..0.64 rows=2 width=32) (actual time=0.003..0.003 rows=1 loops=56,730)

  • Index Cond: (person_id = person_2.id)
32. 166.988 166.988 ↑ 1.0 1 83,494

Index Only Scan using pk_organization on organization (cost=0.29..0.40 rows=1 width=16) (actual time=0.002..0.002 rows=1 loops=83,494)

  • Index Cond: (id = employee_1.organization_id)
  • Heap Fetches: 83306
33. 249.918 249.918 ↓ 0.0 0 83,306

Index Scan using pk_attribute_value on attribute_value (cost=0.42..0.75 rows=1 width=16) (actual time=0.003..0.003 rows=0 loops=83,306)

  • Index Cond: ((target_id = employee_1.organization_id) AND (type = 'ORGANIZATION'::text) AND (attribute_id = '198e1d92-d72e-410e-9b0a-b4e84a77fb94'::uuid))
  • Filter: ((lower(value) = '8629d240-7156-9c2a-53c0-cef17c71b6d7'::text) OR (lower(value) = '2e2d7d94-45fd-483c-8dce-74fe871afefb'::text) OR (lower(value) = 'f5c7f99c-82c4-490d-916e-9decb1140670'::text) OR (lower(value) = 'acdfec66-ad38-4705-8c7e-ce6b0d0a41c4'::text))
34.          

CTE match

35. 3.331 1,428.447 ↓ 2,401.0 2,401 1

Sort (cost=8.36..8.37 rows=1 width=48) (actual time=1,428.115..1,428.447 rows=2,401 loops=1)

  • Sort Key: ((SubPlan 4))
  • Sort Method: quicksort Memory: 284kB
36. 1.636 1,425.116 ↓ 2,401.0 2,401 1

Subquery Scan on alias_87432128 (cost=0.02..8.35 rows=1 width=48) (actual time=1,418.216..1,425.116 rows=2,401 loops=1)

37. 2.686 1,418.678 ↓ 2,401.0 2,401 1

HashAggregate (cost=0.02..0.03 rows=1 width=16) (actual time=1,418.205..1,418.678 rows=2,401 loops=1)

  • Group Key: "list-condition-0".id
38. 1,415.992 1,415.992 ↓ 4,922.0 4,922 1

CTE Scan on "list-condition-0" (cost=0.00..0.02 rows=1 width=16) (actual time=187.295..1,415.992 rows=4,922 loops=1)

39.          

SubPlan (for Subquery Scan)

40. 4.802 4.802 ↑ 1.0 1 2,401

Index Scan using pk_person on person person_3 (cost=0.29..8.31 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=2,401)

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

CTE view

42. 5.431 1,556.814 ↓ 2,401.0 2,401 1

WindowAgg (cost=0.00..85.76 rows=1 width=264) (actual time=1,428.237..1,556.814 rows=2,401 loops=1)

43. 1,428.932 1,428.932 ↓ 2,401.0 2,401 1

CTE Scan on match (cost=0.00..0.02 rows=1 width=48) (actual time=1,428.116..1,428.932 rows=2,401 loops=1)

44.          

SubPlan (for WindowAgg)

45. 7.203 7.203 ↑ 1.0 1 2,401

Index Scan using pk_person on person person_4 (cost=0.29..8.31 rows=1 width=7) (actual time=0.003..0.003 rows=1 loops=2,401)

  • Index Cond: (id = match.record_id)
46. 4.802 4.802 ↑ 1.0 1 2,401

Index Scan using pk_person on person person_5 (cost=0.29..8.31 rows=1 width=9) (actual time=0.002..0.002 rows=1 loops=2,401)

  • Index Cond: (id = match.record_id)
47. 2.401 24.010 ↑ 1.0 1 2,401

Limit (cost=16.76..16.76 rows=1 width=30) (actual time=0.009..0.010 rows=1 loops=2,401)

48. 2.401 21.609 ↑ 1.0 1 2,401

Sort (cost=16.76..16.76 rows=1 width=30) (actual time=0.009..0.009 rows=1 loops=2,401)

  • Sort Key: employee_2.sort_code
  • Sort Method: quicksort Memory: 25kB
49. 2.371 19.208 ↑ 1.0 1 2,401

Nested Loop (cost=0.71..16.75 rows=1 width=30) (actual time=0.008..0.008 rows=1 loops=2,401)

50. 9.604 9.604 ↑ 1.0 1 2,401

Index Scan using employee_person_id_state_idx on employee employee_2 (cost=0.42..8.44 rows=1 width=20) (actual time=0.004..0.004 rows=1 loops=2,401)

  • Index Cond: ((person_id = match.record_id) AND (state = 'ACTIVE'::text))
51. 7.233 7.233 ↑ 1.0 1 2,411

Index Scan using pk_organization on organization organization_1 (cost=0.29..8.31 rows=1 width=42) (actual time=0.003..0.003 rows=1 loops=2,411)

  • Index Cond: (id = employee_2.organization_id)
  • Filter: (state = 'ACTIVE'::text)
52. 2.401 36.015 ↑ 1.0 1 2,401

Limit (cost=17.74..17.75 rows=1 width=13) (actual time=0.014..0.015 rows=1 loops=2,401)

53. 2.401 33.614 ↑ 1.0 1 2,401

Sort (cost=17.74..17.75 rows=1 width=13) (actual time=0.014..0.014 rows=1 loops=2,401)

  • Sort Key: employee_3.sort_code
  • Sort Method: quicksort Memory: 25kB
54. 2.371 31.213 ↑ 1.0 1 2,401

Nested Loop (cost=1.41..17.73 rows=1 width=13) (actual time=0.012..0.013 rows=1 loops=2,401)

55. 0.000 21.609 ↑ 1.0 1 2,401

Nested Loop (cost=1.12..17.30 rows=1 width=20) (actual time=0.009..0.009 rows=1 loops=2,401)

56. 2.381 12.005 ↑ 1.0 1 2,401

Nested Loop (cost=0.71..16.75 rows=1 width=36) (actual time=0.004..0.005 rows=1 loops=2,401)

57. 4.802 4.802 ↑ 1.0 1 2,401

Index Scan using employee_person_id_state_idx on employee employee_3 (cost=0.42..8.44 rows=1 width=20) (actual time=0.002..0.002 rows=1 loops=2,401)

  • Index Cond: ((person_id = match.record_id) AND (state = 'ACTIVE'::text))
58. 4.822 4.822 ↑ 1.0 1 2,411

Index Scan using pk_organization on organization organization_2 (cost=0.29..8.31 rows=1 width=16) (actual time=0.002..0.002 rows=1 loops=2,411)

  • Index Cond: (id = employee_3.organization_id)
  • Filter: (state = 'ACTIVE'::text)
59. 9.644 9.644 ↑ 1.0 1 2,411

Index Scan using pk_organization_address on organization_address (cost=0.41..0.55 rows=1 width=32) (actual time=0.004..0.004 rows=1 loops=2,411)

  • Index Cond: ((organization_id = organization_2.id) AND (address_type = 'STANDARD'::text))
60. 7.233 7.233 ↑ 1.0 1 2,411

Index Scan using address_pkey on address (cost=0.29..0.44 rows=1 width=25) (actual time=0.003..0.003 rows=1 loops=2,411)

  • Index Cond: (id = organization_address.address_id)
61. 2.401 38.416 ↑ 1.0 1 2,401

Limit (cost=17.83..17.84 rows=1 width=20) (actual time=0.015..0.016 rows=1 loops=2,401)

62. 2.401 36.015 ↑ 1.0 1 2,401

Sort (cost=17.83..17.84 rows=1 width=20) (actual time=0.015..0.015 rows=1 loops=2,401)

  • Sort Key: employee_4.sort_code
  • Sort Method: quicksort Memory: 25kB
63. 3.545 33.614 ↑ 1.0 1 2,401

Nested Loop (cost=1.26..17.82 rows=1 width=20) (actual time=0.011..0.014 rows=1 loops=2,401)

64. 2.361 24.010 ↓ 1.5 3 2,401

Nested Loop (cost=1.12..17.50 rows=2 width=36) (actual time=0.009..0.010 rows=3 loops=2,401)

65. 2.381 12.005 ↑ 1.0 1 2,401

Nested Loop (cost=0.71..16.75 rows=1 width=36) (actual time=0.004..0.005 rows=1 loops=2,401)

66. 4.802 4.802 ↑ 1.0 1 2,401

Index Scan using employee_person_id_state_idx on employee employee_4 (cost=0.42..8.44 rows=1 width=20) (actual time=0.002..0.002 rows=1 loops=2,401)

  • Index Cond: ((person_id = match.record_id) AND (state = 'ACTIVE'::text))
67. 4.822 4.822 ↑ 1.0 1 2,411

Index Scan using pk_organization on organization organization_3 (cost=0.29..8.31 rows=1 width=16) (actual time=0.002..0.002 rows=1 loops=2,411)

  • Index Cond: (id = employee_4.organization_id)
  • Filter: (state = 'ACTIVE'::text)
68. 9.644 9.644 ↑ 1.0 3 2,411

Index Scan using organization_communication_data_organization_id_idx on organization_communication_data (cost=0.42..0.72 rows=3 width=48) (actual time=0.004..0.004 rows=3 loops=2,411)

  • Index Cond: (organization_id = organization_3.id)
69. 6.059 6.059 ↓ 0.0 0 6,059

Index Scan using pk_communication_data_type on communication_data_type (cost=0.13..0.15 rows=1 width=16) (actual time=0.001..0.001 rows=0 loops=6,059)

  • Index Cond: (id = organization_communication_data.communication_data_type_id)
  • Filter: (base_type = 'PHONE'::text)
  • Rows Removed by Filter: 1
70. 2.401 12.005 ↑ 1.0 1 2,401

Aggregate (cost=16.75..16.76 rows=1 width=32) (actual time=0.005..0.005 rows=1 loops=2,401)

71. 2.401 9.604 ↓ 0.0 0 2,401

Nested Loop Left Join (cost=0.69..16.74 rows=1 width=12) (actual time=0.004..0.004 rows=0 loops=2,401)

72. 7.203 7.203 ↓ 0.0 0 2,401

Index Scan using attribute_value_lists_idx on attribute_value attribute_value_1 (cost=0.42..8.44 rows=1 width=22) (actual time=0.003..0.003 rows=0 loops=2,401)

  • Index Cond: ((target_id = match.record_id) AND (type = 'ORGANIZATION'::text) AND (attribute_id = '198e1d92-d72e-410e-9b0a-b4e84a77fb94'::uuid))
73. 0.000 0.000 ↓ 0.0 0

Index Scan using attribute_option_id_varchar_text_idx on attribute_option (cost=0.27..8.29 rows=1 width=28) (never executed)

  • Index Cond: (attribute_value_1.value = ((id)::character varying)::text)
74.          

CTE output

75. 1.097 1,560.691 ↓ 1,201.0 2,402 1

Sort (cost=0.10..0.11 rows=2 width=264) (actual time=1,560.559..1,560.691 rows=2,402 loops=1)

  • Sort Key: view.index
  • Sort Method: quicksort Memory: 727kB
76. 0.311 1,559.594 ↓ 1,201.0 2,402 1

Append (cost=0.00..0.09 rows=2 width=264) (actual time=1,428.240..1,559.594 rows=2,402 loops=1)

77. 1,558.837 1,558.837 ↓ 2,401.0 2,401 1

CTE Scan on view (cost=0.00..0.02 rows=1 width=264) (actual time=1,428.238..1,558.837 rows=2,401 loops=1)

78. 0.002 0.446 ↑ 1.0 1 1

Subquery Scan on *SELECT* 2 (cost=0.02..0.05 rows=1 width=264) (actual time=0.446..0.446 rows=1 loops=1)

79. 0.156 0.444 ↑ 1.0 1 1

Aggregate (cost=0.02..0.04 rows=1 width=260) (actual time=0.444..0.444 rows=1 loops=1)

80. 0.288 0.288 ↓ 2,401.0 2,401 1

CTE Scan on match match_1 (cost=0.00..0.02 rows=1 width=16) (actual time=0.001..0.288 rows=2,401 loops=1)

Planning time : 10.305 ms
Execution time : 1,563.840 ms