explain.depesz.com

PostgreSQL's explain analyze made readable

Result: nYi3 : Optimization for: plan #s1RQ

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 2.163 1,445.377 ↑ 1.0 15 1

Limit (cost=3,884.39..3,885.40 rows=15 width=579) (actual time=1,445.322..1,445.377 rows=15 loops=1)

2. 161.782 1,443.214 ↑ 4.9 45,045 1

WindowAgg (cost=858.47..15,553.63 rows=218,685 width=579) (actual time=1,374.551..1,443.214 rows=45,045 loops=1)

3. 19.301 1,281.432 ↑ 4.9 45,054 1

Hash Join (cost=858.47..7,352.95 rows=218,685 width=544) (actual time=1,125.501..1,281.432 rows=45,054 loops=1)

  • Hash Cond: ("Route"."Id" = "ContractorRouteRef"."RouteId")
4. 18.107 137.881 ↓ 1.1 44,119 1

Hash Join (cost=45.81..3,436.60 rows=38,604 width=544) (actual time=0.927..137.881 rows=44,119 loops=1)

  • Hash Cond: ("Route"."PipelineId" = "Pipeline"."Id")
5. 17.258 119.339 ↓ 1.1 44,119 1

Hash Join (cost=27.87..3,316.45 rows=38,604 width=523) (actual time=0.471..119.339 rows=44,119 loops=1)

  • Hash Cond: ("Artifact"."RepairActualId" = "ActualOverhaulMethod"."Id")
6. 18.804 102.033 ↓ 1.1 44,119 1

Hash Join (cost=25.88..3,203.37 rows=38,604 width=477) (actual time=0.412..102.033 rows=44,119 loops=1)

  • Hash Cond: ("Artifact"."RepairStatedId" = "StatedOverhaulMethod"."Id")
7. 16.056 83.191 ↓ 1.1 44,119 1

Hash Join (cost=23.89..3,090.29 rows=38,604 width=431) (actual time=0.362..83.191 rows=44,119 loops=1)

  • Hash Cond: ("Artifact"."StatusId" = "StatusType"."Id")
8. 17.153 67.124 ↓ 1.1 44,119 1

Hash Join (cost=22.80..2,880.49 rows=38,604 width=408) (actual time=0.322..67.124 rows=44,119 loops=1)

  • Hash Cond: ("Artifact"."OffenceTypeId" = "OffenceType"."Id")
9. 20.112 49.961 ↓ 1.1 44,119 1

Hash Join (cost=21.71..2,670.70 rows=38,604 width=349) (actual time=0.299..49.961 rows=44,119 loops=1)

  • Hash Cond: ("Artifact"."SessionId" = "Session"."Id")
10. 29.648 29.648 ↑ 1.0 44,119 1

Seq Scan on "Artifact" (cost=0.00..2,097.50 rows=44,119 width=238) (actual time=0.011..29.648 rows=44,119 loops=1)

  • Filter: ("StatusId" <> 4)
  • Rows Removed by Filter: 1
11. 0.027 0.201 ↑ 1.0 7 1

