explain.depesz.com

PostgreSQL's explain analyze made readable

Result: n1RC

Settings
# exclusive inclusive rows x rows loops node
1. 0.102 34.456 ↑ 1.1 228 1

Merge Left Join (cost=51,242.53..266,846.68 rows=243 width=307) (actual time=34.266..34.456 rows=228 loops=1)

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

Merge Left Join (cost=51,242.10..171,838.11 rows=243 width=233) (actual time=34.260..34.354 rows=228 loops=1)

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

Merge Left Join (cost=40,060.51..160,655.51 rows=243 width=201) (actual time=34.259..34.328 rows=228 loops=1)

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

Merge Left Join (cost=4,756.74..119,473.66 rows=243 width=169) (actual time=34.259..34.308 rows=228 loops=1)

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

Sort (cost=4,756.31..4,756.91 rows=243 width=137) (actual time=34.257..34.267 rows=228 loops=1)

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

Nested Loop Left Join (cost=44.03..4,746.68 rows=243 width=137) (actual time=11.782..34.169 rows=228 loops=1)

7. 0.049 33.822 ↓ 11.4 228 1

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

8. 0.132 33.545 ↓ 11.4 228 1

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

9. 0.077 33.185 ↓ 11.4 228 1

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

10. 4.145 32.880 ↓ 11.4 228 1

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

  • Hash Cond: (re.exportingorgid = t1.id)
11. 23.083 28.611 ↓ 2,394.7 93,393 1

Seq Scan on roadworkexport re (cost=25.50..3,089.84 rows=39 width=49) (actual time=6.492..28.611 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)))
12.          

SubPlan (for Seq Scan)

13. 5.528 5.528 ↓ 6.5 6,470 1

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

14. 0.000 0.000 ↓ 0.0 0

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

15. 0.001 0.124 ↑ 200.0 1 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
16. 0.003 0.123 ↑ 200.0 1 1

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

  • Group Key: t1.id
17. 0.120 0.120 ↑ 1,000.0 1 1

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

18. 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)
19. 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)
20. 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)
21. 0.228 0.228 ↓ 0.0 0 228

Index Scan using ix_roadworkrequest_ownerexportid on roadworkrequest ownrq (cost=0.43..59.58 rows=209 width=12) (actual time=0.001..0.001 rows=0 loops=228)

  • Index Cond: (re.id = ownerexportid)
22. 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
23. 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
24. 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
25. 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
26. 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)
27. 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
28. 0.000 0.000 ↓ 0.0 0

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

29. 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)
30. 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
31. 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
32. 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)
33. 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
34. 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
35. 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
36. 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.710 ms