explain.depesz.com

PostgreSQL's explain analyze made readable

Result: lMub

Settings
# exclusive inclusive rows x rows loops node
1. 0.101 393.576 ↑ 1.1 228 1

Merge Left Join (cost=124,243.89..339,848.04 rows=243 width=307) (actual time=393.386..393.576 rows=228 loops=1)

  • Merge Cond: (ownrq.id = roadworkrequestadditional.roadworkrequestid)
2. 0.027 393.475 ↑ 1.1 228 1

Merge Left Join (cost=124,243.46..244,839.47 rows=243 width=233) (actual time=393.382..393.475 rows=228 loops=1)

  • Merge Cond: (ownrq.id = roadworkrequestlegalpenalty.roadworkrequestid)
3. 0.024 393.448 ↑ 1.1 228 1

Merge Left Join (cost=113,061.87..233,656.87 rows=243 width=201) (actual time=393.381..393.448 rows=228 loops=1)

  • Merge Cond: (ownrq.id = roadworkrequestpenalty.roadworkrequestid)
4. 0.037 393.424 ↑ 1.1 228 1

Merge Left Join (cost=77,758.09..192,475.01 rows=243 width=169) (actual time=393.380..393.424 rows=228 loops=1)

  • Merge Cond: (ownrq.id = roadworkrequestmaterial.roadworkrequestid)
5. 0.073 393.387 ↑ 1.1 228 1

Sort (cost=77,757.66..77,758.27 rows=243 width=137) (actual time=393.378..393.387 rows=228 loops=1)

  • Sort Key: ownrq.id
  • Sort Method: quicksort Memory: 82kB
6. 53.474 393.314 ↑ 1.1 228 1

Hash Right Join (cost=3,513.45..77,748.03 rows=243 width=137) (actual time=393.270..393.314 rows=228 loops=1)

  • Hash Cond: (ownrq.ownerexportid = re.id)
7. 303.175 303.175 ↑ 1.0 1,134,948 1

Seq Scan on roadworkrequest ownrq (cost=0.00..69,976.72 rows=1,135,272 width=12) (actual time=0.044..303.175 rows=1,134,948 loops=1)

8. 0.092 36.665 ↓ 11.4 228 1

