explain.depesz.com

PostgreSQL's explain analyze made readable

Result: XDpq

Settings
# exclusive inclusive rows x rows loops node
1. 13.153 502.789 ↑ 592.4 7,787 1

Sort (cost=1,980,127.96..1,991,659.53 rows=4,612,630 width=265) (actual time=501.490..502.789 rows=7,787 loops=1)

  • Sort Key: sect."Number
  • Sort Method: quicksort Memory: 2261kB
2.          

CTE sect

3. 5.333 5.333 ↓ 1.3 7,779 1

Index Scan using "IX_Section_RouteId" on "Section" (cost=0.56..7,846.41 rows=6,199 width=56) (actual time=0.053..5.333 rows=7,779 loops=1)

  • Index Cond: ("RouteId" = 'e25b6b4d-7e8d-4ac8-880b-6f0840b2da73'::uuid)
  • Filter: ("vrDeleted" IS FALSE)
  • Rows Removed by Filter: 68
4.          

CTE pipeisolation

5. 1.560 15.304 ↓ 0.0 0 1

Hash Join (cost=183.19..32,769.21 rows=6,270 width=64) (actual time=15.303..15.304 rows=0 loops=1)

  • Hash Cond: (sect_1."Id" = isol."FirstSectionId")
6. 10.319 10.319 ↓ 1.3 7,779 1

CTE Scan on sect sect_1 (cost=0.00..123.98 rows=6,199 width=16) (actual time=0.001..10.319 rows=7,779 loops=1)

7. 1.715 3.425 ↑ 1.0 5,564 1

