explain.depesz.com

PostgreSQL's explain analyze made readable

Result: No2g

Settings
# exclusive inclusive rows x rows loops node
1. 0.113 2,916.210 ↑ 1.8 228 1

Sort (cost=447,577.79..447,578.79 rows=400 width=289) (actual time=2,916.202..2,916.210 rows=228 loops=1)

  • Sort Key: (max("*SELECT* 1".exportdate)) DESC
  • Sort Method: quicksort Memory: 83kB
2. 1.671 2,916.097 ↑ 1.8 228 1

GroupAggregate (cost=447,533.28..447,560.50 rows=400 width=289) (actual time=2,914.378..2,916.097 rows=228 loops=1)

  • Group Key: "*SELECT* 1".roadworkexportid, "*SELECT* 1".isdone
3. 1.993 2,914.426 ↓ 5.1 2,495 1

Sort (cost=447,533.28..447,534.49 rows=486 width=283) (actual time=2,914.337..2,914.426 rows=2,495 loops=1)

  • Sort Key: "*SELECT* 1".roadworkexportid, "*SELECT* 1".isdone
  • Sort Method: quicksort Memory: 728kB
4. 0.546 2,912.433 ↓ 5.1 2,495 1

Result (cost=51,242.53..447,511.59 rows=486 width=283) (actual time=40.722..2,912.433 rows=2,495 loops=1)

5. 0.246 2,911.887 ↓ 5.1 2,495 1

Append (cost=51,242.53..447,506.73 rows=486 width=283) (actual time=40.721..2,911.887 rows=2,495 loops=1)

6. 0.020 40.929 ↑ 1.1 228 1

Subquery Scan on *SELECT* 1 (cost=51,242.53..224,205.23 rows=243 width=283) (actual time=40.721..40.929 rows=228 loops=1)

7. 0.111 40.909 ↑ 1.1 228 1

Merge Left Join (cost=51,242.53..224,202.80 rows=243 width=307) (actual time=40.720..40.909 rows=228 loops=1)

  • Merge Cond: (ownrq.id = roadworkrequestadditional.roadworkrequestid)
8. 0.019 40.798 ↑ 1.1 228 1

Merge Left Join (cost=51,242.10..129,194.23 rows=243 width=209) (actual time=40.715..40.798 rows=228 loops=1)

  • Merge Cond: (ownrq.id = roadworkrequestlegalpenalty.roadworkrequestid)
9. 0.023 40.779 ↑ 1.1 228 1

Merge Left Join (cost=40,060.51..118,011.63 rows=243 width=177) (actual time=40.714..40.779 rows=228 loops=1)

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

Merge Left Join (cost=4,756.73..76,829.78 rows=243 width=145) (actual time=40.713..40.756 rows=228 loops=1)

  • Merge Cond: (ownrq.id = roadworkrequestmaterial.roadworkrequestid)
11. 0.089 40.719 ↑ 1.1 228 1

Sort (cost=4,756.31..4,756.91 rows=243 width=113) (actual time=40.711..40.719 rows=228 loops=1)

  • Sort Key: ownrq.id
  • Sort Method: quicksort Memory: 77kB
12. 0.118 40.630 ↑ 1.1 228 1

Nested Loop Left Join (cost=44.03..4,746.68 rows=243 width=113) (actual time=14.046..40.630 rows=228 loops=1)

13. 0.105 40.056 ↓ 11.4 228 1

Nested Loop (cost=43.60..3,513.20 rows=20 width=109) (actual time=14.025..40.056 rows=228 loops=1)

14. 0.000 39.723 ↓ 11.4 228 1

Nested Loop (cost=43.31..3,353.26 rows=20 width=95) (actual time=13.996..39.723 rows=228 loops=1)

15. 0.128 39.282 ↓ 11.4 228 1

Nested Loop (cost=43.03..3,230.33 rows=20 width=70) (actual time=13.981..39.282 rows=228 loops=1)

16. 4.427 38.926 ↓ 11.4 228 1

Hash Join (cost=42.75..3,107.41 rows=20 width=45) (actual time=13.956..38.926 rows=228 loops=1)

  • Hash Cond: (re.exportingorgid = t1.id)
17. 27.475 34.278 ↓ 2,394.7 93,393 1

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

SubPlan (forSeq Scan)

19. 6.803 6.803 ↓ 6.5 6,470 1

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

20. 0.000 0.000 ↓ 0.0 0

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

21. 0.000 0.221 ↑ 200.0 1 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
22. 0.004 0.221 ↑ 200.0 1 1

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

  • Group Key: t1.id
23. 0.217 0.217 ↑ 1,000.0 1 1

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

24. 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)
25. 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)
26. 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)
27. 0.456 0.456 ↓ 0.0 0 228

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

  • Index Cond: (re.id = ownerexportid)
28. 0.000 0.000 ↓ 0.0 0

GroupAggregate (cost=0.43..65,973.43 rows=487,823 width=36) (never executed)

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

Index Scan using ix_dks_1 on roadworkrequestmaterial (cost=0.43..53,929.85 rows=1,189,159 width=10) (never executed)

30. 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
31. 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
32. 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)
33. 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
34. 0.000 0.000 ↓ 0.0 0

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

35. 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)
36. 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
37. 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
38. 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)
39. 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
40. 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
41. 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
42. 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
43. 0.510 2,870.712 ↓ 9.3 2,267 1

Subquery Scan on *SELECT* 2 (cost=50,338.79..223,301.50 rows=243 width=283) (actual time=919.326..2,870.712 rows=2,267 loops=1)

44. 40.558 2,870.202 ↓ 9.3 2,267 1

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

  • Merge Cond: (ownrq_1.id = roadworkrequestadditional_1.roadworkrequestid)
