explain.depesz.com

PostgreSQL's explain analyze made readable

Result: vtCw

Settings
# exclusive inclusive rows x rows loops node
1. 0.634 2,611.357 ↓ 1.4 20 1

Sort (cost=49,673.00..49,673.04 rows=14 width=4,277) (actual time=2,611.288..2,611.357 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.323 2,610.723 ↓ 1.4 20 1

Nested Loop Left Join (cost=48,279.95..49,672.74 rows=14 width=4,277) (actual time=1,549.310..2,610.723 rows=20 loops=1)

3. 103.839 2,610.180 ↓ 1.4 20 1

Hash Join (cost=48,279.80..49,669.03 rows=14 width=4,069) (actual time=1,549.263..2,610.180 rows=20 loops=1)

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

Hash Join (cost=2,007.10..3,296.25 rows=26,649 width=4,047) (actual time=539.989..1,762.769 rows=26,649 loops=1)

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

Hash Left Join (cost=1,990.96..3,209.07 rows=26,649 width=3,811) (actual time=537.919..1,537.570 rows=26,649 loops=1)

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

Hash Left Join (cost=1,989.60..3,119.87 rows=26,649 width=3,085) (actual time=537.756..1,312.781 rows=26,649 loops=1)

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

Hash Left Join (cost=1,510.30..2,570.59 rows=26,649 width=2,924) (actual time=449.453..1,006.009 rows=26,649 loops=1)

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

Hash Left Join (cost=1,266.32..2,256.48 rows=26,649 width=1,446) (actual time=433.238..767.002 rows=26,649 loops=1)

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

Seq Scan on ""Employees"" ""p.Employees"" (cost=0.00..641.49 rows=26,649 width=139) (actual time=0.008..109.720 rows=26,649 loops=1)

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

Hash (cost=1,234.18..1,234.18 rows=2,571 width=1,307) (actual time=433.212..433.216 rows=2,571 loops=1)

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

Hash Left Join (cost=325.98..1,234.18 rows=2,571 width=1,307) (actual time=21.740..418.991 rows=2,571 loops=1)

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

Hash Right Join (cost=320.85..1,222.00 rows=2,571 width=1,131) (actual time=20.974..393.863 rows=2,571 loops=1)

  • Hash Cond: (""e.Division.Location"".""Id"" = ""e.Division0"".""LocationId"")
13. 173.081 173.081 ↑ 1.0 43,528 1

Seq Scan on ""Locations"" ""e.Division.Location"" (cost=0.00..713.28 rows=43,528 width=24) (actual time=0.011..173.081 rows=43,528 loops=1)

14. 10.537 20.948 ↑ 1.0 2,571 1

Hash (cost=288.71..288.71 rows=2,571 width=1,107) (actual time=20.945..20.948 rows=2,571 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 2094kB
15. 10.411 10.411 ↑ 1.0 2,571 1

Seq Scan on ""Divisions"" ""e.Division0"" (cost=0.00..288.71 rows=2,571 width=1,107) (actual time=0.011..10.411 rows=2,571 loops=1)

16. 0.381 0.741 ↑ 1.0 95 1

Hash (cost=3.95..3.95 rows=95 width=176) (actual time=0.738..0.741 rows=95 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 29kB
17. 0.360 0.360 ↑ 1.0 95 1

Seq Scan on ""TypeOfDivisionRefs"" ""e.Division.TypeOfDivisionRef"" (cost=0.00..3.95 rows=95 width=176) (actual time=0.008..0.360 rows=95 loops=1)

18. 8.182 16.195 ↑ 1.0 1,554 1

Hash (cost=224.55..224.55 rows=1,554 width=1,478) (actual time=16.192..16.195 rows=1,554 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 1632kB
19. 8.013 8.013 ↑ 1.0 1,554 1

Seq Scan on ""LegalEntities"" l1 (cost=0.00..224.55 rows=1,554 width=1,478) (actual time=0.016..8.013 rows=1,554 loops=1)

  • Filter: (NOT ""IsDeleted"")
  • Rows Removed by Filter: 1
20. 44.042 88.278 ↑ 1.0 10,058 1

Hash (cost=353.58..353.58 rows=10,058 width=161) (actual time=88.275..88.278 rows=10,058 loops=1)

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

Seq Scan on ""Files"" ""e.Division.LegalEntity.Logo"" (cost=0.00..353.58 rows=10,058 width=161) (actual time=0.011..44.236 rows=10,058 loops=1)

22. 0.070 0.139 ↑ 1.0 16 1

Hash (cost=1.16..1.16 rows=16 width=726) (actual time=0.136..0.139 rows=16 loops=1)

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

Seq Scan on ""LegalFormRefs"" ""e.Division.LegalEntity.LegalFormRef"" (cost=0.00..1.16 rows=16 width=726) (actual time=0.010..0.069 rows=16 loops=1)

24. 1.020 2.057 ↑ 1.0 273 1

Hash (cost=12.73..12.73 rows=273 width=236) (actual time=2.054..2.057 rows=273 loops=1)

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

Seq Scan on ""PositionRefs"" ""e.PositionRef"" (cost=0.00..12.73 rows=273 width=236) (actual time=0.008..1.037 rows=273 loops=1)

26. 0.048 743.572 ↑ 1.0 10 1

Hash (cost=46,272.58..46,272.58 rows=10 width=22) (actual time=743.569..743.572 rows=10 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
27. 0.081 743.524 ↑ 1.0 10 1

Subquery Scan on t5 (cost=46,272.35..46,272.58 rows=10 width=22) (actual time=743.334..743.524 rows=10 loops=1)

28. 0.080 743.443 ↑ 1.0 10 1

Unique (cost=46,272.35..46,272.48 rows=10 width=22) (actual time=743.326..743.443 rows=10 loops=1)

29. 0.098 743.363 ↑ 1.0 10 1

Sort (cost=46,272.35..46,272.38 rows=10 width=22) (actual time=743.318..743.363 rows=10 loops=1)

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

Limit (cost=46,272.06..46,272.08 rows=10 width=22) (actual time=743.161..743.265 rows=10 loops=1)

31. 0.336 743.185 ↑ 3.6 10 1

Sort (cost=46,272.06..46,272.15 rows=36 width=22) (actual time=743.152..743.185 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.622 742.849 ↓ 1.2 45 1

Nested Loop Semi Join (cost=43,678.40..46,271.28 rows=36 width=22) (actual time=495.360..742.849 rows=45 loops=1)

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

Hash Semi Join (cost=43,677.83..46,239.10 rows=43 width=30) (actual time=495.290..740.517 rows=45 loops=1)

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

Hash Semi Join (cost=43,411.30..45,952.30 rows=7,043 width=26) (actual time=491.901..721.450 rows=4,751 loops=1)

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

Hash Join (cost=242.21..2,566.35 rows=12,523 width=22) (actual time=11.059..171.912 rows=13,009 loops=1)

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

Seq Scan on ""Parties"" p0 (cost=0.00..2,287.86 rows=13,782 width=36) (actual time=0.009..60.174 rows=13,603 loops=1)

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

Hash (cost=224.55..224.55 rows=1,413 width=4) (actual time=11.024..11.029 rows=1,413 loops=1)

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

Seq Scan on ""LegalEntities"" l2 (cost=0.00..224.55 rows=1,413 width=4) (actual time=0.008..5.730 rows=1,413 loops=1)

  • Filter: ((NOT ""IsDeleted"") AND ""IsOnPortal"")
  • Rows Removed by Filter: 142
39. 33.444 480.717 ↑ 1.4 8,341 1

Hash (cost=43,025.07..43,025.07 rows=11,521 width=4) (actual time=480.714..480.717 rows=8,341 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 422kB
40. 123.859 447.273 ↑ 1.4 8,341 1

Hash Semi Join (cost=42,190.12..43,025.07 rows=11,521 width=4) (actual time=227.749..447.273 rows=8,341 loops=1)

  • Hash Cond: (e2.""DivisionId"" = d0.""Id"")
41. 95.922 95.922 ↑ 1.0 24,793 1

Seq Scan on ""Employees"" e2 (cost=0.00..641.49 rows=24,873 width=8) (actual time=0.011..95.922 rows=24,793 loops=1)

  • Filter: ((NOT ""IsDeleted"") AND (""DivisionId"" IS NOT NULL))
  • Rows Removed by Filter: 1856
42. 3.886 227.492 ↓ 1.3 974 1

Hash (cost=42,180.92..42,180.92 rows=736 width=4) (actual time=227.488..227.492 rows=974 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 43kB
43. 7.685 223.606 ↓ 1.3 974 1

Hash Join (cost=242.49..42,180.92 rows=736 width=4) (actual time=12.082..223.606 rows=974 loops=1)

  • Hash Cond: (d0.""LegalEntityId"" = l3.""Id"")
44. 24.980 204.060 ↓ 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.202..204.060 rows=982 loops=1)

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

SubPlan (for Index Scan)

46. 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)

47. 41.796 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"")
48. 51.476 91.960 ↑ 1.0 1 2,420

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

49. 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
50. 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
51. 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
52. 5.413 11.861 ↑ 1.0 1,413 1

Hash (cost=224.55..224.55 rows=1,413 width=4) (actual time=11.857..11.861 rows=1,413 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 66kB
53. 6.448 6.448 ↑ 1.0 1,413 1

Seq Scan on ""LegalEntities"" l3 (cost=0.00..224.55 rows=1,413 width=4) (actual time=0.007..6.448 rows=1,413 loops=1)

  • Filter: ((NOT ""IsDeleted"") AND ""IsOnPortal"")
  • Rows Removed by Filter: 142
54. 0.477 1.056 ↑ 1.0 124 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 13kB
55. 0.555 0.579 ↑ 1.0 124 1

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

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

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

  • Index Cond: (""PositionRefId"" = 123)
57. 0.630 1.710 ↑ 1.0 1 45

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

58. 0.720 0.720 ↑ 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.016..0.016 rows=1 loops=45)

  • Index Cond: (""PartyId"" = e2.""PartyId"")
  • Filter: ((NOT ""IsDeleted"") AND ""IsOnPortal"" AND (""DivisionId"" IS NOT NULL))
  • Rows Removed by Filter: 0
59. 0.360 0.360 ↑ 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.008..0.008 rows=1 loops=45)

  • Index Cond: (""Id"" = e3.""DivisionId"")
  • Filter: ""IsOnPortal"
60. 0.220 0.220 ↑ 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.011..0.011 rows=1 loops=20)

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