explain.depesz.com

PostgreSQL's explain analyze made readable

Result: saZ1

Settings
# exclusive inclusive rows x rows loops node
1. 41.557 3,429.376 ↓ 9.3 2,267 1

Merge Left Join (cost=50,338.79..265,942.95 rows=243 width=307) (actual time=1,119.408..3,429.376 rows=2,267 loops=1)

  • Merge Cond: (ownrq.id = roadworkrequestadditional_1.roadworkrequestid)
2. 0.660 2,403.836 ↓ 9.3 2,267 1

Merge Left Join (cost=50,338.37..170,934.38 rows=243 width=233) (actual time=852.047..2,403.836 rows=2,267 loops=1)

  • Merge Cond: (ownrq.id = roadworkrequestpenalty_2.roadworkrequestid)
3. 7.783 2,401.580 ↓ 9.3 2,267 1

Merge Left Join (cost=39,156.77..159,751.78 rows=243 width=201) (actual time=850.449..2,401.580 rows=2,267 loops=1)

  • Merge Cond: (ownrq.id = roadworkrequestpenalty_1.roadworkrequestid)
4. 37.100 2,060.983 ↓ 9.3 2,267 1

Merge Left Join (cost=3,853.00..118,569.92 rows=243 width=169) (actual time=590.489..2,060.983 rows=2,267 loops=1)

  • Merge Cond: (ownrq.id = roadworkrequestmaterial_1.roadworkrequestid)
5. 1.921 64.508 ↓ 9.3 2,267 1

Sort (cost=3,852.57..3,853.18 rows=243 width=137) (actual time=63.951..64.508 rows=2,267 loops=1)

  • Sort Key: ownrq.id
  • Sort Method: quicksort Memory: 699kB
6. 0.502 62.587 ↓ 9.3 2,267 1

Nested Loop Left Join (cost=44.03..3,842.94 rows=243 width=137) (actual time=12.103..62.587 rows=2,267 loops=1)

7. 0.078 34.041 ↓ 11.4 228 1

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

8. 0.151 33.735 ↓ 11.4 228 1

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

9. 0.149 33.356 ↓ 11.4 228 1

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

10. 4.203 32.979 ↓ 11.4 228 1

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

  • Hash Cond: (re.exportingorgid = t4.id)
11. 22.941 28.649 ↓ 2,394.7 93,393 1

Seq Scan on roadworkexport re (cost=25.50..3,089.84 rows=39 width=49) (actual time=6.707..28.649 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.708 5.708 ↓ 6.5 6,470 1

Function Scan on getorgtree t5 (cost=0.25..10.25 rows=1,000 width=4) (actual time=5.225..5.708 rows=6,470 loops=1)

14. 0.000 0.000 ↓ 0.0 0

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

15. 0.002 0.127 ↑ 200.0 1 1

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

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

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

  • Group Key: t4.id
17. 0.122 0.122 ↑ 1,000.0 1 1

Function Scan on getorgtree t4 (cost=0.25..10.25 rows=1,000 width=4) (actual time=0.122..0.122 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. 28.044 28.044 ↑ 3.6 10 228

Index Scan using ix_roadworkrequest_psmexportid on roadworkrequest ownrq (cost=0.43..16.13 rows=36 width=12) (actual time=0.017..0.123 rows=10 loops=228)

  • Index Cond: (re.id = psmexportid)
22. 421.107 1,959.375 ↓ 1.6 763,807 1

GroupAggregate (cost=0.43..108,617.46 rows=487,811 width=36) (actual time=0.110..1,959.375 rows=763,807 loops=1)

  • Group Key: roadworkrequestmaterial_1.roadworkrequestid
23. 1,538.268 1,538.268 ↑ 1.1 1,128,453 1

Index Scan using ix_roadworkrequestmaterial_roadworkrequestid on roadworkrequestmaterial roadworkrequestmaterial_1 (cost=0.43..96,574.17 rows=1,189,131 width=10) (actual time=0.077..1,538.268 rows=1,128,453 loops=1)

  • Filter: ispsm
  • Rows Removed by Filter: 899384
24. 71.003 332.814 ↓ 1.1 191,332 1

GroupAggregate (cost=35,303.77..39,086.66 rows=167,538 width=36) (actual time=247.593..332.814 rows=191,332 loops=1)

  • Group Key: roadworkrequestpenalty_1.roadworkrequestid
25. 138.503 261.811 ↑ 1.1 200,318 1

Sort (cost=35,303.77..35,866.66 rows=225,155 width=9) (actual time=247.585..261.811 rows=200,318 loops=1)

  • Sort Key: roadworkrequestpenalty_1.roadworkrequestid
  • Sort Method: external merge Disk: 4328kB
26. 31.214 123.308 ↑ 1.0 224,863 1

Hash Join (cost=592.41..11,436.87 rows=225,155 width=9) (actual time=20.840..123.308 rows=224,863 loops=1)

  • Hash Cond: (roadworkrequestpenalty_1.penaltytypeid = penalty.id)
27. 87.218 87.218 ↑ 1.0 224,863 1

Seq Scan on roadworkrequestpenalty roadworkrequestpenalty_1 (cost=0.00..8,001.98 rows=225,155 width=13) (actual time=15.855..87.218 rows=224,863 loops=1)

  • Filter: ispsm
  • Rows Removed by Filter: 148735
28. 1.794 4.876 ↑ 1.0 18,196 1

Hash (cost=364.96..364.96 rows=18,196 width=4) (actual time=4.876..4.876 rows=18,196 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 896kB
29. 3.082 3.082 ↑ 1.0 18,196 1

Seq Scan on penalty (cost=0.00..364.96 rows=18,196 width=4) (actual time=0.030..3.082 rows=18,196 loops=1)

  • Filter: (NOT islegalfee)
30. 0.001 1.596 ↓ 0.0 0 1

GroupAggregate (cost=11,181.59..11,181.83 rows=12 width=36) (actual time=1.596..1.596 rows=0 loops=1)

  • Group Key: roadworkrequestpenalty_2.roadworkrequestid
31. 0.015 1.595 ↓ 0.0 0 1

Sort (cost=11,181.59..11,181.62 rows=12 width=9) (actual time=1.595..1.595 rows=0 loops=1)

  • Sort Key: roadworkrequestpenalty_2.roadworkrequestid
  • Sort Method: quicksort Memory: 25kB
32. 0.000 1.580 ↓ 0.0 0 1

Nested Loop (cost=0.00..11,181.38 rows=12 width=9) (actual time=1.580..1.580 rows=0 loops=1)

  • Join Filter: (roadworkrequestpenalty_2.penaltytypeid = penalty_1.id)
33. 1.580 1.580 ↓ 0.0 0 1

Seq Scan on penalty penalty_1 (cost=0.00..364.96 rows=1 width=4) (actual time=1.580..1.580 rows=0 loops=1)

  • Filter: islegalfee
  • Rows Removed by Filter: 18196
34. 0.000 0.000 ↓ 0.0 0

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

  • Filter: ispsm
35. 346.339 983.983 ↓ 1.2 935,262 1

GroupAggregate (cost=0.43..85,520.11 rows=758,656 width=36) (actual time=0.096..983.983 rows=935,262 loops=1)

  • Group Key: roadworkrequestadditional_1.roadworkrequestid
36. 637.644 637.644 ↑ 1.1 935,263 1

Index Scan using idx_roadworkrequestadditional_roadworkrequestid_ispsm on roadworkrequestadditional roadworkrequestadditional_1 (cost=0.43..70,988.49 rows=1,009,683 width=7) (actual time=0.081..637.644 rows=935,263 loops=1)

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