explain.depesz.com

PostgreSQL's explain analyze made readable

Result: MfJl

Settings
# exclusive inclusive rows x rows loops node
1. 41.426 2,893.862 ↓ 9.3 2,267 1

Merge Left Join (cost=50,338.79..223,299.07 rows=243 width=307) (actual time=931.994..2,893.862 rows=2,267 loops=1)

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

Merge Left Join (cost=50,338.36..128,290.50 rows=243 width=233) (actual time=689.984..1,887.890 rows=2,267 loops=1)

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

Merge Left Join (cost=39,156.77..117,107.90 rows=243 width=201) (actual time=688.396..1,885.704 rows=2,267 loops=1)

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

Merge Left Join (cost=3,853.00..75,926.04 rows=243 width=169) (actual time=457.541..1,574.103 rows=2,267 loops=1)

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

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

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

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

7. 0.116 39.028 ↓ 11.4 228 1

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

8. 0.028 38.684 ↓ 11.4 228 1

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

9. 0.181 38.200 ↓ 11.4 228 1

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

10. 4.184 37.791 ↓ 11.4 228 1

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

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

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

Function Scan on getorgtree t5 (cost=0.25..10.25 rows=1,000 width=4) (actual time=6.159..6.568 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.001 0.194 ↑ 200.0 1 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
16. 0.004 0.193 ↑ 200.0 1 1

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

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

Function Scan on getorgtree t4 (cost=0.25..10.25 rows=1,000 width=4) (actual time=0.189..0.189 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.456 0.456 ↑ 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.002..0.002 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. 23.028 23.028 ↑ 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.014..0.101 rows=10 loops=228)

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

GroupAggregate (cost=0.43..65,973.43 rows=487,823 width=36) (actual time=0.082..1,474.722 rows=763,807 loops=1)

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

Index Scan using ix_dks_1 on roadworkrequestmaterial roadworkrequestmaterial_1 (cost=0.43..53,929.85 rows=1,189,159 width=10) (actual time=0.057..943.369 rows=1,128,453 loops=1)

24. 71.167 303.865 ↓ 1.1 191,332 1

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

  • Group Key: roadworkrequestpenalty_1.roadworkrequestid
25. 138.057 232.698 ↑ 1.1 200,318 1

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

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

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

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

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

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

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

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

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

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

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

  • Group Key: roadworkrequestpenalty_2.roadworkrequestid
31. 0.014 1.585 ↓ 0.0 0 1

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

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

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

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

Seq Scan on penalty penalty_1 (cost=0.00..364.96 rows=1 width=4) (actual time=1.571..1.571 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. 348.523 964.546 ↓ 1.2 935,262 1

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

  • Group Key: roadworkrequestadditional_1.roadworkrequestid
36. 616.023 616.023 ↑ 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.066..616.023 rows=935,263 loops=1)

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