explain.depesz.com

PostgreSQL's explain analyze made readable

Result: YtDJ : Optimization for: Optimization for: plan #VQxf; plan #Jro3

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.032 30,753.073 ↑ 1.0 200 1

Limit (cost=186,561,849.33..186,903,188.83 rows=200 width=900) (actual time=30,704.991..30,753.073 rows=200 loops=1)

2.          

CTE sections

3. 13.288 145.657 ↓ 1.0 15,149 1

WindowAgg (cost=39,071.46..39,333.77 rows=14,989 width=60) (actual time=127.622..145.657 rows=15,149 loops=1)

4. 13.323 132.369 ↓ 1.0 15,149 1

Sort (cost=39,071.46..39,108.93 rows=14,989 width=104) (actual time=127.603..132.369 rows=15,149 loops=1)

  • Sort Key: sec."Number
  • Sort Method: quicksort Memory: 1568kB
5. 7.508 119.046 ↓ 1.0 15,149 1

Hash Left Join (cost=2,146.40..38,031.85 rows=14,989 width=104) (actual time=29.630..119.046 rows=15,149 loops=1)

  • Hash Cond: (ob."Id" = ddoc."DiagnosticObjectId")
6. 3.849 83.877 ↓ 1.0 15,149 1

Nested Loop (cost=189.68..36,013.87 rows=14,989 width=28) (actual time=1.256..83.877 rows=15,149 loops=1)

7. 3.072 4.283 ↓ 1.0 15,149 1

Bitmap Heap Scan on "DiagnosticObject" ob (cost=189.25..10,636.83 rows=15,139 width=16) (actual time=1.237..4.283 rows=15,149 loops=1)

  • Recheck Cond: ("DiagnosticTargetId" = '6847248f-37c1-4f72-ad7a-357e4491c28a'::uuid)
  • Heap Blocks: exact=157
8. 1.211 1.211 ↓ 1.0 15,149 1

Bitmap Index Scan on "IX_DiagnosticSection_DiagnosticTargetId" (cost=0.00..185.47 rows=15,139 width=0) (actual time=1.211..1.211 rows=15,149 loops=1)

  • Index Cond: ("DiagnosticTargetId" = '6847248f-37c1-4f72-ad7a-357e4491c28a'::uuid)
9. 75.745 75.745 ↑ 1.0 1 15,149

Index Scan using "PK_Section" on "Section" sec (cost=0.43..1.67 rows=1 width=28) (actual time=0.005..0.005 rows=1 loops=15,149)

  • Index Cond: ("Id" = ob."Id")
10. 15.434 27.661 ↑ 1.0 49,232 1

