explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 1xad

Settings
# exclusive inclusive rows x rows loops node
1. 173,776.267 173,776.267 ↓ 25.5 51 1

CTE Scan on output (cost=27,056.31..27,056.35 rows=2 width=264) (actual time=173,776.249..173,776.267 rows=51 loops=1)

2.          

CTE base

3. 24.895 1,445.348 ↓ 56,730.0 56,730 1

Nested Loop (cost=2,111.20..2,127.31 rows=1 width=16) (actual time=321.330..1,445.348 rows=56,730 loops=1)

4.          

CTE pit

5. 62.313 1,025.642 ↓ 56,730.0 56,730 1

Nested Loop (cost=2,027.06..2,110.48 rows=1 width=16) (actual time=321.311..1,025.642 rows=56,730 loops=1)

6. 41.780 849.119 ↓ 57,105.0 57,105 1

Nested Loop Anti Join (cost=2,026.77..2,102.17 rows=1 width=16) (actual time=321.286..849.119 rows=57,105 loops=1)

7. 11.766 521.814 ↓ 57,105.0 57,105 1

Nested Loop (cost=2,026.34..2,093.67 rows=1 width=16) (actual time=321.244..521.814 rows=57,105 loops=1)

8. 0.011 0.384 ↓ 4.0 4 1

Nested Loop (cost=0.00..6.63 rows=1 width=32) (actual time=0.049..0.384 rows=4 loops=1)

  • Join Filter: (organizational_unit.organizational_hierarchy_id = organizational_hierarchy.id)
9. 0.049 0.361 ↓ 4.0 4 1

Nested Loop (cost=0.00..5.61 rows=1 width=48) (actual time=0.042..0.361 rows=4 loops=1)

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

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

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

Seq Scan on person_assignment (cost=0.00..4.23 rows=2 width=16) (actual time=0.003..0.018 rows=4 loops=16)

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

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

  • Filter: (state = 'ACTIVE'::text)
13. 16.128 509.664 ↓ 1,586.2 14,276 4

Subquery Scan on alias_755097 (cost=2,026.34..2,086.95 rows=9 width=16) (actual time=96.738..127.416 rows=14,276 loops=4)

  • Filter: (alias_755097.r = 1)
  • Rows Removed by Filter: 4045
14. 89.964 493.536 ↓ 9.8 18,321 4

WindowAgg (cost=2,026.34..2,063.64 rows=1,865 width=44) (actual time=96.737..123.384 rows=18,321 loops=4)

15. 169.840 403.572 ↓ 9.8 18,321 4

Sort (cost=2,026.34..2,031.00 rows=1,865 width=20) (actual time=96.199..100.893 rows=18,321 loops=4)

  • Sort Key: employee.person_id, employee.sort_code
  • Sort Method: quicksort Memory: 71kB
16. 49.050 233.732 ↓ 9.8 18,321 4

Nested Loop (cost=68.85..1,925.03 rows=1,865 width=20) (actual time=0.202..58.433 rows=18,321 loops=4)

17. 8.884 26.804 ↓ 9.4 13,156 4

Nested Loop (cost=68.43..972.53 rows=1,402 width=16) (actual time=0.187..6.701 rows=13,156 loops=4)

18. 0.120 0.120 ↓ 6.0 6 4

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

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

Bitmap Heap Scan on organization_assignment (cost=68.43..950.44 rows=2,067 width=32) (actual time=0.152..0.712 rows=2,105 loops=25)

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

Bitmap Index Scan on organizational_assignment_org_unit_id_org_id_idx (cost=0.00..67.92 rows=2,067 width=0) (actual time=0.138..0.138 rows=2,105 loops=25)

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

Index Scan using employee_organization_id_state_idx on employee (cost=0.42..0.66 rows=2 width=36) (actual time=0.003..0.003 rows=1 loops=52,626)

  • Index Cond: ((organization_id = organization_assignment.organization_id) AND (state = 'ACTIVE'::text))
22. 57.103 285.525 ↓ 0.0 0 57,105

Nested Loop (cost=0.43..8.49 rows=1 width=16) (actual time=0.005..0.005 rows=0 loops=57,105)

23. 171.315 171.315 ↑ 1.0 1 57,105

Index Scan using person_to_person_type_person_idx on person_to_person_type (cost=0.29..8.31 rows=1 width=32) (actual time=0.003..0.003 rows=1 loops=57,105)

  • Index Cond: (person_id = alias_755097.person_id)