Hash (cost=21.63..21.63 rows=7 width=127) (actual time=0.201..0.201 rows=7 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
12. 0.013 0.174 ↑ 1.0 7 1

Nested Loop (cost=0.57..21.63 rows=7 width=127) (actual time=0.057..0.174 rows=7 loops=1)

13. 0.015 0.105 ↑ 1.0 7 1

Nested Loop (cost=0.29..19.52 rows=7 width=52) (actual time=0.039..0.105 rows=7 loops=1)

14. 0.010 0.010 ↑ 1.0 8 1

Seq Scan on "Session" (cost=0.00..1.08 rows=8 width=52) (actual time=0.007..0.010 rows=8 loops=1)

15. 0.080 0.080 ↑ 1.0 1 8

Index Scan using "PK_InnerDiagnosticTarget" on "InnerDiagnosticTarget" (cost=0.29..2.31 rows=1 width=32) (actual time=0.010..0.010 rows=1 loops=8)

  • Index Cond: ("Id" = "Session"."DiagnosticTargetId")
16. 0.056 0.056 ↑ 1.0 1 7

Index Scan using "PK_Route" on "Route" (cost=0.28..0.30 rows=1 width=75) (actual time=0.008..0.008 rows=1 loops=7)

  • Index Cond: ("Id" = "InnerDiagnosticTarget"."StartRouteId")
17. 0.003 0.010 ↑ 1.0 4 1

Hash (cost=1.04..1.04 rows=4 width=61) (actual time=0.010..0.010 rows=4 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
18. 0.007 0.007 ↑ 1.0 4 1

Seq Scan on "OffenceType" (cost=0.00..1.04 rows=4 width=61) (actual time=0.004..0.007 rows=4 loops=1)

19. 0.004 0.011 ↑ 1.0 4 1

Hash (cost=1.04..1.04 rows=4 width=25) (actual time=0.010..0.011 rows=4 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
20. 0.007 0.007 ↑ 1.0 4 1

Seq Scan on "StatusType" (cost=0.00..1.04 rows=4 width=25) (actual time=0.005..0.007 rows=4 loops=1)

21. 0.020 0.038 ↑ 1.0 44 1

Hash (cost=1.44..1.44 rows=44 width=62) (actual time=0.038..0.038 rows=44 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 13kB
22. 0.018 0.018 ↑ 1.0 44 1

Seq Scan on "OverhaulMethod" "StatedOverhaulMethod" (cost=0.00..1.44 rows=44 width=62) (actual time=0.006..0.018 rows=44 loops=1)

23. 0.021 0.048 ↑ 1.0 44 1

Hash (cost=1.44..1.44 rows=44 width=62) (actual time=0.048..0.048 rows=44 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 13kB
24. 0.027 0.027 ↑ 1.0 44 1

Seq Scan on "OverhaulMethod" "ActualOverhaulMethod" (cost=0.00..1.44 rows=44 width=62) (actual time=0.008..0.027 rows=44 loops=1)

25. 0.184 0.435 ↑ 1.0 486 1

Hash (cost=11.86..11.86 rows=486 width=53) (actual time=0.435..0.435 rows=486 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 49kB
26. 0.251 0.251 ↑ 1.0 486 1

Seq Scan on "Pipeline" (cost=0.00..11.86 rows=486 width=53) (actual time=0.011..0.251 rows=486 loops=1)

27. 1.756 1,124.250 ↑ 5.7 3,380 1

Hash (cost=572.47..572.47 rows=19,215 width=48) (actual time=1,124.250..1,124.250 rows=3,380 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 640kB
28. 35.796 1,122.494 ↑ 5.7 3,380 1

Merge Join (cost=202.10..572.47 rows=19,215 width=48) (actual time=1,059.355..1,122.494 rows=3,380 loops=1)

  • Merge Cond: ("ContractorRouteRef"."ContractorId" = "Contractor"."Id")
29. 0.977 0.977 ↑ 1.0 3,380 1

Index Only Scan using "UC_ContractorRouteRef" on "ContractorRouteRef" (cost=0.28..73.98 rows=3,380 width=32) (actual time=0.044..0.977 rows=3,380 loops=1)

  • Heap Fetches: 0
30. 187.354 1,085.721 ↓ 79.6 90,461 1

Sort (cost=201.82..204.66 rows=1,137 width=48) (actual time=1,059.019..1,085.721 rows=90,461 loops=1)

  • Sort Key: "Contractor"."Id
  • Sort Method: quicksort Memory: 31025kB
31. 898.367 898.367 ↓ 79.7 90,626 1

Foreign Scan on staff_contractor_contractor "Contractor" (cost=100.00..144.11 rows=1,137 width=48) (actual time=2.393..898.367 rows=90,626 loops=1)

Planning time : 10.117 ms
Execution time : 1,454.079 ms