explain.depesz.com

PostgreSQL's explain analyze made readable

Result: sF4R

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

Limit (cost=130.54..210.69 rows=1 width=296) (actual time=23.940..24.911 rows=10 loops=1)

2. 0.062 24.909 ↓ 10.0 10 1

WindowAgg (cost=130.54..210.69 rows=1 width=296) (actual time=23.939..24.909 rows=10 loops=1)

3. 0.003 23.167 ↓ 10.0 10 1

Subquery Scan on match (cost=130.54..130.55 rows=1 width=48) (actual time=23.164..23.167 rows=10 loops=1)

4. 0.032 23.164 ↓ 10.0 10 1

Sort (cost=130.54..130.54 rows=1 width=48) (actual time=23.163..23.164 rows=10 loops=1)

  • Sort Key: ((SubPlan 8))
  • Sort Method: quicksort Memory: 25kB
5. 0.013 23.132 ↓ 10.0 10 1

Subquery Scan on alias_87432128 (cost=123.19..130.53 rows=1 width=48) (actual time=23.026..23.132 rows=10 loops=1)

6. 0.005 23.009 ↓ 10.0 10 1

Unique (cost=123.19..123.19 rows=1 width=16) (actual time=23.003..23.009 rows=10 loops=1)

7. 0.011 23.004 ↓ 10.0 10 1

Sort (cost=123.19..123.19 rows=1 width=16) (actual time=23.003..23.004 rows=10 loops=1)

  • Sort Key: alias_755097.person_id
  • Sort Method: quicksort Memory: 25kB
8. 0.035 22.993 ↓ 10.0 10 1

Nested Loop Semi Join (cost=82.80..123.18 rows=1 width=16) (actual time=16.622..22.993 rows=10 loops=1)

  • Join Filter: (alias_755097.person_id = attribute_value_1.target_id)
9. 0.103 22.856 ↓ 51.0 51 1

Nested Loop Semi Join (cost=82.52..121.52 rows=1 width=48) (actual time=15.823..22.856 rows=51 loops=1)

  • Join Filter: (alias_755097.person_id = attribute_value.target_id)
10. 0.002 21.973 ↓ 156.0 156 1

Nested Loop (cost=82.25..119.87 rows=1 width=32) (actual time=15.789..21.973 rows=156 loops=1)

11. 0.044 21.815 ↓ 156.0 156 1

Nested Loop Anti Join (cost=82.10..111.62 rows=1 width=16) (actual time=15.782..21.815 rows=156 loops=1)

12. 0.027 21.459 ↓ 156.0 156 1

Nested Loop (cost=81.82..103.14 rows=1 width=16) (actual time=15.763..21.459 rows=156 loops=1)

13. 0.195 21.276 ↓ 156.0 156 1

Nested Loop (cost=81.68..102.82 rows=1 width=32) (actual time=15.752..21.276 rows=156 loops=1)

  • Join Filter: (organizational_unit.organizational_hierarchy_id = organizational_hierarchy.id)
14. 0.058 21.081 ↓ 156.0 156 1

Nested Loop (cost=81.68..101.80 rows=1 width=48) (actual time=15.748..21.081 rows=156 loops=1)

15. 1.262 20.867 ↓ 156.0 156 1

Nested Loop (cost=81.53..93.55 rows=1 width=32) (actual time=15.738..20.867 rows=156 loops=1)

  • Join Filter: (organizational_unit.id = person_assignment.organizational_unit_id)
  • Rows Removed by Join Filter: 1716
16. 0.327 13.989 ↓ 1,872.0 1,872 1

Nested Loop (cost=81.53..92.10 rows=1 width=48) (actual time=1.648..13.989 rows=1,872 loops=1)

17. 0.012 0.012 ↓ 26.0 26 1

Seq Scan on organizational_unit (cost=0.00..1.32 rows=1 width=64) (actual time=0.005..0.012 rows=26 loops=1)

  • Filter: (state = 'ACTIVE'::text)
18. 0.520 13.650 ↓ 72.0 72 26

Nested Loop Anti Join (cost=81.53..90.77 rows=1 width=16) (actual time=0.263..0.525 rows=72 loops=26)

19. 0.364 9.386 ↓ 72.0 72 26

Subquery Scan on alias_755097 (cost=81.25..82.29 rows=1 width=16) (actual time=0.260..0.361 rows=72 loops=26)

  • Filter: (alias_755097.r = 1)
  • Rows Removed by Filter: 76
20. 2.002 9.022 ↓ 4.6 148 26