24. 57.107 57.107 ↓ 0.0 0 57,107

Index Scan using person_type_pkey on person_type (cost=0.14..0.16 rows=1 width=16) (actual time=0.001..0.001 rows=0 loops=57,107)

  • Index Cond: (id = person_to_person_type.person_type_id)
  • Filter: (base_type = 'INTERNAL'::text)
  • Rows Removed by Filter: 1
25. 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
26. 28.940 1,306.993 ↓ 56,730.0 56,730 1

Nested Loop Anti Join (cost=0.43..8.52 rows=1 width=16) (actual time=321.323..1,306.993 rows=56,730 loops=1)

27. 1,051.133 1,051.133 ↓ 56,730.0 56,730 1

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

28. 56.728 226.920 ↓ 0.0 0 56,730

Nested Loop (cost=0.43..8.49 rows=1 width=16) (actual time=0.004..0.004 rows=0 loops=56,730)

29. 113.460 113.460 ↑ 1.0 1 56,730

Index Scan using person_to_person_type_person_idx on person_to_person_type person_to_person_type_1 (cost=0.29..8.31 rows=1 width=32) (actual time=0.002..0.002 rows=1 loops=56,730)

  • Index Cond: (person_id = pit."PERSON_ID")
30. 56.732 56.732 ↓ 0.0 0 56,732

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

  • Index Cond: (id = person_to_person_type_1.person_type_id)
  • Filter: (base_type = 'INTERNAL'::text)
  • Rows Removed by Filter: 1
31. 113.460 113.460 ↑ 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.002..0.002 rows=1 loops=56,730)

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

CTE list-condition-0

33. 13,179.967 168,131.205 ↓ 3,566.0 3,566 1

Nested Loop (cost=10,208.74..13,446.97 rows=1 width=16) (actual time=2,846.689..168,131.205 rows=3,566 loops=1)

  • Join Filter: (em.id = pe.id)
  • Rows Removed by Join Filter: 145667864
34. 35.003 2,623.948 ↓ 3,590.0 3,590 1

Hash Join (cost=14.04..2,074.57 rows=1 width=32) (actual time=2,567.807..2,623.948 rows=3,590 loops=1)

  • Hash Cond: (organization.materialized_path[1] = parent_child.materialized_path[1])
35. 22.834 22.834 ↑ 1.0 40,249 1

Seq Scan on organization (cost=0.00..1,907.53 rows=40,267 width=37) (actual time=0.009..22.834 rows=40,249 loops=1)

  • Filter: (state = 'ACTIVE'::text)
  • Rows Removed by Filter: 6673
36. 5.309 2,566.111 ↓ 3,590.0 3,590 1

Hash (cost=14.02..14.02 rows=1 width=69) (actual time=2,566.111..2,566.111 rows=3,590 loops=1)

  • Buckets: 4096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 387kB
37. 0.000 2,560.802 ↓ 3,590.0 3,590 1

Nested Loop Semi Join (cost=5.14..14.02 rows=1 width=69) (actual time=323.696..2,560.802 rows=3,590 loops=1)

  • Join Filter: (em.organization_id = attribute_value.target_id)
38. 93.536 2,242.712 ↓ 79,793.0 79,793 1

Nested Loop (cost=4.72..13.09 rows=1 width=101) (actual time=321.365..2,242.712 rows=79,793 loops=1)

39. 143.409 1,898.694 ↓ 41,747.0 83,494 1

Nested Loop (cost=4.43..12.29 rows=2 width=48) (actual time=321.347..1,898.694 rows=83,494 loops=1)

40. 1,471.635 1,471.635 ↓ 56,730.0 56,730 1

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

41. 113.460 283.650 ↑ 2.0 1 56,730

Bitmap Heap Scan on employee em (cost=4.43..12.25 rows=2 width=48) (actual time=0.004..0.005 rows=1 loops=56,730)

  • Recheck Cond: (person_id = base.id)
  • Heap Blocks: exact=79362
42. 170.190 170.190 ↑ 2.0 1 56,730

Bitmap Index Scan on employee_person_id_state_idx (cost=0.00..4.43 rows=2 width=0) (actual time=0.003..0.003 rows=1 loops=56,730)

  • Index Cond: (person_id = base.id)
43. 250.482 250.482 ↑ 1.0 1 83,494