Hash (cost=113.64..113.64 rows=5,564 width=56) (actual time=3.424..3.425 rows=5,564 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 542kB
8. 1.710 1.710 ↑ 1.0 5,564 1

Seq Scan on "Isolation" isol (cost=0.00..113.64 rows=5,564 width=56) (actual time=0.865..1.710 rows=5,564 loops=1)

9.          

SubPlan (forHash Join)

10. 0.000 0.000 ↓ 0.0 0

Index Scan using "PK_Section" on "Section" "Section_1" (cost=0.56..2.58 rows=1 width=4) (never executed)

  • Index Cond: ("Id" = isol."FirstSectionId")
11. 0.000 0.000 ↓ 0.0 0

Index Scan using "PK_Section" on "Section" "Section_2" (cost=0.56..2.58 rows=1 width=4) (never executed)

  • Index Cond: ("Id" = isol."LastSectionId")
12. 5.801 489.636 ↑ 592.4 7,787 1

Nested Loop Left Join (cost=33.71..1,095,623.74 rows=4,612,630 width=265) (actual time=16.100..489.636 rows=7,787 loops=1)

  • Join Filter: ((pipeisolation."FirstSectionNumber" <= sectdefect."SectionNumber") AND (pipeisolation."LastSectionNumber" >= sectdefect."SectionNumber"))
13. 10.087 468.261 ↓ 1.2 7,787 1

Nested Loop (cost=33.71..57,719.29 rows=6,621 width=201) (actual time=0.793..468.261 rows=7,787 loops=1)

14. 5.522 403.665 ↓ 1.2 7,787 1

Nested Loop Left Join (cost=33.27..42,178.74 rows=6,621 width=193) (actual time=0.762..403.665 rows=7,787 loops=1)

15. 4.177 335.847 ↓ 1.2 7,787 1

Hash Left Join (cost=32.71..25,782.80 rows=6,621 width=189) (actual time=0.730..335.847 rows=7,787 loops=1)

  • Hash Cond: (meas."Id" = stmeas."Id")
16. 4.043 331.632 ↓ 1.2 7,787 1

Hash Left Join (cost=30.95..25,762.91 rows=6,621 width=193) (actual time=0.681..331.632 rows=7,787 loops=1)

  • Hash Cond: (pimr."PipeDataId" = meas."Id")
17. 5.256 327.554 ↓ 1.2 7,787 1

Nested Loop Left Join (cost=28.28..25,742.12 rows=6,621 width=193) (actual time=0.636..327.554 rows=7,787 loops=1)

18. 4.420 260.002 ↓ 1.2 7,787 1

Hash Left Join (cost=27.85..22,637.85 rows=6,621 width=193) (actual time=0.611..260.002 rows=7,787 loops=1)

  • Hash Cond: (bod."ClassId" = acl."EquipmentClassId")
19. 4.293 255.462 ↓ 1.3 7,779 1

Hash Left Join (cost=18.01..21,941.90 rows=6,199 width=168) (actual time=0.486..255.462 rows=7,779 loops=1)

  • Hash Cond: (pm."PipeSupplierId" = ps."Id")
20. 4.249 251.024 ↓ 1.3 7,779 1

Hash Left Join (cost=10.36..21,917.63 rows=6,199 width=148) (actual time=0.331..251.024 rows=7,779 loops=1)

  • Hash Cond: (pm."SteelGradeId" = sg."Id")
21. 3.291 246.633 ↓ 1.3 7,779 1

Hash Left Join (cost=2.69..21,893.41 rows=6,199 width=139) (actual time=0.176..246.633 rows=7,779 loops=1)

  • Hash Cond: (pm."StrengthClassId" = sc."Id")
22. 4.693 243.320 ↓ 1.3 7,779 1

Nested Loop Left Join (cost=1.44..21,875.89 rows=6,199 width=138) (actual time=0.141..243.320 rows=7,779 loops=1)

23. 5.502 176.395 ↓ 1.3 7,779 1

Nested Loop (cost=1.00..18,971.44 rows=6,199 width=84) (actual time=0.118..176.395 rows=7,779 loops=1)

  • Join Filter: (sect."PipeInstallationId" = pi."Id")
24. 3.947 108.661 ↓ 1.3 7,779 1

Nested Loop (cost=0.57..16,079.40 rows=6,199 width=88) (actual time=0.090..108.661 rows=7,779 loops=1)

25. 3.587 3.587 ↓ 1.3 7,779 1

CTE Scan on sect (cost=0.00..123.98 rows=6,199 width=56) (actual time=0.058..3.587 rows=7,779 loops=1)

26. 101.127 101.127 ↑ 1.0 1 7,779

Index Scan using "PK_BaseObject" on "BaseObject" bod (cost=0.57..2.57 rows=1 width=32) (actual time=0.013..0.013 rows=1 loops=7,779)

  • Index Cond: ("Id" = sect."PipeInstallationId")
27. 62.232 62.232 ↑ 1.0 1 7,779

Index Scan using "PK_PipeInstallation" on "PipeInstallation" pi (cost=0.43..0.45 rows=1 width=28) (actual time=0.008..0.008 rows=1 loops=7,779)

  • Index Cond: ("Id" = bod."Id")
28. 62.232 62.232 ↑ 1.0 1 7,779

Index Scan using "PK_PipeMaterial" on "PipeMaterial" pm (cost=0.43..0.47 rows=1 width=70) (actual time=0.008..0.008 rows=1 loops=7,779)

  • Index Cond: ("Id" = pi."Id")
29. 0.008 0.022 ↑ 1.0 11 1

Hash (cost=1.11..1.11 rows=11 width=9) (actual time=0.022..0.022 rows=11 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
30. 0.014 0.014 ↑ 1.0 11 1

Seq Scan on "StrengthClass" sc (cost=0.00..1.11 rows=11 width=9) (actual time=0.010..0.014 rows=11 loops=1)

31. 0.078 0.142 ↑ 1.0 252 1

Hash (cost=4.52..4.52 rows=252 width=25) (actual time=0.142..0.142 rows=252 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 23kB
32. 0.064 0.064 ↑ 1.0 252 1

Seq Scan on "SteelGrade" sg (cost=0.00..4.52 rows=252 width=25) (actual time=0.007..0.064 rows=252 loops=1)

33. 0.074 0.145 ↑ 1.0 207 1

Hash (cost=5.07..5.07 rows=207 width=52) (actual time=0.144..0.145 rows=207 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 26kB
34. 0.071 0.071 ↑ 1.0 207 1

Seq Scan on "PipeSupplier" ps (cost=0.00..5.07 rows=207 width=52) (actual time=0.005..0.071 rows=207 loops=1)

35. 0.040 0.120 ↑ 3.4 63 1

Hash (cost=7.15..7.15 rows=215 width=57) (actual time=0.116..0.120 rows=63 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 14kB
36. 0.080 0.080 ↑ 1.0 215 1

Seq Scan on "ArtifactClass" acl (cost=0.00..7.15 rows=215 width=57) (actual time=0.008..0.080 rows=215 loops=1)

37. 62.296 62.296 ↑ 1.0 1 7,787

Index Scan using "IX_PipeDataPipeInstallationRef_Id" on "PipeDataPipeInstallationRef" pimr (cost=0.43..0.46 rows=1 width=32) (actual time=0.008..0.008 rows=1 loops=7,787)

  • Index Cond: ("PipeInstallationId" = pi."Id")
38. 0.010 0.035 ↑ 1.0 34 1

Hash (cost=2.24..2.24 rows=34 width=16) (actual time=0.035..0.035 rows=34 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
39. 0.025 0.025 ↑ 1.0 34 1

Seq Scan on "PipeData" meas (cost=0.00..2.24 rows=34 width=16) (actual time=0.015..0.025 rows=34 loops=1)

  • Filter: ("ClassId" = '4bed554d-7a16-41e0-a280-a42600dca842'::uuid)
  • Rows Removed by Filter: 65
40. 0.013 0.038 ↑ 1.0 34 1

Hash (cost=1.34..1.34 rows=34 width=28) (actual time=0.038..0.038 rows=34 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
41. 0.025 0.025 ↑ 1.0 34 1

Seq Scan on "StrengthDataComputing" stmeas (cost=0.00..1.34 rows=34 width=28) (actual time=0.006..0.025 rows=34 loops=1)

42. 62.296 62.296 ↑ 1.0 1 7,787

Index Scan using "PK_SectionDefect" on "SectionDefect" sectdefect (cost=0.56..2.48 rows=1 width=20) (actual time=0.008..0.008 rows=1 loops=7,787)

  • Index Cond: ("Id" = sect."Id")
43. 54.509 54.509 ↑ 1.0 1 7,787

Index Scan using "IX_SectionViewId_Uniq" on "SectionView" sview (cost=0.43..2.35 rows=1 width=24) (actual time=0.007..0.007 rows=1 loops=7,787)

  • Index Cond: ("Id" = sect."Id")
44. 15.574 15.574 ↓ 0.0 0 7,787

CTE Scan on pipeisolation (cost=0.00..125.40 rows=6,270 width=64) (actual time=0.002..0.002 rows=0 loops=7,787)

Planning time : 7.401 ms
Execution time : 503.814 ms