45. 0.605 1,870.010 ↓ 9.3 2,267 1

Merge Left Join (cost=50,338.36..128,290.50 rows=243 width=209) (actual time=676.769..1,870.010 rows=2,267 loops=1)

  • Merge Cond: (ownrq_1.id = roadworkrequestpenalty_2.roadworkrequestid)
46. 7.240 1,867.821 ↓ 9.3 2,267 1

Merge Left Join (cost=39,156.77..117,107.90 rows=243 width=177) (actual time=675.183..1,867.821 rows=2,267 loops=1)

  • Merge Cond: (ownrq_1.id = roadworkrequestpenalty_1.roadworkrequestid)
47. 33.322 1,562.030 ↓ 9.3 2,267 1

Merge Left Join (cost=3,853.00..75,926.04 rows=243 width=145) (actual time=449.125..1,562.030 rows=2,267 loops=1)

  • Merge Cond: (ownrq_1.id = roadworkrequestmaterial_1.roadworkrequestid)
48. 1.617 58.332 ↓ 9.3 2,267 1

Sort (cost=3,852.57..3,853.18 rows=243 width=113) (actual time=57.839..58.332 rows=2,267 loops=1)

  • Sort Key: ownrq_1.id
  • Sort Method: quicksort Memory: 558kB
49. 0.412 56.715 ↓ 9.3 2,267 1

Nested Loop Left Join (cost=44.03..3,842.94 rows=243 width=113) (actual time=11.268..56.715 rows=2,267 loops=1)

50. 0.091 33.047 ↓ 11.4 228 1

Nested Loop (cost=43.60..3,513.20 rows=20 width=109) (actual time=11.226..33.047 rows=228 loops=1)

51. 0.197 32.728 ↓ 11.4 228 1

Nested Loop (cost=43.31..3,353.26 rows=20 width=95) (actual time=11.220..32.728 rows=228 loops=1)

52. 0.158 32.303 ↓ 11.4 228 1

Nested Loop (cost=43.03..3,230.33 rows=20 width=70) (actual time=11.216..32.303 rows=228 loops=1)

53. 4.214 31.917 ↓ 11.4 228 1

Hash Join (cost=42.75..3,107.41 rows=20 width=45) (actual time=11.210..31.917 rows=228 loops=1)

  • Hash Cond: (re_1.exportingorgid = t4.id)
54. 22.328 27.593 ↓ 2,394.7 93,393 1

Seq Scan on roadworkexport re_1 (cost=25.50..3,089.84 rows=39 width=49) (actual time=6.210..27.593 rows=93,393 loops=1)

  • Filter: ((exportdate >= COALESCE(exportdate)) AND (exportdate <= COALESCE(exportdate)) AND (isdone = COALESCE(isdone)) AND ((hashed SubPlan 3) OR (hashed SubPlan 4)))
55.          

SubPlan (forSeq Scan)

56. 5.265 5.265 ↓ 6.5 6,470 1

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

57. 0.000 0.000 ↓ 0.0 0

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

58. 0.001 0.110 ↑ 200.0 1 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
59. 0.005 0.109 ↑ 200.0 1 1

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

  • Group Key: t4.id
60. 0.104 0.104 ↑ 1,000.0 1 1

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

61. 0.228 0.228 ↑ 1.0 1 228

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

  • Index Cond: (id = re_1.psmid)
62. 0.228 0.228 ↑ 1.0 1 228

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

  • Index Cond: (id = re_1.ownerid)
63. 0.228 0.228 ↑ 1.0 1 228

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

  • Index Cond: (id = re_1.userid)
64. 23.256 23.256 ↑ 3.6 10 228

Index Scan using ix_roadworkrequest_psmexportid on roadworkrequest ownrq_1 (cost=0.43..16.13 rows=36 width=8) (actual time=0.014..0.102 rows=10 loops=228)

  • Index Cond: (re_1.id = psmexportid)
65. 528.197 1,470.376 ↓ 1.6 763,807 1

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

  • Group Key: roadworkrequestmaterial_1.roadworkrequestid
66. 942.179 942.179 ↑ 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.051..942.179 rows=1,128,453 loops=1)

67. 70.489 298.551 ↓ 1.1 191,332 1

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

  • Group Key: roadworkrequestpenalty_1.roadworkrequestid
68. 136.161 228.062 ↑ 1.1 200,318 1

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

  • Sort Key: roadworkrequestpenalty_1.roadworkrequestid
  • Sort Method: external merge Disk: 4328kB
69. 29.998 91.901 ↑ 1.0 224,863 1

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

  • Hash Cond: (roadworkrequestpenalty_1.penaltytypeid = penalty_2.id)
70. 57.280 57.280 ↑ 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..57.280 rows=224,863 loops=1)

  • Filter: ispsm
  • Rows Removed by Filter: 148735
71. 1.888 4.623 ↑ 1.0 18,196 1

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

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

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

  • Filter: (NOT islegalfee)
73. 0.001 1.584 ↓ 0.0 0 1

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

  • Group Key: roadworkrequestpenalty_2.roadworkrequestid
74. 0.016 1.583 ↓ 0.0 0 1

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

  • Sort Key: roadworkrequestpenalty_2.roadworkrequestid
  • Sort Method: quicksort Memory: 25kB
75. 0.001 1.567 ↓ 0.0 0 1

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

  • Join Filter: (roadworkrequestpenalty_2.penaltytypeid = penalty_3.id)
76. 1.566 1.566 ↓ 0.0 0 1

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

  • Filter: islegalfee
  • Rows Removed by Filter: 18196
77. 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
78. 344.795 959.634 ↓ 1.2 935,262 1

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

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

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