Index Scan using pk_organization on organization parent_child (cost=0.29..0.40 rows=1 width=53) (actual time=0.003..0.003 rows=1 loops=83,494)

  • Index Cond: (id = em.organization_id)
  • Filter: (state = 'ACTIVE'::text)
  • Rows Removed by Filter: 0
44. 319.172 319.172 ↓ 0.0 0 79,793

Index Scan using pk_attribute_value on attribute_value (cost=0.42..0.93 rows=1 width=16) (actual time=0.004..0.004 rows=0 loops=79,793)

  • Index Cond: ((target_id = parent_child.id) AND (type = 'ORGANIZATION'::text) AND (attribute_id = '198e1d92-d72e-410e-9b0a-b4e84a77fb94'::uuid))
  • Filter: (lower(value) = '8629d240-7156-9c2a-53c0-cef17c71b6d7'::text)
  • Rows Removed by Filter: 0
45. 24,408.410 152,327.290 ↓ 224.2 40,577 3,590

Subquery Scan on pe (cost=10,194.71..11,370.13 rows=181 width=16) (actual time=0.062..42.431 rows=40,577 loops=3,590)

  • Filter: (pe.row_number = 1)
  • Rows Removed by Filter: 10142
46. 99,766.100 127,918.880 ↓ 1.4 50,719 3,590

WindowAgg (cost=10,194.71..10,918.05 rows=36,167 width=44) (actual time=0.062..35.632 rows=50,719 loops=3,590)

47. 28,005.627 28,152.780 ↓ 1.4 50,719 3,590

Sort (cost=10,194.71..10,285.12 rows=36,167 width=36) (actual time=0.061..7.842 rows=50,719 loops=3,590)

  • Sort Key: e.person_id, e.sort_code
  • Sort Method: external sort Disk: 2360kB
48. 27.298 147.153 ↓ 1.4 50,719 1

Hash Join (cost=4,740.26..7,456.43 rows=36,167 width=36) (actual time=62.927..147.153 rows=50,719 loops=1)

  • Hash Cond: (e.organization_id = o.id)
49. 34.115 90.635 ↓ 1.2 51,008 1

Hash Join (cost=2,329.40..4,934.93 rows=42,144 width=52) (actual time=33.657..90.635 rows=51,008 loops=1)

  • Hash Cond: (e.person_id = p.id)
50. 23.321 23.321 ↑ 1.0 51,202 1

Seq Scan on employee e (cost=0.00..2,470.81 rows=51,317 width=52) (actual time=0.015..23.321 rows=51,202 loops=1)

  • Filter: (state = 'ACTIVE'::text)
  • Rows Removed by Filter: 16383
51. 13.546 33.199 ↑ 1.0 41,939 1