WindowAgg (cost=81.25..81.89 rows=32 width=44) (actual time=0.260..0.347 rows=148 loops=26)

21. 1.222 7.020 ↓ 4.6 148 26

Sort (cost=81.25..81.33 rows=32 width=20) (actual time=0.258..0.270 rows=148 loops=26)

  • Sort Key: employee.person_id, employee.sort_code
  • Sort Method: quicksort Memory: 25kB
22. 0.694 5.798 ↓ 4.6 148 26

Hash Join (cost=72.42..80.45 rows=32 width=20) (actual time=0.186..0.223 rows=148 loops=26)

  • Hash Cond: (employee.organization_id = organization_assignment.organization_id)
23. 0.372 0.372 ↑ 1.0 167 12

Seq Scan on employee (cost=0.00..7.09 rows=167 width=36) (actual time=0.002..0.031 rows=167 loops=12)

  • Filter: (state = 'ACTIVE'::text)
24. 1.222 4.732 ↓ 4.6 396 26

Hash (cost=71.34..71.34 rows=86 width=16) (actual time=0.182..0.182 rows=396 loops=26)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
25. 1.354 3.510 ↓ 4.6 396 26

Nested Loop (cost=19.75..71.34 rows=86 width=16) (actual time=0.023..0.135 rows=396 loops=26)

26. 0.182 0.182 ↓ 4.0 4 26

Seq Scan on organizational_unit ou2 (cost=0.00..1.39 rows=1 width=16) (actual time=0.004..0.007 rows=4 loops=26)

  • Filter: ((materialized_path @> organizational_unit.materialized_path) AND (state = 'ACTIVE'::text))
  • Rows Removed by Filter: 22
27. 1.504 1.974 ↑ 4.1 110 94

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

  • Recheck Cond: ((organizational_unit_id = ou2.id) AND (state = 'ACTIVE'::text))
  • Heap Blocks: exact=897
28. 0.470 0.470 ↑ 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.005..0.005 rows=110 loops=94)

  • Index Cond: (organizational_unit_id = ou2.id)
29. 0.000 3.744 ↓ 0.0 0 1,872

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

30. 1.872 1.872 ↑ 1.0 1 1,872

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=1,872)

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

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

  • Index Cond: (id = person_to_person_type_1.person_type_id)
  • Filter: (base_type = 'INTERNAL'::text)
  • Rows Removed by Filter: 1
32. 5.616 5.616 ↑ 1.0 1 1,872

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

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

Index Only Scan using person_id_state_idx on person person_1 (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)
  • Heap Fetches: 156
34. 0.000 0.000 ↑ 1.0 1 156

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

  • Filter: (state = 'ACTIVE'::text)
35. 0.156 0.156 ↑ 1.0 1 156

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

  • Index Cond: (id = person_1.id)
  • Heap Fetches: 156
36. 0.000 0.312 ↓ 0.0 0 156

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

37. 0.156 0.156 ↑ 1.0 1 156

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=156)

  • Index Cond: (person_id = alias_755097.person_id)
38. 0.156 0.156 ↓ 0.0 0 156

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

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

Index Only Scan using person_id_state_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)
  • Heap Fetches: 156
40. 0.780 0.780 ↓ 0.0 0 156

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

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

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

  • Index Cond: ((target_id = person.id) AND (type = 'PERSON'::text) AND (attribute_id = 'da99ffba-6349-09bf-4ebf-43073faf1431'::uuid))
  • Filter: (lower(value) = 'false'::text)
  • Rows Removed by Filter: 1
42.          

SubPlan (for Subquery Scan)

43. 0.110 0.110 ↑ 1.0 1 10

Seq Scan on person person_5 (cost=0.00..7.33 rows=1 width=8) (actual time=0.005..0.011 rows=1 loops=10)

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

SubPlan (for WindowAgg)

45. 0.110 0.110 ↑ 1.0 1 10

Seq Scan on person person_3 (cost=0.00..7.33 rows=1 width=7) (actual time=0.006..0.011 rows=1 loops=10)

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

Seq Scan on person person_4 (cost=0.00..7.33 rows=1 width=12) (actual time=0.005..0.010 rows=1 loops=10)

  • Filter: (id = match.record_id)
  • Rows Removed by Filter: 185
47. 0.010 0.440 ↑ 1.0 1 10

Limit (cost=15.84..15.85 rows=1 width=60) (actual time=0.043..0.044 rows=1 loops=10)

48. 0.020 0.430 ↑ 1.0 1 10

