explain.depesz.com

PostgreSQL's explain analyze made readable

Result: hRY4

Settings
# exclusive inclusive rows x rows loops node
1. 0.550 2,560.502 ↓ 1.4 20 1

Sort (cost=55,953.75..55,953.79 rows=14 width=4,277) (actual time=2,560.429..2,560.502 rows=20 loops=1)

  • Sort Key: t5.""IsTop"" DESC, t5.""HasContractWithH24"" DESC, t5.""LastName"", ""p.Employees"".""PartyId"", ""e.Division0"".""Id"", l1.""Id"", ""p.Employees"".""Id"
  • Sort Method: quicksort Memory: 113kB
2. 0.311 2,559.952 ↓ 1.4 20 1

Nested Loop Left Join (cost=53,228.01..55,953.49 rows=14 width=4,277) (actual time=1,299.927..2,559.952 rows=20 loops=1)

3. 100.519 2,559.481 ↓ 1.4 20 1

Hash Join (cost=53,227.86..55,949.77 rows=14 width=4,069) (actual time=1,299.886..2,559.481 rows=20 loops=1)

  • Hash Cond: (""p.Employees"".""PartyId"" = t5.""Id"")
4. 215.015 1,695.382 ↑ 1.0 26,649 1

Hash Join (cost=3,867.73..6,489.56 rows=26,649 width=4,047) (actual time=503.216..1,695.382 rows=26,649 loops=1)

  • Hash Cond: (""p.Employees"".""PositionRefId"" = ""e.PositionRef"".""Id"")
5. 213.978 1,478.158 ↑ 1.0 26,649 1

Hash Left Join (cost=3,818.80..6,369.59 rows=26,649 width=3,811) (actual time=500.987..1,478.158 rows=26,649 loops=1)

  • Hash Cond: ((l1.""LegalFormRefValue"")::text = (""e.Division.LegalEntity.LegalFormRef"".""Value"")::text)
6. 207.922 1,263.995 ↑ 1.0 26,649 1

Hash Left Join (cost=3,806.22..6,269.18 rows=26,649 width=3,085) (actual time=500.765..1,263.995 rows=26,649 loops=1)

  • Hash Cond: (l1.""LogoId"" = ""e.Division.LegalEntity.Logo"".""Id"")
7. 215.131 971.047 ↑ 1.0 26,649 1

Hash Left Join (cost=2,763.16..5,156.14 rows=26,649 width=2,924) (actual time=415.669..971.047 rows=26,649 loops=1)

  • Hash Cond: (""e.Division0"".""LegalEntityId"" = l1.""Id"")
8. 217.149 740.587 ↑ 1.0 26,649 1

Hash Left Join (cost=2,482.76..4,805.60 rows=26,649 width=1,446) (actual time=400.312..740.587 rows=26,649 loops=1)

  • Hash Cond: (""p.Employees"".""DivisionId"" = ""e.Division0"".""Id"")
9. 123.162 123.162 ↑ 1.0 26,649 1

Index Scan using ""Employees_IX_PartyId"" on ""Employees"" ""p.Employees"" (cost=0.29..1,974.47 rows=26,649 width=139) (actual time=0.013..123.162 rows=26,649 loops=1)

  • Filter: (NOT ""IsDeleted"")
10. 13.084 400.276 ↑ 1.0 2,571 1