Hash (cost=1,341.32..1,341.32 rows=49,232 width=92) (actual time=27.661..27.661 rows=49,232 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 6519kB
11. 12.227 12.227 ↑ 1.0 49,232 1

Seq Scan on "DiagnosticObjectCoordinate" ddoc (cost=0.00..1,341.32 rows=49,232 width=92) (actual time=0.008..12.227 rows=49,232 loops=1)

12.          

CTE marker

13. 2.279 10.073 ↑ 87.9 182 1

Hash Join (cost=292.57..982.24 rows=16,000 width=54) (actual time=5.399..10.073 rows=182 loops=1)

  • Hash Cond: (sections."Id" = mar."SectionId")
14. 2.535 2.535 ↓ 1.0 15,149 1

CTE Scan on sections (cost=0.00..299.78 rows=14,989 width=24) (actual time=0.001..2.535 rows=15,149 loops=1)

15. 3.015 5.259 ↑ 1.0 8,292 1

Hash (cost=188.92..188.92 rows=8,292 width=70) (actual time=5.259..5.259 rows=8,292 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 974kB
16. 2.244 2.244 ↑ 1.0 8,292 1

Seq Scan on "Marker" mar (cost=0.00..188.92 rows=8,292 width=70) (actual time=0.010..2.244 rows=8,292 loops=1)

17.          

CTE dobjects

18. 190.722 507.021 ↓ 1.0 15,149 1

Hash Left Join (cost=2,272.13..48,656.17 rows=14,989 width=814) (actual time=49.516..507.021 rows=15,149 loops=1)

  • Hash Cond: (sect."Id" = diagobjcoord."DiagnosticObjectId")
19. 8.478 271.463 ↓ 1.0 15,149 1

Hash Left Join (cost=192.33..43,667.15 rows=14,989 width=224) (actual time=4.002..271.463 rows=15,149 loops=1)

  • Hash Cond: (sect."PipeTypeId" = pt."Id")
20. 3.286 262.965 ↓ 1.0 15,149 1

Nested Loop Left Join (cost=191.22..43,459.94 rows=14,989 width=206) (actual time=3.965..262.965 rows=15,149 loops=1)

21. 13.156 153.636 ↓ 1.0 15,149 1

Hash Left Join (cost=190.79..36,221.09 rows=14,989 width=62) (actual time=3.944..153.636 rows=15,149 loops=1)

  • Hash Cond: (sect."SectionTypeId" = st."Id")
22. 24.400 140.439 ↓ 1.0 15,149 1

Nested Loop (cost=189.68..36,013.87 rows=14,989 width=48) (actual time=3.877..140.439 rows=15,149 loops=1)

23. 6.199 9.996 ↓ 1.0 15,149 1

Bitmap Heap Scan on "DiagnosticObject" diagobj (cost=189.25..10,636.83 rows=15,139 width=16) (actual time=3.854..9.996 rows=15,149 loops=1)

  • Recheck Cond: ("DiagnosticTargetId" = '6847248f-37c1-4f72-ad7a-357e4491c28a'::uuid)
  • Filter: ("vrDeleted" IS FALSE)
  • Heap Blocks: exact=157
24. 3.797 3.797 ↓ 1.0 15,149 1

Bitmap Index Scan on "IX_DiagnosticSection_DiagnosticTargetId" (cost=0.00..185.47 rows=15,139 width=0) (actual time=3.797..3.797 rows=15,149 loops=1)

  • Index Cond: ("DiagnosticTargetId" = '6847248f-37c1-4f72-ad7a-357e4491c28a'::uuid)
25. 106.043 106.043 ↑ 1.0 1 15,149

Index Scan using "PK_Section" on "Section" sect (cost=0.43..1.67 rows=1 width=48) (actual time=0.007..0.007 rows=1 loops=15,149)

  • Index Cond: ("Id" = diagobj."Id")
26. 0.030 0.041 ↑ 1.0 5 1

Hash (cost=1.05..1.05 rows=5 width=22) (actual time=0.041..0.041 rows=5 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
27. 0.011 0.011 ↑ 1.0 5 1

Seq Scan on "SectionType" st (cost=0.00..1.05 rows=5 width=22) (actual time=0.009..0.011 rows=5 loops=1)

28. 106.043 106.043 ↑ 1.0 1 15,149

Index Scan using "PK_SectionAddonData" on "SectionAddonData" sectionaddondata (cost=0.43..0.47 rows=1 width=160) (actual time=0.006..0.007 rows=1 loops=15,149)

  • Index Cond: ("Id" = sect."Id")
29. 0.016 0.020 ↑ 1.0 5 1

Hash (cost=1.05..1.05 rows=5 width=26) (actual time=0.020..0.020 rows=5 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
30. 0.004 0.004 ↑ 1.0 5 1

Seq Scan on "PipeType" pt (cost=0.00..1.05 rows=5 width=26) (actual time=0.003..0.004 rows=5 loops=1)

31. 23.992 44.836 ↑ 1.0 49,232 1

Hash (cost=1,464.40..1,464.40 rows=49,232 width=92) (actual time=44.836..44.836 rows=49,232 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 6519kB
32. 20.844 20.844 ↑ 1.0 49,232 1

Seq Scan on "DiagnosticObjectCoordinate" diagobjcoord (cost=0.00..1,464.40 rows=49,232 width=92) (actual time=0.009..20.844 rows=49,232 loops=1)

  • Filter: ("CoordinateSystemId" = '72b16dc7-f56e-4203-ba60-7b369f444c88'::uuid)
33.          

CTE darts

34. 2,234.849 29,237.144 ↑ 1.7 57,308 1

Hash Left Join (cost=114,218.24..186,439,535.54 rows=99,452 width=824) (actual time=5,319.663..29,237.144 rows=57,308 loops=1)

  • Hash Cond: (artmeas."SurfaceLocationId" = surfacelocation."Id")
35. 40.040 7,746.785 ↑ 1.7 57,308 1

Hash Left Join (cost=114,217.13..749,597.35 rows=99,452 width=1,403) (actual time=5,308.824..7,746.785 rows=57,308 loops=1)

  • Hash Cond: (sect_1."Id" = diagobjcoord_1."DiagnosticObjectId")
36. 56.297 7,676.901 ↑ 1.7 57,308 1

Nested Loop Left Join (cost=112,137.33..747,110.75 rows=99,452 width=1,343) (actual time=5,278.570..7,676.901 rows=57,308 loops=1)

37. 35.677 7,620.604 ↑ 1.7 57,308 1

Hash Left Join (cost=112,136.90..693,914.92 rows=99,452 width=554) (actual time=5,278.551..7,620.604 rows=57,308 loops=1)

  • Hash Cond: (artifactaddonpof."POFAnomalyClassId" = pofanomalyclass."Id")
38. 43.406 7,584.908 ↑ 1.7 57,308 1

Hash Left Join (cost=112,135.75..692,680.03 rows=99,452 width=565) (actual time=5,278.516..7,584.908 rows=57,308 loops=1)

  • Hash Cond: (artifactclass."DefectTypeId" = defecttype."Id")
39. 60.436 7,541.237 ↑ 1.7 57,308 1

Hash Left Join (cost=112,124.55..691,612.45 rows=99,452 width=577) (actual time=5,278.216..7,541.237 rows=57,308 loops=1)

  • Hash Cond: (artifact."Id" = sac."ArtifactId")
40. 40.839 6,168.210 ↑ 1.7 57,308 1

Hash Left Join (cost=43,995.11..623,086.35 rows=99,452 width=577) (actual time=3,963.881..6,168.210 rows=57,308 loops=1)

  • Hash Cond: (artifactaddonpof."POFArtifactIdentification" = pofartifactidentification."Id")
41. 43.214 6,127.319 ↑ 1.7 57,308 1

Nested Loop Left Join (cost=43,991.85..621,723.72 rows=99,452 width=553) (actual time=3,963.820..6,127.319 rows=57,308 loops=1)

42. 43.605 5,625.641 ↑ 1.7 57,308 1

Hash Left Join (cost=43,991.42..571,135.98 rows=99,452 width=308) (actual time=3,963.793..5,625.641 rows=57,308 loops=1)

  • Hash Cond: (artifactaddonpof."POFArtifactTypeId" = pofartifacttype."Id")
43. 69.605 5,582.012 ↑ 1.7 57,308 1

Nested Loop Left Join (cost=43,989.97..569,767.07 rows=99,452 width=295) (actual time=3,963.756..5,582.012 rows=57,308 loops=1)

44. 46.200 5,053.943 ↑ 1.7 57,308 1

Hash Left Join (cost=43,989.53..521,217.64 rows=99,452 width=247) (actual time=3,963.726..5,053.943 rows=57,308 loops=1)

  • Hash Cond: (artifact."ClassId" = artifactclass."Id")
45. 30.604 5,007.572 ↑ 1.7 57,308 1

Hash Left Join (cost=43,979.78..519,840.43 rows=99,452 width=191) (actual time=3,963.523..5,007.572 rows=57,308 loops=1)

  • Hash Cond: (pipelinesect."PipeInstallationId" = pipeinstallation."Id")
46. 82.613 4,976.953 ↑ 1.7 57,308 1

Nested Loop Left Join (cost=43,940.52..519,426.96 rows=99,452 width=203) (actual time=3,963.472..4,976.953 rows=57,308 loops=1)

47. 2,617.459 4,263.952 ↑ 1.7 57,308 1

Hash Join (cost=43,940.08..471,456.03 rows=99,452 width=161) (actual time=3,963.427..4,263.952 rows=57,308 loops=1)

  • Hash Cond: (artifact."DiagnosticObjectId" = diagobj_1."Id")
48. 1,297.122 1,297.122 ↑ 1.0 9,530,948 1

Seq Scan on "Artifact" artifact (cost=0.00..390,544.77 rows=9,593,777 width=89) (actual time=0.039..1,297.122 rows=9,530,948 loops=1)

49. 10.949 349.371 ↓ 1.0 15,149 1

Hash (cost=43,750.84..43,750.84 rows=15,139 width=104) (actual time=349.371..349.371 rows=15,149 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 1845kB
50. 16.079 338.422 ↓ 1.0 15,149 1

Nested Loop Left Join (cost=10,830.98..43,750.84 rows=15,139 width=104) (actual time=136.695..338.422 rows=15,149 loops=1)

51. 7.137 261.747 ↓ 1.0 15,149 1

Nested Loop Left Join (cost=10,830.55..36,772.87 rows=15,139 width=104) (actual time=136.672..261.747 rows=15,149 loops=1)

52. 6.005 178.865 ↓ 1.0 15,149 1

Hash Left Join (cost=10,830.12..11,395.82 rows=15,139 width=52) (actual time=136.661..178.865 rows=15,149 loops=1)

  • Hash Cond: (diagobj_1."DiagnosticTargetId" = "innerDiagnosticTarget"."Id")
53. 11.846 172.752 ↓ 1.0 15,149 1

Hash Right Join (cost=10,826.07..11,183.61 rows=15,139 width=64) (actual time=136.531..172.752 rows=15,149 loops=1)

  • Hash Cond: (sections_1."Id" = diagobj_1."Id")
54. 152.159 152.159 ↓ 1.0 15,149 1

CTE Scan on sections sections_1 (cost=0.00..299.78 rows=14,989 width=48) (actual time=127.641..152.159 rows=15,149 loops=1)

55. 3.694 8.747 ↓ 1.0 15,149 1

Hash (cost=10,636.83..10,636.83 rows=15,139 width=32) (actual time=8.747..8.747 rows=15,149 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 1075kB
56. 3.514 5.053 ↓ 1.0 15,149 1

Bitmap Heap Scan on "DiagnosticObject" diagobj_1 (cost=189.25..10,636.83 rows=15,139 width=32) (actual time=1.579..5.053 rows=15,149 loops=1)

  • Recheck Cond: ("DiagnosticTargetId" = '6847248f-37c1-4f72-ad7a-357e4491c28a'::uuid)
  • Filter: ("vrDeleted" IS FALSE)
  • Heap Blocks: exact=157
57. 1.539 1.539 ↓ 1.0 15,149 1

Bitmap Index Scan on "IX_DiagnosticSection_DiagnosticTargetId" (cost=0.00..185.47 rows=15,139 width=0) (actual time=1.539..1.539 rows=15,149 loops=1)

  • Index Cond: ("DiagnosticTargetId" = '6847248f-37c1-4f72-ad7a-357e4491c28a'::uuid)
58. 0.002 0.108 ↑ 1.0 1 1

Hash (cost=4.05..4.05 rows=1 width=20) (actual time=0.108..0.108 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
59. 0.051 0.106 ↑ 1.0 1 1

Hash Right Join (cost=2.38..4.05 rows=1 width=20) (actual time=0.101..0.106 rows=1 loops=1)

  • Hash Cond: ("constructiveDiameter"."Id" = "innerDiagnosticTarget"."DiameterId")
60. 0.017 0.017 ↑ 1.0 48 1

Seq Scan on "ConstructiveDiameter" "constructiveDiameter" (cost=0.00..1.48 rows=48 width=8) (actual time=0.013..0.017 rows=48 loops=1)

61. 0.026 0.038 ↑ 1.0 1 1

Hash (cost=2.36..2.36 rows=1 width=20) (actual time=0.038..0.038 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
62. 0.012 0.012 ↑ 1.0 1 1

Index Scan using "PK_InnerDiagnosticTarget" on "InnerDiagnosticTarget" "innerDiagnosticTarget" (cost=0.14..2.36 rows=1 width=20) (actual time=0.011..0.012 rows=1 loops=1)

  • Index Cond: ("Id" = '6847248f-37c1-4f72-ad7a-357e4491c28a'::uuid)
63. 75.745 75.745 ↑ 1.0 1 15,149

Index Scan using "PK_Section" on "Section" sect_1 (cost=0.43..1.67 rows=1 width=52) (actual time=0.005..0.005 rows=1 loops=15,149)

  • Index Cond: ("Id" = diagobj_1."Id")
64. 60.596 60.596 ↑ 1.0 1 15,149

Index Scan using "PK_Section" on "Section" pipelinesect (cost=0.42..0.45 rows=1 width=32) (actual time=0.004..0.004 rows=1 loops=15,149)

  • Index Cond: ("Id" = sect_1."PipelineSectionId")
65. 630.388 630.388 ↑ 1.0 1 57,308

Index Scan using "PK_ArtifactAddonMeasured" on "ArtifactAddonMeasured" artmeas (cost=0.43..0.47 rows=1 width=58) (actual time=0.011..0.011 rows=1 loops=57,308)

  • Index Cond: ("Id" = artifact."Id")
66. 0.001 0.015 ↓ 0.0 0 1

Hash (cost=29.89..29.89 rows=750 width=20) (actual time=0.015..0.015 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
67. 0.001 0.014 ↓ 0.0 0 1

Hash Left Join (cost=2.08..29.89 rows=750 width=20) (actual time=0.014..0.014 rows=0 loops=1)

  • Hash Cond: (pipeinstallation."ConstructiveDiameterId" = constructivediameter."Id")
68. 0.013 0.013 ↓ 0.0 0 1

Seq Scan on "PipeInstallation" pipeinstallation (cost=0.00..17.50 rows=750 width=20) (actual time=0.013..0.013 rows=0 loops=1)

69. 0.000 0.000 ↓ 0.0 0

Hash (cost=1.48..1.48 rows=48 width=8) (never executed)

70. 0.000 0.000 ↓ 0.0 0

Seq Scan on "ConstructiveDiameter" constructivediameter (cost=0.00..1.48 rows=48 width=8) (never executed)

71. 0.097 0.171 ↑ 1.0 211 1

Hash (cost=7.11..7.11 rows=211 width=72) (actual time=0.171..0.171 rows=211 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 29kB
72. 0.074 0.074 ↑ 1.0 211 1

Seq Scan on "ArtifactClass" artifactclass (cost=0.00..7.11 rows=211 width=72) (actual time=0.008..0.074 rows=211 loops=1)

73. 458.464 458.464 ↑ 1.0 1 57,308

Index Scan using "PK_ArtifactAddonPOF" on "ArtifactAddonPOF" artifactaddonpof (cost=0.43..0.48 rows=1 width=64) (actual time=0.008..0.008 rows=1 loops=57,308)

  • Index Cond: ("Id" = artifact."Id")
74. 0.011 0.024 ↑ 1.0 20 1

Hash (cost=1.20..1.20 rows=20 width=45) (actual time=0.024..0.024 rows=20 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
75. 0.013 0.013 ↑ 1.0 20 1

Seq Scan on "POFArtifactType" pofartifacttype (cost=0.00..1.20 rows=20 width=45) (actual time=0.008..0.013 rows=20 loops=1)

76. 458.464 458.464 ↑ 1.0 1 57,308

Index Scan using "PK_ArtifactAddonData" on "ArtifactAddonData" artifactaddondata (cost=0.43..0.50 rows=1 width=261) (actual time=0.007..0.008 rows=1 loops=57,308)

  • Index Cond: ("Id" = artifact."Id")
77. 0.030 0.052 ↑ 1.0 56 1

Hash (cost=2.56..2.56 rows=56 width=56) (actual time=0.052..0.052 rows=56 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 13kB
78. 0.022 0.022 ↑ 1.0 56 1

Seq Scan on "POFArtifactIdentification" pofartifactidentification (cost=0.00..2.56 rows=56 width=56) (actual time=0.009..0.022 rows=56 loops=1)

79. 74.655 1,312.591 ↑ 1.0 228,709 1

Hash (cost=65,270.58..65,270.58 rows=228,709 width=32) (actual time=1,312.591..1,312.591 rows=228,709 loops=1)

  • Buckets: 262144 Batches: 1 Memory Usage: 16343kB
80. 850.665 1,237.936 ↑ 1.0 228,709 1

Hash Right Join (cost=10,551.21..65,270.58 rows=228,709 width=32) (actual time=454.162..1,237.936 rows=228,709 loops=1)

  • Hash Cond: ((calcart."CalculationId" = calc."Id") AND (calcart."ArtifactId" = sac."ArtifactId"))
81. 206.192 206.192 ↑ 1.0 2,036,536 1

Seq Scan on "CalculationArtifact" calcart (cost=0.00..39,399.36 rows=2,036,536 width=48) (actual time=0.012..206.192 rows=2,036,536 loops=1)

82. 65.999 181.079 ↑ 1.0 228,709 1

Hash (cost=7,120.57..7,120.57 rows=228,709 width=32) (actual time=181.079..181.079 rows=228,709 loops=1)

  • Buckets: 262144 Batches: 1 Memory Usage: 16343kB
83. 86.135 115.080 ↑ 1.0 228,709 1

Hash Left Join (cost=6.74..7,120.57 rows=228,709 width=32) (actual time=0.109..115.080 rows=228,709 loops=1)

  • Hash Cond: (sac."CalculationId" = calc."Id")
84. 28.858 28.858 ↑ 1.0 228,709 1

Seq Scan on "ActiveCalculation" sac (cost=0.00..3,969.09 rows=228,709 width=32) (actual time=0.010..28.858 rows=228,709 loops=1)

85. 0.030 0.087 ↑ 1.0 166 1

Hash (cost=4.66..4.66 rows=166 width=16) (actual time=0.087..0.087 rows=166 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 16kB
86. 0.057 0.057 ↑ 1.0 166 1

Seq Scan on "Calculation" calc (cost=0.00..4.66 rows=166 width=16) (actual time=0.007..0.057 rows=166 loops=1)

87. 0.056 0.265 ↑ 1.0 145 1

Hash (cost=9.38..9.38 rows=145 width=20) (actual time=0.265..0.265 rows=145 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 16kB
88. 0.073 0.209 ↑ 1.0 145 1

Hash Left Join (cost=4.04..9.38 rows=145 width=20) (actual time=0.136..0.209 rows=145 loops=1)

  • Hash Cond: (defecttype."Id" = "defectTypeDefectGroupRef"."DefectTypeId")
89. 0.037 0.037 ↑ 1.0 145 1

Seq Scan on "DefectType" defecttype (cost=0.00..3.45 rows=145 width=16) (actual time=0.020..0.037 rows=145 loops=1)

90. 0.069 0.099 ↑ 1.0 135 1

Hash (cost=2.35..2.35 rows=135 width=20) (actual time=0.099..0.099 rows=135 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 15kB
91. 0.030 0.030 ↑ 1.0 135 1

Seq Scan on "DefectTypeDefectGroupRef" "defectTypeDefectGroupRef" (cost=0.00..2.35 rows=135 width=20) (actual time=0.010..0.030 rows=135 loops=1)

92. 0.010 0.019 ↑ 1.0 7 1

Hash (cost=1.07..1.07 rows=7 width=21) (actual time=0.019..0.019 rows=7 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
93. 0.009 0.009 ↑ 1.0 7 1

Seq Scan on "POFAnomalyClass" pofanomalyclass (cost=0.00..1.07 rows=7 width=21) (actual time=0.008..0.009 rows=7 loops=1)

94. 0.000 0.000 ↓ 0.0 0 57,308

Index Scan using "CalculationResult_PK" on "CalculationResult" res (cost=0.43..0.52 rows=1 width=821) (actual time=0.000..0.000 rows=0 loops=57,308)

  • Index Cond: (calcart."Id" = "Id")
95. 16.128 29.844 ↑ 1.0 49,232 1

Hash (cost=1,464.40..1,464.40 rows=49,232 width=92) (actual time=29.844..29.844 rows=49,232 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 6519kB
96. 13.716 13.716 ↑ 1.0 49,232 1

Seq Scan on "DiagnosticObjectCoordinate" diagobjcoord_1 (cost=0.00..1,464.40 rows=49,232 width=92) (actual time=0.012..13.716 rows=49,232 loops=1)

  • Filter: ("CoordinateSystemId" = '72b16dc7-f56e-4203-ba60-7b369f444c88'::uuid)
97. 0.010 0.022 ↑ 1.0 5 1

Hash (cost=1.05..1.05 rows=5 width=29) (actual time=0.022..0.022 rows=5 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
98. 0.012 0.012 ↑ 1.0 5 1

Seq Scan on "SurfaceLocation" surfacelocation (cost=0.00..1.05 rows=5 width=29) (actual time=0.010..0.012 rows=5 loops=1)

99.          

SubPlan (forHash Left Join)

100. 114.616 5,329.644 ↑ 1.0 1 57,308

Limit (cost=466.67..466.67 rows=1 width=8) (actual time=0.092..0.093 rows=1 loops=57,308)

101. 1,260.776 5,215.028 ↑ 5,333.0 1 57,308

Sort (cost=466.67..480.00 rows=5,333 width=8) (actual time=0.091..0.091 rows=1 loops=57,308)

  • Sort Key: marker."MarkerDistanse" DESC
  • Sort Method: top-N heapsort Memory: 25kB
102. 3,954.252 3,954.252 ↑ 50.3 106 57,308

CTE Scan on marker (cost=0.00..440.00 rows=5,333 width=8) (actual time=0.002..0.069 rows=106 loops=57,308)

  • Filter: (((sect_1."Distance")::real + artmeas."Distance") > "MarkerDistanse")
  • Rows Removed by Filter: 76
103. 0.000 4,871.180 ↑ 1.0 1 57,308

Limit (cost=466.67..466.67 rows=1 width=4) (actual time=0.085..0.085 rows=1 loops=57,308)

104. 1,088.852 4,871.180 ↑ 5,333.0 1 57,308

Sort (cost=466.67..480.00 rows=5,333 width=4) (actual time=0.085..0.085 rows=1 loops=57,308)

  • Sort Key: marker_1."MarkerDistanse" DESC
  • Sort Method: top-N heapsort Memory: 25kB
105. 3,782.328 3,782.328 ↑ 50.3 106 57,308

CTE Scan on marker marker_1 (cost=0.00..440.00 rows=5,333 width=4) (actual time=0.001..0.066 rows=106 loops=57,308)

  • Filter: (((sect_1."Distance")::real + artmeas."Distance") > "MarkerDistanse")
  • Rows Removed by Filter: 76
106. 0.000 4,584.640 ↑ 1.0 1 57,308

Limit (cost=466.67..466.67 rows=1 width=8) (actual time=0.080..0.080 rows=1 loops=57,308)

107. 859.620 4,584.640 ↑ 5,333.0 1 57,308

Sort (cost=466.67..480.00 rows=5,333 width=8) (actual time=0.080..0.080 rows=1 loops=57,308)

  • Sort Key: marker_2."MarkerDistanse
  • Sort Method: quicksort Memory: 25kB
108. 3,725.020 3,725.020 ↑ 70.2 76 57,308

CTE Scan on marker marker_2 (cost=0.00..440.00 rows=5,333 width=8) (actual time=0.035..0.065 rows=76 loops=57,308)

  • Filter: (((sect_1."Distance")::real + artmeas."Distance") < "MarkerDistanse")
  • Rows Removed by Filter: 106
109. 57.308 4,470.024 ↑ 1.0 1 57,308

Limit (cost=466.67..466.67 rows=1 width=4) (actual time=0.078..0.078 rows=1 loops=57,308)

110. 745.004 4,412.716 ↑ 5,333.0 1 57,308

Sort (cost=466.67..480.00 rows=5,333 width=4) (actual time=0.077..0.077 rows=1 loops=57,308)

  • Sort Key: marker_3."MarkerDistanse
  • Sort Method: quicksort Memory: 25kB
111. 3,667.712 3,667.712 ↑ 70.2 76 57,308

CTE Scan on marker marker_3 (cost=0.00..440.00 rows=5,333 width=4) (actual time=0.035..0.064 rows=76 loops=57,308)

  • Filter: (((sect_1."Distance")::real + artmeas."Distance") < "MarkerDistanse")
  • Rows Removed by Filter: 106
112.          

CTE dmarks

113. 2.519 17.142 ↓ 2.1 182 1

Nested Loop Left Join (cost=482.67..11,288.16 rows=86 width=848) (actual time=6.317..17.142 rows=182 loops=1)

114. 0.177 13.713 ↓ 2.1 182 1

Nested Loop (cost=482.25..11,226.02 rows=86 width=74) (actual time=6.224..13.713 rows=182 loops=1)

  • Join Filter: (mark."SectionId" = sect_2."Id")
115. 2.171 11.716 ↓ 2.1 182 1

Hash Join (cost=481.82..11,081.65 rows=86 width=70) (actual time=6.199..11.716 rows=182 loops=1)

  • Hash Cond: (diagobj_2."Id" = mark."SectionId")
116. 3.453 4.945 ↓ 1.0 15,149 1

Bitmap Heap Scan on "DiagnosticObject" diagobj_2 (cost=189.25..10,636.83 rows=15,139 width=16) (actual time=1.533..4.945 rows=15,149 loops=1)

  • Recheck Cond: ("DiagnosticTargetId" = '6847248f-37c1-4f72-ad7a-357e4491c28a'::uuid)
  • Filter: ("vrDeleted" IS FALSE)
  • Heap Blocks: exact=157
117. 1.492 1.492 ↓ 1.0 15,149 1

Bitmap Index Scan on "IX_DiagnosticSection_DiagnosticTargetId" (cost=0.00..185.47 rows=15,139 width=0) (actual time=1.492..1.492 rows=15,149 loops=1)

  • Index Cond: ("DiagnosticTargetId" = '6847248f-37c1-4f72-ad7a-357e4491c28a'::uuid)
118. 2.541 4.600 ↑ 1.0 8,292 1

Hash (cost=188.92..188.92 rows=8,292 width=54) (actual time=4.600..4.600 rows=8,292 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 832kB
119. 2.059 2.059 ↑ 1.0 8,292 1

Seq Scan on "Marker" mark (cost=0.00..188.92 rows=8,292 width=54) (actual time=0.012..2.059 rows=8,292 loops=1)

120. 1.820 1.820 ↑ 1.0 1 182

Index Scan using "PK_Section" on "Section" sect_2 (cost=0.43..1.67 rows=1 width=36) (actual time=0.009..0.010 rows=1 loops=182)

  • Index Cond: ("Id" = diagobj_2."Id")
121. 0.910 0.910 ↓ 0.0 0 182

Index Scan using "PK_ObjectCoordinate" on "DiagnosticObjectCoordinate" diagnosticobjectcoordinatetable (cost=0.41..0.53 rows=1 width=92) (actual time=0.005..0.005 rows=0 loops=182)

  • Index Cond: (("DiagnosticObjectId" = diagobj_2."Id") AND ("CoordinateSystemId" = '72b16dc7-f56e-4203-ba60-7b369f444c88'::uuid))
122. 1.524 30,753.041 ↑ 572.6 200 1

Result (cost=22,053.45..195,484,998.03 rows=114,527 width=900) (actual time=30,704.986..30,753.041 rows=200 loops=1)

123. 59.377 30,704.717 ↑ 572.6 200 1

Sort (cost=22,053.45..22,339.77 rows=114,527 width=828) (actual time=30,704.585..30,704.717 rows=200 loops=1)

  • Sort Key: "*SELECT* 1"."Distance", "*SELECT* 1"."Order
  • Sort Method: top-N heapsort Memory: 111kB
124. 462.142 30,645.340 ↑ 1.6 72,639 1

HashAggregate (cost=14,813.13..15,958.40 rows=114,527 width=828) (actual time=30,558.047..30,645.340 rows=72,639 loops=1)

  • Group Key: "*SELECT* 1"."ObjectType", "*SELECT* 1"."ObjectTypePOF", "*SELECT* 1"."Order", "*SELECT* 1"."Distance", "*SELECT* 1"."Number", "*SELECT* 1"."Description", "*SELECT* 1"."DescriptionPOF", "*SELECT* 1"."SectNumber", "*SELECT* 1"."PipeType", "*SELECT* 1"."SectionLength", "*SELECT* 1"."AxialWeldStartAngle", "*SELECT* 1"."AverageWallThickness", "*SELECT* 1"."MeasuredDepth", "*SELECT* 1"."MeasuredDepthInPercent", "*SELECT* 1"."MeasuredLength", "*SELECT* 1"."MeasuredWidth", "*SELECT* 1"."EffectiveLength", "*SELECT* 1"."AngularPosition", "*SELECT* 1"."KeypointAngularPosition", "*SELECT* 1"."PrevDistance", "*SELECT* 1"."NextDistance", "*SELECT* 1"."AxialWeldDistance", "*SELECT* 1"."ERF", "*SELECT* 1"."Psw", "*SELECT* 1"."Danger", "*SELECT* 1"."Lat", "*SELECT* 1"."Lon", "*SELECT* 1"."Alt", "*SELECT* 1"."UpstreamMarkNumber", "*SELECT* 1"."UpstreamMarkNumberDistance", "*SELECT* 1"."DownstreamMarkNumber", "*SELECT* 1"."DownstreamMarkNumberDistance", "*SELECT* 1"."SurfaceLocation", "*SELECT* 1"."DepthInPercent", (("*SELECT* 1"."KeypointDistance")::double precision), "*SELECT* 1"."SizeCategory", "*SELECT* 1"."Coordinates
125. 21.378 30,183.198 ↑ 1.6 72,639 1

Append (cost=0.00..4,219.39 rows=114,527 width=828) (actual time=49.528..30,183.198 rows=72,639 loops=1)

126. 12.507 554.805 ↓ 1.0 15,149 1

Subquery Scan on *SELECT* 1 (cost=0.00..487.14 rows=14,989 width=828) (actual time=49.528..554.805 rows=15,149 loops=1)

127. 542.298 542.298 ↓ 1.0 15,149 1

CTE Scan on dobjects (cost=0.00..299.78 rows=14,989 width=852) (actual time=49.524..542.298 rows=15,149 loops=1)

128. 153.357 29,589.285 ↑ 1.7 57,308 1

Subquery Scan on *SELECT* 2 (cost=0.00..3,729.45 rows=99,452 width=828) (actual time=5,319.685..29,589.285 rows=57,308 loops=1)

129. 29,435.928 29,435.928 ↑ 1.7 57,308 1

CTE Scan on darts (cost=0.00..1,989.04 rows=99,452 width=872) (actual time=5,319.670..29,435.928 rows=57,308 loops=1)

130. 0.165 17.730 ↓ 2.1 182 1

Subquery Scan on *SELECT* 3 (cost=0.00..2.79 rows=86 width=828) (actual time=6.327..17.730 rows=182 loops=1)

131. 17.565 17.565 ↓ 2.1 182 1

CTE Scan on dmarks (cost=0.00..1.72 rows=86 width=852) (actual time=6.322..17.565 rows=182 loops=1)

132.          

SubPlan (forResult)

133. 0.200 7.600 ↑ 1.0 1 200

Limit (cost=426.67..426.67 rows=1 width=8) (actual time=0.038..0.038 rows=1 loops=200)

134. 0.400 7.400 ↑ 5,333.0 1 200

Sort (cost=426.67..440.00 rows=5,333 width=8) (actual time=0.037..0.037 rows=1 loops=200)

  • Sort Key: marker_4."MarkerDistanse" DESC
  • Sort Method: quicksort Memory: 25kB
135. 7.000 7.000 ↑ 5,333.0 1 200

CTE Scan on marker marker_4 (cost=0.00..400.00 rows=5,333 width=8) (actual time=0.005..0.035 rows=1 loops=200)

  • Filter: (("*SELECT* 1"."Distance")::real > "MarkerDistanse")
  • Rows Removed by Filter: 181
136. 0.200 7.400 ↑ 1.0 1 200

Limit (cost=426.67..426.67 rows=1 width=4) (actual time=0.037..0.037 rows=1 loops=200)

137. 0.200 7.200 ↑ 5,333.0 1 200

Sort (cost=426.67..440.00 rows=5,333 width=4) (actual time=0.036..0.036 rows=1 loops=200)

  • Sort Key: marker_5."MarkerDistanse" DESC
  • Sort Method: quicksort Memory: 25kB
138. 7.000 7.000 ↑ 5,333.0 1 200

CTE Scan on marker marker_5 (cost=0.00..400.00 rows=5,333 width=4) (actual time=0.005..0.035 rows=1 loops=200)

  • Filter: (("*SELECT* 1"."Distance")::real > "MarkerDistanse")
  • Rows Removed by Filter: 181
139. 0.200 16.000 ↑ 1.0 1 200

Limit (cost=426.67..426.67 rows=1 width=8) (actual time=0.080..0.080 rows=1 loops=200)

140. 5.800 15.800 ↑ 5,333.0 1 200

Sort (cost=426.67..440.00 rows=5,333 width=8) (actual time=0.079..0.079 rows=1 loops=200)

  • Sort Key: marker_6."MarkerDistanse
  • Sort Method: top-N heapsort Memory: 25kB
141. 10.000 10.000 ↑ 29.5 181 200

CTE Scan on marker marker_6 (cost=0.00..400.00 rows=5,333 width=8) (actual time=0.001..0.050 rows=181 loops=200)

  • Filter: (("*SELECT* 1"."Distance")::real < "MarkerDistanse")
  • Rows Removed by Filter: 1
142. 0.200 15.800 ↑ 1.0 1 200

Limit (cost=426.67..426.67 rows=1 width=4) (actual time=0.079..0.079 rows=1 loops=200)

143. 5.400 15.600 ↑ 5,333.0 1 200

Sort (cost=426.67..440.00 rows=5,333 width=4) (actual time=0.078..0.078 rows=1 loops=200)

  • Sort Key: marker_7."MarkerDistanse
  • Sort Method: top-N heapsort Memory: 25kB
144. 10.200 10.200 ↑ 29.5 181 200

CTE Scan on marker marker_7 (cost=0.00..400.00 rows=5,333 width=4) (actual time=0.001..0.051 rows=181 loops=200)

  • Filter: (("*SELECT* 1"."Distance")::real < "MarkerDistanse")
  • Rows Removed by Filter: 1