Hash (cost=3,513.20..3,513.20 rows=20 width=125) (actual time=36.665..36.665 rows=228 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 45kB
9. 0.030 36.573 ↓ 11.4 228 1

Nested Loop (cost=43.60..3,513.20 rows=20 width=125) (actual time=15.206..36.573 rows=228 loops=1)

10. 0.117 36.315 ↓ 11.4 228 1

Nested Loop (cost=43.31..3,353.26 rows=20 width=107) (actual time=15.199..36.315 rows=228 loops=1)

11. 0.050 35.970 ↓ 11.4 228 1

Nested Loop (cost=43.03..3,230.33 rows=20 width=78) (actual time=15.194..35.970 rows=228 loops=1)

12. 4.097 35.692 ↓ 11.4 228 1

Hash Join (cost=42.75..3,107.41 rows=20 width=49) (actual time=15.181..35.692 rows=228 loops=1)

  • Hash Cond: (re.exportingorgid = t1.id)
13. 23.104 31.160 ↓ 2,394.7 93,393 1

Seq Scan on roadworkexport re (cost=25.50..3,089.84 rows=39 width=49) (actual time=9.837..31.160 rows=93,393 loops=1)

  • Filter: ((exportdate >= COALESCE(exportdate)) AND (exportdate <= COALESCE(exportdate)) AND (isdone = COALESCE(isdone)) AND ((hashed SubPlan 1) OR (hashed SubPlan 2)))
14.          

SubPlan (for Seq Scan)

15. 8.056 8.056 ↓ 6.5 6,470 1

Function Scan on getorgtree t2 (cost=0.25..10.25 rows=1,000 width=4) (actual time=7.337..8.056 rows=6,470 loops=1)

16. 0.000 0.000 ↓ 0.0 0

Function Scan on getorgtree t3 (cost=0.25..10.25 rows=1,000 width=4) (never executed)

17. 0.005 0.435 ↑ 200.0 1 1

Hash (cost=14.75..14.75 rows=200 width=4) (actual time=0.435..0.435 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
18. 0.007 0.430 ↑ 200.0 1 1

HashAggregate (cost=12.75..14.75 rows=200 width=4) (actual time=0.430..0.430 rows=1 loops=1)

  • Group Key: t1.id
19. 0.423 0.423 ↑ 1,000.0 1 1

Function Scan on getorgtree t1 (cost=0.25..10.25 rows=1,000 width=4) (actual time=0.422..0.423 rows=1 loops=1)

20. 0.228 0.228 ↑ 1.0 1 228

Index Scan using ix_organisation_id on organisation psmorg (cost=0.28..6.15 rows=1 width=33) (actual time=0.001..0.001 rows=1 loops=228)

  • Index Cond: (id = re.psmid)
21. 0.228 0.228 ↑ 1.0 1 228

Index Scan using ix_organisation_id on organisation ownerorg (cost=0.28..6.15 rows=1 width=33) (actual time=0.001..0.001 rows=1 loops=228)

  • Index Cond: (id = re.ownerid)
22. 0.228 0.228 ↑ 1.0 1 228

Index Scan using pk_user on "user" usr (cost=0.29..8.00 rows=1 width=22) (actual time=0.001..0.001 rows=1 loops=228)

  • Index Cond: (id = re.userid)
23. 0.000 0.000 ↓ 0.0 0

GroupAggregate (cost=0.43..108,617.46 rows=487,811 width=36) (never executed)

  • Group Key: roadworkrequestmaterial.roadworkrequestid
24. 0.000 0.000 ↓ 0.0 0

Index Scan using ix_roadworkrequestmaterial_roadworkrequestid on roadworkrequestmaterial (cost=0.43..96,574.17 rows=1,189,131 width=10) (never executed)

  • Filter: ispsm
25. 0.000 0.000 ↓ 0.0 0

GroupAggregate (cost=35,303.77..39,086.66 rows=167,538 width=36) (never executed)

  • Group Key: roadworkrequestpenalty.roadworkrequestid
26. 0.000 0.000 ↓ 0.0 0

Sort (cost=35,303.77..35,866.66 rows=225,155 width=9) (never executed)

  • Sort Key: roadworkrequestpenalty.roadworkrequestid
27. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=592.41..11,436.87 rows=225,155 width=9) (never executed)

  • Hash Cond: (roadworkrequestpenalty.penaltytypeid = penalty.id)
28. 0.000 0.000 ↓ 0.0 0

Seq Scan on roadworkrequestpenalty (cost=0.00..8,001.98 rows=225,155 width=13) (never executed)

  • Filter: ispsm
29. 0.000 0.000 ↓ 0.0 0

Hash (cost=364.96..364.96 rows=18,196 width=4) (never executed)

30. 0.000 0.000 ↓ 0.0 0

Seq Scan on penalty (cost=0.00..364.96 rows=18,196 width=4) (never executed)

  • Filter: (NOT islegalfee)
31. 0.000 0.000 ↓ 0.0 0

GroupAggregate (cost=11,181.59..11,181.83 rows=12 width=36) (never executed)

  • Group Key: roadworkrequestlegalpenalty.roadworkrequestid
32. 0.000 0.000 ↓ 0.0 0

Sort (cost=11,181.59..11,181.62 rows=12 width=9) (never executed)

  • Sort Key: roadworkrequestlegalpenalty.roadworkrequestid
33. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.00..11,181.38 rows=12 width=9) (never executed)

  • Join Filter: (roadworkrequestlegalpenalty.penaltytypeid = penalty_1.id)
34. 0.000 0.000 ↓ 0.0 0

Seq Scan on penalty penalty_1 (cost=0.00..364.96 rows=1 width=4) (never executed)

  • Filter: islegalfee
35. 0.000 0.000 ↓ 0.0 0

Seq Scan on roadworkrequestpenalty roadworkrequestlegalpenalty (cost=0.00..8,001.98 rows=225,155 width=13) (never executed)

  • Filter: ispsm
36. 0.000 0.000 ↓ 0.0 0

GroupAggregate (cost=0.43..85,520.11 rows=758,656 width=36) (never executed)

  • Group Key: roadworkrequestadditional.roadworkrequestid
37. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_roadworkrequestadditional_roadworkrequestid_ispsm on roadworkrequestadditional (cost=0.43..70,988.49 rows=1,009,683 width=7) (never executed)

  • Index Cond: (ispsm = true)
  • Filter: ispsm
Planning time : 5.161 ms