Sort (cost=15.84..15.85 rows=1 width=60) (actual time=0.043..0.043 rows=1 loops=10)

  • Sort Key: employee_1.sort_code
  • Sort Method: quicksort Memory: 25kB
49. 0.017 0.410 ↑ 1.0 1 10

Nested Loop (cost=0.27..15.83 rows=1 width=60) (actual time=0.033..0.041 rows=1 loops=10)

50. 0.140 0.140 ↑ 1.0 1 10

Seq Scan on employee employee_1 (cost=0.00..7.50 rows=1 width=20) (actual time=0.006..0.014 rows=1 loops=10)

  • Filter: ((person_id = match.record_id) AND (state = 'ACTIVE'::text))
  • Rows Removed by Filter: 166
51. 0.253 0.253 ↑ 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.023..0.023 rows=1 loops=11)

  • Index Cond: (id = employee_1.organization_id)
52. 0.010 0.520 ↑ 1.0 1 10

Limit (cost=16.52..16.52 rows=1 width=12) (actual time=0.051..0.052 rows=1 loops=10)

53. 0.020 0.510 ↑ 1.0 1 10

Sort (cost=16.52..16.52 rows=1 width=12) (actual time=0.051..0.051 rows=1 loops=10)

  • Sort Key: employee_2.sort_code
  • Sort Method: quicksort Memory: 25kB
54. 0.007 0.490 ↑ 1.0 1 10

Nested Loop (cost=0.69..16.51 rows=1 width=12) (actual time=0.041..0.049 rows=1 loops=10)

55. 0.005 0.450 ↑ 1.0 1 10

Nested Loop (cost=0.55..16.25 rows=1 width=20) (actual time=0.037..0.045 rows=1 loops=10)

56. 0.008 0.170 ↑ 1.0 1 10

Nested Loop (cost=0.27..15.83 rows=1 width=36) (actual time=0.009..0.017 rows=1 loops=10)

57. 0.140 0.140 ↑ 1.0 1 10

Seq Scan on employee employee_2 (cost=0.00..7.50 rows=1 width=20) (actual time=0.006..0.014 rows=1 loops=10)

  • Filter: ((person_id = match.record_id) AND (state = 'ACTIVE'::text))
  • Rows Removed by Filter: 166
58. 0.022 0.022 ↑ 1.0 1 11

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

  • Index Cond: (id = employee_2.organization_id)
  • Heap Fetches: 11
59. 0.275 0.275 ↑ 1.0 1 11

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

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

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

  • Index Cond: (id = organization_address.address_id)
61. 0.020 0.460 ↑ 1.0 1 10

Limit (cost=16.49..16.50 rows=1 width=16) (actual time=0.045..0.046 rows=1 loops=10)

62. 0.010 0.440 ↑ 1.0 1 10

Sort (cost=16.49..16.50 rows=1 width=16) (actual time=0.044..0.044 rows=1 loops=10)

  • Sort Key: employee_3.sort_code
  • Sort Method: quicksort Memory: 25kB
63. 0.008 0.430 ↑ 1.0 1 10

Nested Loop (cost=0.68..16.48 rows=1 width=16) (actual time=0.036..0.043 rows=1 loops=10)

64. 0.008 0.180 ↑ 1.0 1 10

Nested Loop (cost=0.55..16.26 rows=1 width=32) (actual time=0.012..0.018 rows=1 loops=10)

65. 0.019 0.150 ↑ 1.0 1 10

Nested Loop (cost=0.27..15.83 rows=1 width=36) (actual time=0.009..0.015 rows=1 loops=10)

66. 0.120 0.120 ↑ 1.0 1 10

Seq Scan on employee employee_3 (cost=0.00..7.50 rows=1 width=20) (actual time=0.006..0.012 rows=1 loops=10)

  • Filter: ((person_id = match.record_id) AND (state = 'ACTIVE'::text))
  • Rows Removed by Filter: 166
67. 0.011 0.011 ↑ 1.0 1 11

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

  • Index Cond: (id = employee_3.organization_id)
  • Heap Fetches: 11
68. 0.022 0.022 ↑ 1.0 1 11

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

  • Index Cond: (organization_id = organization_2.id)
69. 0.242 0.242 ↑ 1.0 1 11

Index Scan using pk_communication_data_type on communication_data_type (cost=0.13..0.18 rows=1 width=16) (actual time=0.022..0.022 rows=1 loops=11)

  • Index Cond: (id = organization_communication_data.communication_data_type_id)
  • Filter: (base_type = 'PHONE'::text)
70. 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))
71. 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 : 16.668 ms
Execution time : 25.130 ms