Hash (cost=1,803.89..1,803.89 rows=42,041 width=16) (actual time=33.199..33.199 rows=41,939 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 2478kB
52. 19.653 19.653 ↑ 1.0 41,939 1

Seq Scan on person p (cost=0.00..1,803.89 rows=42,041 width=16) (actual time=0.009..19.653 rows=41,939 loops=1)

  • Filter: (state = 'ACTIVE'::text)
  • Rows Removed by Filter: 9252
53. 12.426 29.220 ↑ 1.0 40,249 1

Hash (cost=1,907.53..1,907.53 rows=40,267 width=16) (actual time=29.220..29.220 rows=40,249 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 2399kB
54. 16.794 16.794 ↑ 1.0 40,249 1

Seq Scan on organization o (cost=0.00..1,907.53 rows=40,267 width=16) (actual time=0.009..16.794 rows=40,249 loops=1)

  • Filter: (state = 'ACTIVE'::text)
  • Rows Removed by Filter: 6673
55.          

CTE match

56. 13.424 168,164.942 ↓ 1,734.0 1,734 1

Sort (cost=8.36..8.37 rows=1 width=48) (actual time=168,164.757..168,164.942 rows=1,734 loops=1)

  • Sort Key: ((SubPlan 4))
  • Sort Method: quicksort Memory: 184kB
57. 0.482 168,151.518 ↓ 1,734.0 1,734 1

Subquery Scan on alias_87432128 (cost=0.02..8.35 rows=1 width=48) (actual time=168,147.250..168,151.518 rows=1,734 loops=1)

58. 10.516 168,147.568 ↓ 1,734.0 1,734 1

HashAggregate (cost=0.02..0.03 rows=1 width=16) (actual time=168,147.231..168,147.568 rows=1,734 loops=1)

  • Group Key: "list-condition-0".id
59. 168,137.052 168,137.052 ↓ 3,566.0 3,566 1

CTE Scan on "list-condition-0" (cost=0.00..0.02 rows=1 width=16) (actual time=2,846.692..168,137.052 rows=3,566 loops=1)

60.          

SubPlan (for Subquery Scan)

61. 3.468 3.468 ↑ 1.0 1 1,734

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

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

CTE view

63. 0.033 173,775.369 ↓ 50.0 50 1

Limit (cost=0.00..11,473.58 rows=1 width=264) (actual time=168,317.269..173,775.369 rows=50 loops=1)

64. 0.567 173,775.336 ↓ 50.0 50 1

WindowAgg (cost=0.00..11,473.58 rows=1 width=264) (actual time=168,317.269..173,775.336 rows=50 loops=1)

65. 168,164.819 168,164.819 ↓ 50.0 50 1

CTE Scan on match (cost=0.00..0.02 rows=1 width=48) (actual time=168,164.759..168,164.819 rows=50 loops=1)

66.          

SubPlan (for WindowAgg)

67. 0.300 0.300 ↑ 1.0 1 50

Index Scan using pk_person on person person_3 (cost=0.29..8.31 rows=1 width=7) (actual time=0.006..0.006 rows=1 loops=50)

  • Index Cond: (id = match.record_id)
68. 0.100 0.100 ↑ 1.0 1 50

Index Scan using pk_person on person person_4 (cost=0.29..8.31 rows=1 width=9) (actual time=0.002..0.002 rows=1 loops=50)

  • Index Cond: (id = match.record_id)
69. 0.100 0.950 ↑ 1.0 1 50

Limit (cost=16.76..16.76 rows=1 width=30) (actual time=0.019..0.019 rows=1 loops=50)

70. 0.200 0.850 ↑ 1.0 1 50

Sort (cost=16.76..16.76 rows=1 width=30) (actual time=0.017..0.017 rows=1 loops=50)

  • Sort Key: employee_1.sort_code
  • Sort Method: quicksort Memory: 25kB
71. 0.100 0.650 ↑ 1.0 1 50

Nested Loop (cost=0.71..16.75 rows=1 width=30) (actual time=0.013..0.013 rows=1 loops=50)

72. 0.300 0.300 ↑ 1.0 1 50

Index Scan using employee_person_id_state_idx on employee employee_1 (cost=0.42..8.44 rows=1 width=20) (actual time=0.006..0.006 rows=1 loops=50)

  • Index Cond: ((person_id = match.record_id) AND (state = 'ACTIVE'::text))
73. 0.250 0.250 ↑ 1.0 1 50

Index Scan using pk_organization on organization organization_1 (cost=0.29..8.31 rows=1 width=42) (actual time=0.005..0.005 rows=1 loops=50)

  • Index Cond: (id = employee_1.organization_id)
  • Filter: (state = 'ACTIVE'::text)
74. 0.150 1.950 ↑ 1.0 1 50

Limit (cost=17.74..17.75 rows=1 width=13) (actual time=0.037..0.039 rows=1 loops=50)

75. 0.150 1.800 ↑ 1.0 1 50

Sort (cost=17.74..17.75 rows=1 width=13) (actual time=0.036..0.036 rows=1 loops=50)

  • Sort Key: employee_2.sort_code
  • Sort Method: quicksort Memory: 25kB
76. 0.150 1.650 ↑ 1.0 1 50

Nested Loop (cost=1.41..17.73 rows=1 width=13) (actual time=0.032..0.033 rows=1 loops=50)

77. 0.050 1.000 ↑ 1.0 1 50

Nested Loop (cost=1.12..17.30 rows=1 width=20) (actual time=0.020..0.020 rows=1 loops=50)

78. 0.100 0.350 ↑ 1.0 1 50

Nested Loop (cost=0.71..16.75 rows=1 width=36) (actual time=0.006..0.007 rows=1 loops=50)

79. 0.150 0.150 ↑ 1.0 1 50

Index Scan using employee_person_id_state_idx on employee employee_2 (cost=0.42..8.44 rows=1 width=20) (actual time=0.002..0.003 rows=1 loops=50)

  • Index Cond: ((person_id = match.record_id) AND (state = 'ACTIVE'::text))
80. 0.100 0.100 ↑ 1.0 1 50

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

  • Index Cond: (id = employee_2.organization_id)
  • Filter: (state = 'ACTIVE'::text)
81. 0.600 0.600 ↑ 1.0 1 50

Index Scan using pk_organization_address on organization_address (cost=0.41..0.55 rows=1 width=32) (actual time=0.012..0.012 rows=1 loops=50)

  • Index Cond: ((organization_id = organization_2.id) AND (address_type = 'STANDARD'::text))
82. 0.500 0.500 ↑ 1.0 1 50

Index Scan using address_pkey on address (cost=0.29..0.44 rows=1 width=25) (actual time=0.010..0.010 rows=1 loops=50)

  • Index Cond: (id = organization_address.address_id)
83. 0.100 1.800 ↑ 1.0 1 50

Limit (cost=17.83..17.84 rows=1 width=20) (actual time=0.035..0.036 rows=1 loops=50)

84. 0.250 1.700 ↑ 1.0 1 50

Sort (cost=17.83..17.84 rows=1 width=20) (actual time=0.033..0.034 rows=1 loops=50)

  • Sort Key: employee_3.sort_code
  • Sort Method: quicksort Memory: 25kB
85. 0.100 1.450 ↑ 1.0 1 50

Nested Loop (cost=1.26..17.82 rows=1 width=20) (actual time=0.027..0.029 rows=1 loops=50)

86. 0.100 1.100 ↑ 1.0 2 50

Nested Loop (cost=1.12..17.50 rows=2 width=36) (actual time=0.020..0.022 rows=2 loops=50)

87. 0.100 0.350 ↑ 1.0 1 50

Nested Loop (cost=0.71..16.75 rows=1 width=36) (actual time=0.007..0.007 rows=1 loops=50)

88. 0.150 0.150 ↑ 1.0 1 50

Index Scan using employee_person_id_state_idx on employee employee_3 (cost=0.42..8.44 rows=1 width=20) (actual time=0.003..0.003 rows=1 loops=50)

  • Index Cond: ((person_id = match.record_id) AND (state = 'ACTIVE'::text))
89. 0.100 0.100 ↑ 1.0 1 50

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

  • Index Cond: (id = employee_3.organization_id)
  • Filter: (state = 'ACTIVE'::text)
90. 0.650 0.650 ↑ 1.5 2 50

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.012..0.013 rows=2 loops=50)

  • Index Cond: (organization_id = organization_3.id)
91. 0.250 0.250 ↓ 0.0 0 125

Index Scan using pk_communication_data_type on communication_data_type (cost=0.13..0.15 rows=1 width=16) (actual time=0.002..0.002 rows=0 loops=125)

  • Index Cond: (id = organization_communication_data.communication_data_type_id)
  • Filter: (base_type = 'PHONE'::text)
  • Rows Removed by Filter: 1
92. 0.000 5,604.850 ↑ 1.0 1 50

Aggregate (cost=11,404.56..11,404.57 rows=1 width=32) (actual time=112.097..112.097 rows=1 loops=50)

93.          

Initplan (for Aggregate)

94. 150.600 5,601.700 ↑ 1.0 1 50

Limit (cost=10,199.14..11,387.82 rows=1 width=16) (actual time=112.031..112.034 rows=1 loops=50)

95. 343.000 5,451.100 ↑ 1.0 1 50

Nested Loop (cost=10,199.14..11,387.82 rows=1 width=16) (actual time=109.022..109.022 rows=1 loops=50)

  • Join Filter: (em_1.id = pe_1.id)
  • Rows Removed by Join Filter: 20414
96. 140.800 5,108.100 ↓ 112.8 20,415 50

Subquery Scan on pe_1 (cost=10,194.71..11,370.13 rows=181 width=16) (actual time=84.991..102.162 rows=20,415 loops=50)

  • Filter: (pe_1.row_number = 1)
  • Rows Removed by Filter: 5077
97. 575.400 4,967.300 ↑ 1.4 25,492 50

WindowAgg (cost=10,194.71..10,918.05 rows=36,167 width=44) (actual time=84.989..99.346 rows=25,492 loops=50)

98. 1,823.450 4,391.900 ↑ 1.4 25,492 50

Sort (cost=10,194.71..10,285.12 rows=36,167 width=36) (actual time=84.984..87.838 rows=25,492 loops=50)

  • Sort Key: e_1.person_id, e_1.sort_code
  • Sort Method: external merge Disk: 2160kB
99. 827.909 2,568.450 ↓ 1.4 50,719 50

Hash Join (cost=4,740.26..7,456.43 rows=36,167 width=36) (actual time=0.695..51.369 rows=50,719 loops=50)

  • Hash Cond: (e_1.organization_id = o_1.id)
100. 1,045.085 1,724.500 ↓ 1.2 51,008 50

Hash Join (cost=2,329.40..4,934.93 rows=42,144 width=52) (actual time=0.372..34.490 rows=51,008 loops=50)

  • Hash Cond: (e_1.person_id = p_1.id)
101. 661.750 661.750 ↑ 1.0 51,202 50

Seq Scan on employee e_1 (cost=0.00..2,470.81 rows=51,317 width=52) (actual time=0.010..13.235 rows=51,202 loops=50)

  • Filter: (state = 'ACTIVE'::text)
  • Rows Removed by Filter: 16383
102. 6.699 17.665 ↑ 1.0 41,939 1

Hash (cost=1,803.89..1,803.89 rows=42,041 width=16) (actual time=17.661..17.665 rows=41,939 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 2478kB
103. 10.966 10.966 ↑ 1.0 41,939 1

Seq Scan on person p_1 (cost=0.00..1,803.89 rows=42,041 width=16) (actual time=0.006..10.966 rows=41,939 loops=1)

  • Filter: (state = 'ACTIVE'::text)
  • Rows Removed by Filter: 9252
104. 6.333 16.041 ↑ 1.0 40,249 1

Hash (cost=1,907.53..1,907.53 rows=40,267 width=16) (actual time=16.038..16.041 rows=40,249 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 2399kB
105. 9.708 9.708 ↑ 1.0 40,249 1

Seq Scan on organization o_1 (cost=0.00..1,907.53 rows=40,267 width=16) (actual time=0.010..9.708 rows=40,249 loops=1)

  • Filter: (state = 'ACTIVE'::text)
  • Rows Removed by Filter: 6673
106. 0.000 0.000 ↑ 2.0 1 1,020,750

Materialize (cost=4.43..12.26 rows=2 width=32) (actual time=0.000..0.000 rows=1 loops=1,020,750)

107. 0.300 0.850 ↑ 2.0 1 50

Bitmap Heap Scan on employee em_1 (cost=4.43..12.25 rows=2 width=32) (actual time=0.016..0.017 rows=1 loops=50)

  • Recheck Cond: (person_id = match.record_id)
  • Heap Blocks: exact=50
108. 0.550 0.550 ↑ 2.0 1 50

Bitmap Index Scan on employee_person_id_state_idx (cost=0.00..4.43 rows=2 width=0) (actual time=0.011..0.011 rows=1 loops=50)

  • Index Cond: (person_id = match.record_id)
109. 5,602.050 5,604.000 ↑ 1.0 1 50

Nested Loop Left Join (cost=0.69..16.74 rows=1 width=12) (actual time=112.079..112.080 rows=1 loops=50)

110. 1.150 1.150 ↑ 1.0 1 50

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.022..0.023 rows=1 loops=50)

  • Index Cond: ((target_id = $28) AND (type = 'ORGANIZATION'::text) AND (attribute_id = '198e1d92-d72e-410e-9b0a-b4e84a77fb94'::uuid))
111. 0.800 0.800 ↑ 1.0 1 50

Index Scan using attribute_option_id_varchar_text_idx on attribute_option (cost=0.27..8.29 rows=1 width=28) (actual time=0.016..0.016 rows=1 loops=50)

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

CTE output

113. 0.077 173,776.252 ↓ 25.5 51 1

Sort (cost=0.09..0.10 rows=2 width=264) (actual time=173,776.248..173,776.252 rows=51 loops=1)

  • Sort Key: view.index
  • Sort Method: quicksort Memory: 37kB
114. 0.026 173,776.175 ↓ 25.5 51 1

Append (cost=0.00..0.08 rows=2 width=264) (actual time=168,317.275..173,776.175 rows=51 loops=1)

115. 173,775.479 173,775.479 ↓ 50.0 50 1

CTE Scan on view (cost=0.00..0.02 rows=1 width=264) (actual time=168,317.275..173,775.479 rows=50 loops=1)

116. 0.002 0.670 ↑ 1.0 1 1

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

117. 0.158 0.668 ↑ 1.0 1 1

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

118. 0.510 0.510 ↓ 1,734.0 1,734 1

CTE Scan on match match_1 (cost=0.00..0.02 rows=1 width=16) (actual time=0.000..0.510 rows=1,734 loops=1)

Planning time : 14.437 ms
Execution time : 173,783.045 ms