Hash (cost=2,450.33..2,450.33 rows=2,571 width=1,307) (actual time=400.273..400.276 rows=2,571 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 2559kB
11. 20.162 387.192 ↑ 1.0 2,571 1

Hash Left Join (cost=22.78..2,450.33 rows=2,571 width=1,307) (actual time=0.811..387.192 rows=2,571 loops=1)

  • Hash Cond: (""e.Division0"".""TypeOfDivisionRefId"" = ""e.Division.TypeOfDivisionRef"".""Id"")
12. 184.192 366.277 ↑ 1.0 2,571 1

Merge Left Join (cost=0.57..2,421.07 rows=2,571 width=1,131) (actual time=0.038..366.277 rows=2,571 loops=1)

  • Merge Cond: (""e.Division0"".""LocationId"" = ""e.Division.Location"".""Id"")
13. 11.525 11.525 ↑ 1.0 2,571 1

Index Scan using ""Divisions_IX_LocationRefId"" on ""Divisions"" ""e.Division0"" (cost=0.28..860.57 rows=2,571 width=1,107) (actual time=0.013..11.525 rows=2,571 loops=1)

14. 170.560 170.560 ↑ 1.0 43,528 1

Index Scan using ""PK_dbo.Locations"" on ""Locations"" ""e.Division.Location"" (cost=0.29..1,420.61 rows=43,528 width=24) (actual time=0.011..170.560 rows=43,528 loops=1)

15. 0.379 0.753 ↑ 1.0 95 1

Hash (cost=21.02..21.02 rows=95 width=176) (actual time=0.750..0.753 rows=95 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 29kB
16. 0.374 0.374 ↑ 1.0 95 1

Index Scan using ""PK_dbo.TypeOfDivisionRefs"" on ""TypeOfDivisionRefs"" ""e.Division.TypeOfDivisionRef"" (cost=0.14..21.02 rows=95 width=176) (actual time=0.008..0.374 rows=95 loops=1)

17. 8.131 15.329 ↑ 1.0 1,554 1

Hash (cost=260.98..260.98 rows=1,554 width=1,478) (actual time=15.326..15.329 rows=1,554 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 1632kB
18. 7.124 7.198 ↑ 1.0 1,554 1

Bitmap Heap Scan on ""LegalEntities"" l1 (cost=36.44..260.98 rows=1,554 width=1,478) (actual time=0.096..7.198 rows=1,554 loops=1)

  • Recheck Cond: (NOT ""IsDeleted"")
  • Heap Blocks: exact=209
19. 0.074 0.074 ↑ 1.0 1,554 1

Bitmap Index Scan on ""LegalEntities_IsDeleted_idx"" (cost=0.00..36.05 rows=1,554 width=0) (actual time=0.071..0.074 rows=1,554 loops=1)

20. 41.448 85.026 ↑ 1.0 10,058 1

Hash (cost=917.34..917.34 rows=10,058 width=161) (actual time=85.022..85.026 rows=10,058 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 2065kB
21. 43.578 43.578 ↑ 1.0 10,058 1

Index Scan using ""PK_dbo.Files"" on ""Files"" ""e.Division.LegalEntity.Logo"" (cost=0.29..917.34 rows=10,058 width=161) (actual time=0.017..43.578 rows=10,058 loops=1)

22. 0.080 0.185 ↑ 1.0 16 1

Hash (cost=12.38..12.38 rows=16 width=726) (actual time=0.182..0.185 rows=16 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 14kB
23. 0.105 0.105 ↑ 1.0 16 1

Index Scan using ""IX_LegalFormRefs_Value"" on ""LegalFormRefs"" ""e.Division.LegalEntity.LegalFormRef"" (cost=0.14..12.38 rows=16 width=726) (actual time=0.036..0.105 rows=16 loops=1)

24. 1.109 2.209 ↑ 1.0 273 1

Hash (cost=45.52..45.52 rows=273 width=236) (actual time=2.206..2.209 rows=273 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 82kB
25. 1.100 1.100 ↑ 1.0 273 1

Index Scan using ""PK_dbo.PositionRefs"" on ""PositionRefs"" ""e.PositionRef"" (cost=0.15..45.52 rows=273 width=236) (actual time=0.012..1.100 rows=273 loops=1)

26. 0.055 763.580 ↑ 1.0 10 1

Hash (cost=49,360.01..49,360.01 rows=10 width=22) (actual time=763.577..763.580 rows=10 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
27. 0.075 763.525 ↑ 1.0 10 1

Subquery Scan on t5 (cost=49,359.79..49,360.01 rows=10 width=22) (actual time=763.352..763.525 rows=10 loops=1)

28. 0.078 763.450 ↑ 1.0 10 1

Unique (cost=49,359.79..49,359.91 rows=10 width=22) (actual time=763.344..763.450 rows=10 loops=1)

29. 0.092 763.372 ↑ 1.0 10 1

Sort (cost=49,359.79..49,359.81 rows=10 width=22) (actual time=763.337..763.372 rows=10 loops=1)

  • Sort Key: p0.""Id"", p0.""IsTop"", p0.""HasContractWithH24"", p0.""LastName"
  • Sort Method: quicksort Memory: 25kB
30. 0.069 763.280 ↑ 1.0 10 1

Limit (cost=49,359.50..49,359.52 rows=10 width=22) (actual time=763.187..763.280 rows=10 loops=1)

31. 0.314 763.211 ↑ 3.6 10 1

Sort (cost=49,359.50..49,359.59 rows=36 width=22) (actual time=763.179..763.211 rows=10 loops=1)

  • Sort Key: p0.""IsTop"" DESC, p0.""HasContractWithH24"" DESC, p0.""LastName"", p0.""Id"
  • Sort Method: top-N heapsort Memory: 26kB
32. 0.614 762.897 ↓ 1.2 45 1

Nested Loop Semi Join (cost=44,210.76..49,358.72 rows=36 width=22) (actual time=511.510..762.897 rows=45 loops=1)

  • Join Filter: (p0.""Id"" = e3.""PartyId"")
33. 18.392 760.978 ↓ 1.0 45 1

Hash Semi Join (cost=44,210.20..49,326.53 rows=43 width=30) (actual time=511.442..760.978 rows=45 loops=1)

  • Hash Cond: (p0.""Id"" = e4.""PartyId"")
34. 71.108 741.293 ↑ 1.5 4,751 1

Hash Semi Join (cost=43,943.66..49,039.74 rows=7,043 width=26) (actual time=501.856..741.293 rows=4,751 loops=1)

  • Hash Cond: (p0.""Id"" = e2.""PartyId"")
35. 104.700 183.454 ↓ 1.0 13,009 1

Hash Join (cost=278.89..5,158.10 rows=12,523 width=22) (actual time=13.530..183.454 rows=13,009 loops=1)

  • Hash Cond: (p0.""LegalEntityId"" = l2.""Id"")
36. 66.730 66.730 ↑ 1.0 13,603 1

Index Scan using ""PK_dbo.Parties"" on ""Parties"" p0 (cost=0.29..4,843.22 rows=13,782 width=36) (actual time=0.020..66.730 rows=13,603 loops=1)

  • Filter: ""IsOnPortal"
  • Rows Removed by Filter: 6883
37. 5.509 12.024 ↑ 1.0 1,413 1

Hash (cost=260.94..260.94 rows=1,413 width=4) (actual time=12.021..12.024 rows=1,413 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 66kB
38. 6.449 6.515 ↑ 1.0 1,413 1

Bitmap Heap Scan on ""LegalEntities"" l2 (cost=36.40..260.94 rows=1,413 width=4) (actual time=0.089..6.515 rows=1,413 loops=1)

  • Recheck Cond: (NOT ""IsDeleted"")
  • Filter: ""IsOnPortal"
  • Rows Removed by Filter: 141
  • Heap Blocks: exact=209
39. 0.066 0.066 ↑ 1.0 1,554 1

Bitmap Index Scan on ""LegalEntities_IsDeleted_idx"" (cost=0.00..36.05 rows=1,554 width=0) (actual time=0.063..0.066 rows=1,554 loops=1)

40. 35.000 486.731 ↑ 1.4 8,341 1

Hash (cost=43,520.75..43,520.75 rows=11,521 width=4) (actual time=486.728..486.731 rows=8,341 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 422kB
41. 125.686 451.731 ↑ 1.4 8,341 1

Hash Semi Join (cost=42,703.56..43,520.75 rows=11,521 width=4) (actual time=227.644..451.731 rows=8,341 loops=1)

  • Hash Cond: (e2.""DivisionId"" = d0.""Id"")
42. 98.634 99.184 ↑ 1.0 24,793 1

Bitmap Heap Scan on ""Employees"" e2 (cost=477.05..1,100.78 rows=24,873 width=8) (actual time=0.583..99.184 rows=24,793 loops=1)

  • Recheck Cond: (""DivisionId"" IS NOT NULL)
  • Filter: (NOT ""IsDeleted"")
  • Heap Blocks: exact=372
43. 0.550 0.550 ↑ 1.0 24,793 1

Bitmap Index Scan on ""Employees_IX_DivisionId"" (cost=0.00..470.84 rows=24,873 width=0) (actual time=0.547..0.550 rows=24,793 loops=1)

  • Index Cond: (""DivisionId"" IS NOT NULL)
44. 4.087 226.861 ↓ 1.3 974 1

Hash (cost=42,217.31..42,217.31 rows=736 width=4) (actual time=226.858..226.861 rows=974 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 43kB
45. 7.896 222.774 ↓ 1.3 974 1

Hash Join (cost=278.88..42,217.31 rows=736 width=4) (actual time=11.090..222.774 rows=974 loops=1)

  • Hash Cond: (d0.""LegalEntityId"" = l3.""Id"")
46. 24.932 204.012 ↓ 1.2 982 1

Index Scan using ""Divisions_IsOnPortal_idx"" on ""Divisions"" d0 (cost=0.28..41,936.57 rows=810 width=8) (actual time=0.200..204.012 rows=982 loops=1)

  • Filter: ((SubPlan 1) > 0)
  • Rows Removed by Filter: 1438
47.          

SubPlan (for Index Scan)

48. 29.040 179.080 ↑ 1.0 1 2,420

Aggregate (cost=17.06..17.07 rows=1 width=4) (actual time=0.071..0.074 rows=1 loops=2,420)

49. 44.216 150.040 ↓ 0.0 0 2,420

Nested Loop (cost=0.98..17.06 rows=1 width=0) (actual time=0.054..0.062 rows=0 loops=2,420)

  • Join Filter: (x0.""Address_Id"" = ""x.Address.AddressH240"".""Id"")
50. 49.056 89.540 ↑ 1.0 1 2,420

Nested Loop (cost=0.70..16.73 rows=1 width=8) (actual time=0.024..0.037 rows=1 loops=2,420)

51. 24.200 24.200 ↑ 1.0 1 2,420

Index Only Scan using ""PK_dbo.DivisionAddresses"" on ""DivisionAddresses"" x0 (cost=0.28..8.30 rows=1 width=4) (actual time=0.006..0.010 rows=1 loops=2,420)

  • Index Cond: (""Division_Id"" = d0.""Id"")
  • Heap Fetches: 2714
52. 16.284 16.284 ↑ 1.0 1 2,714

Index Only Scan using ""PK_dbo.Addresses"" on ""Addresses"" ""x.Address0"" (cost=0.42..8.44 rows=1 width=4) (actual time=0.006..0.006 rows=1 loops=2,714)

  • Index Cond: (""Id"" = x0.""Address_Id"")
  • Heap Fetches: 2714
53. 16.284 16.284 ↓ 0.0 0 2,714

Index Scan using ""PK_dbo.AddressH24"" on ""AddressH24"" ""x.Address.AddressH240"" (cost=0.29..0.31 rows=1 width=4) (actual time=0.006..0.006 rows=0 loops=2,714)

  • Index Cond: ((""Id"" = ""x.Address0"".""Id"") AND (""Id"" IS NOT NULL))
  • Filter: (""SettlementId"" = 29219)
  • Rows Removed by Filter: 1
54. 5.148 10.866 ↑ 1.0 1,413 1

Hash (cost=260.94..260.94 rows=1,413 width=4) (actual time=10.863..10.866 rows=1,413 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 66kB
55. 5.647 5.718 ↑ 1.0 1,413 1

Bitmap Heap Scan on ""LegalEntities"" l3 (cost=36.40..260.94 rows=1,413 width=4) (actual time=0.092..5.718 rows=1,413 loops=1)

  • Recheck Cond: (NOT ""IsDeleted"")
  • Filter: ""IsOnPortal"
  • Rows Removed by Filter: 141
  • Heap Blocks: exact=209
56. 0.071 0.071 ↑ 1.0 1,554 1

Bitmap Index Scan on ""LegalEntities_IsDeleted_idx"" (cost=0.00..36.05 rows=1,554 width=0) (actual time=0.068..0.071 rows=1,554 loops=1)

57. 0.526 1.293 ↑ 1.0 124 1

Hash (cost=264.96..264.96 rows=126 width=4) (actual time=1.290..1.293 rows=124 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 13kB
58. 0.740 0.767 ↑ 1.0 124 1

Bitmap Heap Scan on ""Employees"" e4 (cost=5.26..264.96 rows=126 width=4) (actual time=0.042..0.767 rows=124 loops=1)

  • Recheck Cond: (""PositionRefId"" = 123)
  • Filter: (NOT ""IsDeleted"")
  • Heap Blocks: exact=81
59. 0.027 0.027 ↑ 1.0 124 1

Bitmap Index Scan on ""Employees_IX_PositionRefId"" (cost=0.00..5.23 rows=126 width=0) (actual time=0.024..0.027 rows=124 loops=1)

  • Index Cond: (""PositionRefId"" = 123)
60. 0.540 1.305 ↑ 1.0 1 45

Nested Loop (cost=0.57..0.74 rows=1 width=4) (actual time=0.028..0.029 rows=1 loops=45)

61. 0.450 0.450 ↑ 1.0 1 45

Index Scan using ""Employees_IX_PartyId"" on ""Employees"" e3 (cost=0.29..0.38 rows=1 width=8) (actual time=0.010..0.010 rows=1 loops=45)

  • Index Cond: (""PartyId"" = e2.""PartyId"")
  • Filter: ((NOT ""IsDeleted"") AND ""IsOnPortal"" AND (""DivisionId"" IS NOT NULL))
  • Rows Removed by Filter: 0
62. 0.315 0.315 ↑ 1.0 1 45

Index Scan using ""PK_dbo.Divisions"" on ""Divisions"" ""e.Division1"" (cost=0.28..0.36 rows=1 width=4) (actual time=0.007..0.007 rows=1 loops=45)

  • Index Cond: (""Id"" = e3.""DivisionId"")
  • Filter: ""IsOnPortal"
63. 0.160 0.160 ↑ 1.0 1 20

Index Scan using ""PK_dbo.PositionRangeRefs"" on ""PositionRangeRefs"" ""e.PositionRef.PositionRangeRef"" (cost=0.14..0.27 rows=1 width=208) (actual time=0.008..0.008 rows=1 loops=20)

  • Index Cond: (""e.PositionRef"".""PositionRangeRefId"" = ""Id"")
Planning time : 3.748 ms
Execution time : 2,561.601 ms