explain.depesz.com

PostgreSQL's explain analyze made readable

Result: dOrX

Settings
# exclusive inclusive rows x rows loops node
1. 117.923 155,435.796 ↓ 2.3 36,985 1

Nested Loop Left Join (cost=3,983,823.35..8,107,927.69 rows=16,008 width=792) (actual time=79,151.275..155,435.796 rows=36,985 loops=1)

2. 10.427 155,169.933 ↓ 2.3 36,985 1

Hash Left Join (cost=3,983,822.92..8,070,140.16 rows=16,008 width=568) (actual time=79,151.056..155,169.933 rows=36,985 loops=1)

  • Hash Cond: (roadworkrequest.roadworkrequestownercontractororganisationid = organisation_owner_contractor.id)
3. 75.558 155,156.763 ↓ 2.3 36,985 1

Hash Left Join (cost=3,983,368.77..8,069,643.98 rows=16,008 width=543) (actual time=79,148.298..155,156.763 rows=36,985 loops=1)

  • Hash Cond: (roadworkrequest.id = rw.id)
4. 14.431 153,819.733 ↓ 2.3 36,985 1

Hash Left Join (cost=3,629,720.42..7,712,723.60 rows=16,008 width=539) (actual time=77,886.473..153,819.733 rows=36,985 loops=1)

  • Hash Cond: (roadworkrequest.id = mi_31_datameldingen_helper.rwr_id)
5. 41.602 151,093.556 ↓ 2.3 36,985 1

Hash Left Join (cost=3,381,462.97..7,464,424.13 rows=16,008 width=507) (actual time=75,174.695..151,093.556 rows=36,985 loops=1)

  • Hash Cond: (roadworkrequest.id = mi_31_datameldingen_totaaladditionelekosten.id)
6. 14.852 148,076.350 ↓ 2.3 36,985 1

Hash Left Join (cost=3,171,096.72..7,254,015.86 rows=16,008 width=475) (actual time=72,198.895..148,076.350 rows=36,985 loops=1)

  • Hash Cond: (roadworkrequest.id = mi_31_datamaterialen.rwr_id)
7. 446.986 144,722.448 ↓ 2.3 36,985 1

Hash Join (cost=2,976,430.34..7,059,307.46 rows=16,008 width=443) (actual time=68,859.704..144,722.448 rows=36,985 loops=1)

  • Hash Cond: (roadworkrequest.plannedroadworkrequestdatesid = roadworkrequestdates_planned.id)
8. 75.073 137,568.137 ↓ 4.2 306,127 1

Hash Left Join (cost=2,939,423.63..7,009,578.01 rows=73,422 width=423) (actual time=39,886.583..137,568.137 rows=306,127 loops=1)

  • Hash Cond: (roadworkrequest.id = mi_31_datameldingen_totaaltoeslagenkosten.id)
9. 602.329 133,810.606 ↓ 4.2 306,127 1

Hash Right Join (cost=2,768,861.92..6,838,823.57 rows=73,422 width=391) (actual time=36,204.110..133,810.606 rows=306,127 loops=1)

  • Hash Cond: (mi_31_datameldingen_timebetween.id = roadworkrequest.id)
10. 60.051 108,819.694 ↓ 13.2 303,393 1

Subquery Scan on mi_31_datameldingen_timebetween (cost=462,460.31..4,528,467.88 rows=22,899 width=20) (actual time=11,809.111..108,819.694 rows=303,393 loops=1)

11. 4,137.168 108,759.643 ↓ 13.2 303,393 1

Nested Loop (cost=462,460.31..4,528,238.89 rows=22,899 width=986) (actual time=11,809.109..108,759.643 rows=303,393 loops=1)

12. 314.382 94,913.867 ↓ 6.6 303,394 1

Nested Loop (cost=462,459.88..3,223,796.74 rows=45,798 width=24) (actual time=11,804.272..94,913.867 rows=303,394 loops=1)

13. 848.061 13,630.238 ↓ 3.4 310,227 1

Hash Join (cost=462,459.44..709,198.54 rows=91,595 width=28) (actual time=11,787.712..13,630.238 rows=310,227 loops=1)

  • Hash Cond: (rwrh_19.roadworkrequestid = roadworkrequest_3.id)
14. 122.897 1,070.506 ↑ 1.0 1,203,923 1

Nested Loop (cost=22,561.05..251,494.42 rows=1,204,209 width=16) (actual time=72.166..1,070.506 rows=1,203,923 loops=1)

15. 0.028 0.028 ↑ 1.0 1 1

Seq Scan on roadworkstatustype roadworkstatustype_1_1 (cost=0.00..1.29 rows=1 width=4) (actual time=0.025..0.028 rows=1 loops=1)

  • Filter: (id = 19)
  • Rows Removed by Filter: 22
16. 883.291 947.581 ↑ 1.0 1,203,923 1

Bitmap Heap Scan on roadworkrequesthistory rwrh_19 (cost=22,561.05..239,451.04 rows=1,204,209 width=20) (actual time=72.131..947.581 rows=1,203,923 loops=1)

  • Recheck Cond: (roadworkstatustypeid = 19)
  • Rows Removed by Index Recheck: 4,080,477
  • Heap Blocks: exact=59,167 lossy=33,031
17. 64.290 64.290 ↑ 1.0 1,203,923 1

Bitmap Index Scan on ix_roadworkrequesttypeid (cost=0.00..22,260.00 rows=1,204,209 width=0) (actual time=64.290..64.290 rows=1,203,923 loops=1)

  • Index Cond: (roadworkstatustypeid = 19)
18. 118.532 11,711.671 ↓ 2.9 304,353 1

Hash (cost=437,975.69..437,975.69 rows=104,696 width=20) (actual time=11,711.671..11,711.671 rows=304,353 loops=1)

  • Buckets: 65,536 (originally 65536) Batches: 8 (originally 2) Memory Usage: 3,585kB
19. 30.898 11,593.139 ↓ 2.9 304,353 1

Nested Loop (cost=101,894.68..437,975.69 rows=104,696 width=20) (actual time=622.329..11,593.139 rows=304,353 loops=1)

20. 0.021 0.021 ↑ 1.0 1 1

Seq Scan on roadworkstatustype roadworkstatustype_1 (cost=0.00..1.29 rows=1 width=4) (actual time=0.019..0.021 rows=1 loops=1)

  • Filter: (id = 16)
  • Rows Removed by Filter: 22
21. 0.000 11,562.220 ↓ 2.9 304,353 1

Nested Loop (cost=101,894.68..436,927.44 rows=104,696 width=24) (actual time=622.298..11,562.220 rows=304,353 loops=1)

22. 70.676 2,440.665 ↓ 2.9 304,353 1

Hash Join (cost=101,894.25..351,590.50 rows=104,696 width=28) (actual time=599.978..2,440.665 rows=304,353 loops=1)

  • Hash Cond: (org_psm.parentid = parentorg_1.id)
23. 78.654 2,367.470 ↓ 2.9 304,353 1

Hash Join (cost=101,440.11..350,861.36 rows=104,696 width=32) (actual time=597.450..2,367.470 rows=304,353 loops=1)

  • Hash Cond: (roadworkrequest_3.roadworkrequestpsmorganisationid = org_psm.id)
24. 85.291 2,287.040 ↓ 2.9 304,353 1

Hash Join (cost=100,985.97..350,132.23 rows=104,696 width=32) (actual time=595.663..2,287.040 rows=304,353 loops=1)

  • Hash Cond: (roadworkrequest_3.roadworkrequestownerorganisationid = org_owner.id)
25. 618.098 2,200.322 ↓ 2.9 304,353 1

Hash Join (cost=100,531.82..349,403.09 rows=104,696 width=36) (actual time=594.205..2,200.322 rows=304,353 loops=1)

  • Hash Cond: (rwrh_16.roadworkrequestid = roadworkrequest_3.id)
26. 999.506 1,180.201 ↓ 2.9 1,288,712 1

Bitmap Heap Scan on roadworkrequesthistory rwrh_16 (cost=24,207.31..265,143.93 rows=442,151 width=20) (actual time=188.039..1,180.201 rows=1,288,712 loops=1)

  • Recheck Cond: (roadworkstatustypeid = 16)
  • Rows Removed by Index Recheck: 4,039,128
  • Filter: (userid = 1)
  • Rows Removed by Filter: 25,536
  • Heap Blocks: exact=52,972 lossy=33,039
27. 180.695 180.695 ↓ 1.0 1,314,248 1

Bitmap Index Scan on ix_roadworkrequesttypeid (cost=0.00..24,096.77 rows=1,303,511 width=0) (actual time=180.694..180.695 rows=1,314,248 loops=1)

  • Index Cond: (roadworkstatustypeid = 16)
28. 59.601 402.023 ↑ 1.0 324,725 1

Hash (cost=70,658.44..70,658.44 rows=325,926 width=16) (actual time=402.023..402.023 rows=324,725 loops=1)

  • Buckets: 131,072 Batches: 8 Memory Usage: 2,919kB
29. 316.180 342.422 ↑ 1.0 324,725 1

Bitmap Heap Scan on roadworkrequest roadworkrequest_3 (cost=5,743.81..70,658.44 rows=325,926 width=16) (actual time=32.874..342.422 rows=324,725 loops=1)

  • Recheck Cond: (roadworkrequestpsmorganisationid = ANY ('{1792,1793,1655,1656,250,249,251,243,244,248,247,246,245,1034,1035,1036,1033,254,231,240,239,241,242}'::integer[]))
  • Heap Blocks: exact=51,974
30. 26.242 26.242 ↑ 1.0 324,725 1

Bitmap Index Scan on ix_roadworkrequest_roadworkrequestpsmorganisationid (cost=0.00..5,662.33 rows=325,926 width=0) (actual time=26.242..26.242 rows=324,725 loops=1)

  • Index Cond: (roadworkrequestpsmorganisationid = ANY ('{1792,1793,1655,1656,250,249,251,243,244,248,247,246,245,1034,1035,1036,1033,254,231,240,239,241,242}'::integer[]))
31. 0.630 1.427 ↑ 1.0 7,073 1

Hash (cost=365.73..365.73 rows=7,073 width=4) (actual time=1.427..1.427 rows=7,073 loops=1)

  • Buckets: 8,192 Batches: 1 Memory Usage: 313kB
32. 0.797 0.797 ↑ 1.0 7,073 1

Seq Scan on organisation org_owner (cost=0.00..365.73 rows=7,073 width=4) (actual time=0.020..0.797 rows=7,073 loops=1)

33. 0.600 1.776 ↑ 1.0 7,073 1

Hash (cost=365.73..365.73 rows=7,073 width=8) (actual time=1.776..1.776 rows=7,073 loops=1)

  • Buckets: 8,192 Batches: 1 Memory Usage: 341kB
34. 1.176 1.176 ↑ 1.0 7,073 1

Seq Scan on organisation org_psm (cost=0.00..365.73 rows=7,073 width=8) (actual time=0.014..1.176 rows=7,073 loops=1)

35. 0.615 2.519 ↑ 1.0 7,073 1

Hash (cost=365.73..365.73 rows=7,073 width=4) (actual time=2.519..2.519 rows=7,073 loops=1)

  • Buckets: 8,192 Batches: 1 Memory Usage: 313kB
36. 1.904 1.904 ↑ 1.0 7,073 1

Seq Scan on organisation parentorg_1 (cost=0.00..365.73 rows=7,073 width=4) (actual time=0.019..1.904 rows=7,073 loops=1)

37. 9,130.590 9,130.590 ↑ 1.0 1 304,353

Index Only Scan using pk_roadworkrequestdates on roadworkrequestdates (cost=0.43..0.82 rows=1 width=4) (actual time=0.030..0.030 rows=1 loops=304,353)

  • Index Cond: (id = roadworkrequest_3.actualroadworkrequestdatesid)
  • Heap Fetches: 304,353
38. 22,956.798 80,969.247 ↑ 1.0 1 310,227

Index Scan using pk_history on roadworkrequesthistory tmp19 (cost=0.43..27.45 rows=1 width=4) (actual time=0.261..0.261 rows=1 loops=310,227)

  • Index Cond: (id = rwrh_19.id)
  • Filter: (SubPlan 1)
  • Rows Removed by Filter: 0
39.          

SubPlan (for Index Scan)

40. 310.227 58,012.449 ↑ 1.0 1 310,227

Aggregate (cost=26.54..26.55 rows=1 width=4) (actual time=0.187..0.187 rows=1 loops=310,227)

41. 57,702.222 57,702.222 ↑ 1.0 1 310,227

Index Scan using ix_roadworkrequesthistory_timestamp_roadworkid on roadworkrequesthistory roadworkrequesthistory_4 (cost=0.56..26.54 rows=1 width=4) (actual time=0.171..0.186 rows=1 loops=310,227)

  • Index Cond: (roadworkrequestid = tmp19.roadworkrequestid)
  • Filter: (tmp19.roadworkstatustypeid = roadworkstatustypeid)
  • Rows Removed by Filter: 10
42. 7,584.850 9,708.608 ↑ 1.0 1 303,394

Index Scan using pk_history on roadworkrequesthistory tmp16 (cost=0.43..28.23 rows=1 width=4) (actual time=0.032..0.032 rows=1 loops=303,394)

  • Index Cond: (id = rwrh_16.id)
  • Filter: (SubPlan 2)
  • Rows Removed by Filter: 0
43.          

SubPlan (for Index Scan)

44. 303.394 2,123.758 ↑ 1.0 1 303,394

Aggregate (cost=26.54..26.55 rows=1 width=4) (actual time=0.007..0.007 rows=1 loops=303,394)

45. 1,820.364 1,820.364 ↑ 1.0 1 303,394

Index Scan using ix_roadworkrequesthistory_timestamp_roadworkid on roadworkrequesthistory roadworkrequesthistory_1_1 (cost=0.56..26.54 rows=1 width=4) (actual time=0.004..0.006 rows=1 loops=303,394)

  • Index Cond: (roadworkrequestid = tmp16.roadworkrequestid)
  • Filter: (tmp16.roadworkstatustypeid = roadworkstatustypeid)
  • Rows Removed by Filter: 10
46. 183.617 24,388.583 ↓ 4.2 306,127 1

Hash (cost=2,301,897.83..2,301,897.83 rows=73,422 width=375) (actual time=24,388.583..24,388.583 rows=306,127 loops=1)

  • Buckets: 16,384 (originally 16384) Batches: 32 (originally 8) Memory Usage: 3,969kB
47. 505.009 24,204.966 ↓ 4.2 306,127 1

Hash Right Join (cost=2,045,181.08..2,301,897.83 rows=73,422 width=375) (actual time=22,109.514..24,204.966 rows=306,127 loops=1)

  • Hash Cond: (roadworkrequest_1.id = roadworkrequest.id)
48. 591.032 2,277.472 ↓ 1.0 1,189,081 1

Hash Join (cost=110,639.21..347,338.86 rows=1,174,553 width=12) (actual time=674.748..2,277.472 rows=1,189,081 loops=1)

  • Hash Cond: (roadworkrequesthistory.roadworkrequestid = roadworkrequest_1.id)
49. 1,027.519 1,189.491 ↓ 1.0 1,189,081 1

Bitmap Heap Scan on roadworkrequesthistory (cost=22,007.22..238,774.66 rows=1,174,553 width=12) (actual time=170.807..1,189.491 rows=1,189,081 loops=1)

  • Recheck Cond: (roadworkstatustypeid = 18)
  • Rows Removed by Index Recheck: 4,086,160
  • Heap Blocks: exact=62,055 lossy=33,031
50. 161.972 161.972 ↓ 1.0 1,189,081 1

Bitmap Index Scan on ix_roadworkrequesttypeid (cost=0.00..21,713.58 rows=1,174,553 width=0) (actual time=161.972..161.972 rows=1,189,081 loops=1)

  • Index Cond: (roadworkstatustypeid = 18)
51. 172.334 496.949 ↓ 1.0 1,376,619 1

Hash (cost=66,049.44..66,049.44 rows=1,376,444 width=4) (actual time=496.949..496.949 rows=1,376,619 loops=1)

  • Buckets: 131,072 Batches: 32 Memory Usage: 2,539kB
52. 324.615 324.615 ↓ 1.0 1,376,619 1

Seq Scan on roadworkrequest roadworkrequest_1 (cost=0.00..66,049.44 rows=1,376,444 width=4) (actual time=0.027..324.615 rows=1,376,619 loops=1)

53. 182.764 21,422.485 ↓ 4.2 306,127 1

Hash (cost=1,930,110.09..1,930,110.09 rows=73,422 width=367) (actual time=21,422.485..21,422.485 rows=306,127 loops=1)

  • Buckets: 16,384 (originally 16384) Batches: 32 (originally 8) Memory Usage: 3,969kB
54. 499.525 21,239.721 ↓ 4.2 306,127 1

Hash Right Join (cost=1,674,127.47..1,930,110.09 rows=73,422 width=367) (actual time=19,658.663..21,239.721 rows=306,127 loops=1)

  • Hash Cond: (roadworkrequest_2.id = roadworkrequest.id)
55. 575.722 1,767.190 ↓ 1.0 1,163,064 1

Hash Join (cost=110,236.06..346,577.97 rows=1,152,985 width=12) (actual time=674.693..1,767.190 rows=1,163,064 loops=1)

  • Hash Cond: (roadworkrequesthistory_1.roadworkrequestid = roadworkrequest_2.id)
56. 535.302 696.222 ↓ 1.0 1,163,064 1

Bitmap Heap Scan on roadworkrequesthistory roadworkrequesthistory_1 (cost=21,604.07..238,282.38 rows=1,152,985 width=12) (actual time=169.567..696.222 rows=1,163,064 loops=1)

  • Recheck Cond: (roadworkstatustypeid = 15)
  • Heap Blocks: exact=62,861
57. 160.920 160.920 ↓ 1.0 1,163,064 1

Bitmap Index Scan on ix_roadworkrequesttypeid (cost=0.00..21,315.82 rows=1,152,985 width=0) (actual time=160.920..160.920 rows=1,163,064 loops=1)

  • Index Cond: (roadworkstatustypeid = 15)
58. 175.135 495.246 ↓ 1.0 1,376,619 1

Hash (cost=66,049.44..66,049.44 rows=1,376,444 width=4) (actual time=495.246..495.246 rows=1,376,619 loops=1)

  • Buckets: 131,072 Batches: 32 Memory Usage: 2,539kB
59. 320.111 320.111 ↓ 1.0 1,376,619 1

Seq Scan on roadworkrequest roadworkrequest_2 (cost=0.00..66,049.44 rows=1,376,444 width=4) (actual time=0.031..320.111 rows=1,376,619 loops=1)

60. 173.293 18,973.006 ↓ 4.2 306,127 1

Hash (cost=1,559,531.64..1,559,531.64 rows=73,422 width=359) (actual time=18,973.006..18,973.006 rows=306,127 loops=1)

  • Buckets: 16,384 (originally 16384) Batches: 32 (originally 8) Memory Usage: 3,969kB
61. 281.228 18,799.713 ↓ 4.2 306,127 1

Hash Join (cost=1,465,701.08..1,559,531.64 rows=73,422 width=359) (actual time=17,377.674..18,799.713 rows=306,127 loops=1)

  • Hash Cond: (rwr.id = roadworkrequest.id)
62. 355.034 14,857.572 ↑ 1.0 324,725 1

Hash Left Join (cost=1,369,165.20..1,443,289.31 rows=325,926 width=20) (actual time=13,716.710..14,857.572 rows=324,725 loops=1)

  • Hash Cond: (rwr.id = rwh19.roadworkrequestid)
63. 493.117 10,185.159 ↑ 1.0 324,725 1

Hash Left Join (cost=682,996.07..752,182.67 rows=325,926 width=12) (actual time=9,398.957..10,185.159 rows=324,725 loops=1)

  • Hash Cond: (rwr.id = rwh20.roadworkrequestid)
64. 306.958 334.156 ↑ 1.0 324,725 1

Bitmap Heap Scan on roadworkrequest rwr (cost=5,743.81..70,658.44 rows=325,926 width=4) (actual time=33.902..334.156 rows=324,725 loops=1)

  • Recheck Cond: (roadworkrequestpsmorganisationid = ANY ('{1792,1793,1655,1656,250,249,251,243,244,248,247,246,245,1034,1035,1036,1033,254,231,240,239,241,242}'::integer[]))
  • Heap Blocks: exact=51,974
65. 27.198 27.198 ↑ 1.0 324,725 1

Bitmap Index Scan on ix_roadworkrequest_roadworkrequestpsmorganisationid (cost=0.00..5,662.33 rows=325,926 width=0) (actual time=27.198..27.198 rows=324,725 loops=1)

  • Index Cond: (roadworkrequestpsmorganisationid = ANY ('{1792,1793,1655,1656,250,249,251,243,244,248,247,246,245,1034,1035,1036,1033,254,231,240,239,241,242}'::integer[]))
66. 207.482 9,357.886 ↓ 16.0 1,109,268 1

Hash (cost=676,049.83..676,049.83 rows=69,154 width=12) (actual time=9,357.886..9,357.886 rows=1,109,268 loops=1)

  • Buckets: 131,072 (originally 131072) Batches: 16 (originally 2) Memory Usage: 4,009kB
67. 507.281 9,150.404 ↓ 16.0 1,109,268 1

Hash Join (cost=634,343.64..676,049.83 rows=69,154 width=12) (actual time=8,233.330..9,150.404 rows=1,109,268 loops=1)

  • Hash Cond: (rwh20min.expr1 = rwh20.id)
68. 45.853 1,695.661 ↓ 1.4 1,109,268 1

Subquery Scan on rwh20min (cost=375,603.98..403,263.69 rows=801,326 width=4) (actual time=1,276.395..1,695.661 rows=1,109,268 loops=1)

69. 281.082 1,649.808 ↓ 1.4 1,109,268 1

GroupAggregate (cost=375,603.98..395,250.43 rows=801,326 width=12) (actual time=1,276.393..1,649.808 rows=1,109,268 loops=1)

  • Group Key: roadworkrequesthistory_2.roadworkrequestid, roadworkrequesthistory_2.roadworkstatustypeid
70. 409.535 1,368.726 ↑ 1.0 1,123,405 1

Sort (cost=375,603.98..378,512.28 rows=1,163,319 width=12) (actual time=1,276.380..1,368.726 rows=1,123,405 loops=1)

  • Sort Key: roadworkrequesthistory_2.roadworkrequestid
  • Sort Method: external merge Disk: 24,232kB
71. 894.905 959.191 ↑ 1.0 1,123,405 1

Bitmap Heap Scan on roadworkrequesthistory roadworkrequesthistory_2 (cost=21,796.16..238,517.17 rows=1,163,319 width=12) (actual time=72.247..959.191 rows=1,123,405 loops=1)

  • Recheck Cond: (roadworkstatustypeid = 20)
  • Rows Removed by Index Recheck: 4,111,496
  • Heap Blocks: exact=61,617 lossy=33,028
72. 64.286 64.286 ↑ 1.0 1,123,405 1

Bitmap Index Scan on ix_roadworkrequesttypeid (cost=0.00..21,505.33 rows=1,163,319 width=0) (actual time=64.286..64.286 rows=1,123,405 loops=1)

  • Index Cond: (roadworkstatustypeid = 20)
73. 367.976 6,947.462 ↑ 1.0 1,123,405 1

Hash (cost=238,517.17..238,517.17 rows=1,163,319 width=16) (actual time=6,947.462..6,947.462 rows=1,123,405 loops=1)

  • Buckets: 131,072 Batches: 32 Memory Usage: 2,666kB
74. 6,415.135 6,579.486 ↑ 1.0 1,123,405 1

Bitmap Heap Scan on roadworkrequesthistory rwh20 (cost=21,796.16..238,517.17 rows=1,163,319 width=16) (actual time=187.146..6,579.486 rows=1,123,405 loops=1)

  • Recheck Cond: (roadworkstatustypeid = 20)
  • Rows Removed by Index Recheck: 4,111,496
  • Heap Blocks: exact=61,617 lossy=33,028
75. 164.351 164.351 ↑ 1.0 1,123,405 1

Bitmap Index Scan on ix_roadworkrequesttypeid (cost=0.00..21,505.33 rows=1,163,319 width=0) (actual time=164.351..164.351 rows=1,123,405 loops=1)

  • Index Cond: (roadworkstatustypeid = 20)
76. 186.853 4,317.379 ↓ 16.3 1,192,916 1

Hash (cost=684,896.35..684,896.35 rows=73,182 width=12) (actual time=4,317.379..4,317.379 rows=1,192,916 loops=1)

  • Buckets: 131,072 (originally 131072) Batches: 32 (originally 2) Memory Usage: 3,073kB
77. 537.200 4,130.526 ↓ 16.3 1,192,916 1

Hash Join (cost=642,037.72..684,896.35 rows=73,182 width=12) (actual time=3,141.023..4,130.526 rows=1,192,916 loops=1)

  • Hash Cond: (rwh19min.expr1 = rwh19.id)
78. 48.753 1,761.008 ↓ 1.5 1,192,916 1

Subquery Scan on rwh19min (cost=381,654.07..410,080.28 rows=819,206 width=4) (actual time=1,306.717..1,761.008 rows=1,192,916 loops=1)

79. 302.092 1,712.255 ↓ 1.5 1,192,916 1

GroupAggregate (cost=381,654.07..401,888.22 rows=819,206 width=12) (actual time=1,306.715..1,712.255 rows=1,192,916 loops=1)

  • Group Key: roadworkrequesthistory_3.roadworkrequestid, roadworkrequesthistory_3.roadworkstatustypeid
80. 445.320 1,410.163 ↑ 1.0 1,203,923 1

Sort (cost=381,654.07..384,664.59 rows=1,204,209 width=12) (actual time=1,306.707..1,410.163 rows=1,203,923 loops=1)

  • Sort Key: roadworkrequesthistory_3.roadworkrequestid
  • Sort Method: external merge Disk: 25,960kB
81. 892.049 964.843 ↑ 1.0 1,203,923 1

Bitmap Heap Scan on roadworkrequesthistory roadworkrequesthistory_3 (cost=22,561.05..239,451.04 rows=1,204,209 width=12) (actual time=80.903..964.843 rows=1,203,923 loops=1)

  • Recheck Cond: (roadworkstatustypeid = 19)
  • Rows Removed by Index Recheck: 4,080,477
  • Heap Blocks: exact=59,167 lossy=33,031
82. 72.794 72.794 ↑ 1.0 1,203,923 1

Bitmap Index Scan on ix_roadworkrequesttypeid (cost=0.00..22,260.00 rows=1,204,209 width=0) (actual time=72.794..72.794 rows=1,203,923 loops=1)

  • Index Cond: (roadworkstatustypeid = 19)
83. 206.755 1,832.318 ↑ 1.0 1,203,923 1

Hash (cost=239,451.04..239,451.04 rows=1,204,209 width=16) (actual time=1,832.318..1,832.318 rows=1,203,923 loops=1)

  • Buckets: 131,072 Batches: 32 Memory Usage: 2,787kB
84. 1,025.606 1,625.563 ↑ 1.0 1,203,923 1

Bitmap Heap Scan on roadworkrequesthistory rwh19 (cost=22,561.05..239,451.04 rows=1,204,209 width=16) (actual time=608.008..1,625.563 rows=1,203,923 loops=1)

  • Recheck Cond: (roadworkstatustypeid = 19)
  • Rows Removed by Index Recheck: 4,080,477
  • Heap Blocks: exact=59,167 lossy=33,031
85. 599.957 599.957 ↑ 1.0 1,203,923 1

Bitmap Index Scan on ix_roadworkrequesttypeid (cost=0.00..22,260.00 rows=1,204,209 width=0) (actual time=599.957..599.957 rows=1,203,923 loops=1)

  • Index Cond: (roadworkstatustypeid = 19)
86. 241.679 3,660.913 ↑ 1.0 306,127 1

Hash (cost=78,729.94..78,729.94 rows=310,075 width=343) (actual time=3,660.913..3,660.913 rows=306,127 loops=1)

  • Buckets: 16,384 Batches: 32 Memory Usage: 2,292kB
87. 69.504 3,419.234 ↑ 1.0 306,127 1

Hash Join (cost=7,104.88..78,729.94 rows=310,075 width=343) (actual time=130.031..3,419.234 rows=306,127 loops=1)

  • Hash Cond: (organisation_psm.parentid = parentorg.id)
88. 64.365 3,324.517 ↑ 1.0 306,127 1

Hash Join (cost=6,650.74..77,461.36 rows=310,075 width=318) (actual time=104.747..3,324.517 rows=306,127 loops=1)

  • Hash Cond: (roadworkrequest.roadworkstatustypeid = roadworkstatustype.id)
89. 63.599 3,260.128 ↑ 1.0 306,127 1

Hash Join (cost=6,649.22..76,495.91 rows=310,075 width=309) (actual time=104.712..3,260.128 rows=306,127 loops=1)

  • Hash Cond: (roadworkrequest.worktypeid = worktype.id)
90. 83.877 3,196.517 ↑ 1.0 306,127 1

Hash Join (cost=6,648.13..74,821.27 rows=310,075 width=287) (actual time=104.694..3,196.517 rows=306,127 loops=1)

  • Hash Cond: (roadworkrequest.roadworkrequestownerorganisationid = organisation_owner.id)
91. 166.684 3,110.983 ↑ 1.0 306,127 1

Hash Join (cost=6,193.99..73,552.69 rows=310,075 width=262) (actual time=103.010..3,110.983 rows=306,127 loops=1)

  • Hash Cond: (roadworkrequest.roadworkrequestpsmorganisationid = organisation_psm.id)
92. 2,863.667 2,942.250 ↑ 1.0 306,127 1

Bitmap Heap Scan on roadworkrequest (cost=5,739.85..72,284.11 rows=310,075 width=237) (actual time=100.932..2,942.250 rows=306,127 loops=1)

  • Recheck Cond: (roadworkrequestpsmorganisationid = ANY ('{1792,1793,1655,1656,250,249,251,243,244,248,247,246,245,1034,1035,1036,1033,254,231,240,239,241,242}'::integer[]))
  • Filter: (roadworkstatustypeid <> ALL ('{2,3,11,14}'::integer[]))
  • Rows Removed by Filter: 18,598
  • Heap Blocks: exact=51,974
93. 78.583 78.583 ↑ 1.0 324,725 1

Bitmap Index Scan on ix_roadworkrequest_roadworkrequestpsmorganisationid (cost=0.00..5,662.33 rows=325,926 width=0) (actual time=78.583..78.583 rows=324,725 loops=1)

  • Index Cond: (roadworkrequestpsmorganisationid = ANY ('{1792,1793,1655,1656,250,249,251,243,244,248,247,246,245,1034,1035,1036,1033,254,231,240,239,241,242}'::integer[]))
94. 0.901 2.049 ↑ 1.0 7,073 1

Hash (cost=365.73..365.73 rows=7,073 width=37) (actual time=2.049..2.049 rows=7,073 loops=1)

  • Buckets: 8,192 Batches: 1 Memory Usage: 558kB
95. 1.148 1.148 ↑ 1.0 7,073 1

Seq Scan on organisation organisation_psm (cost=0.00..365.73 rows=7,073 width=37) (actual time=0.008..1.148 rows=7,073 loops=1)

96. 0.901 1.657 ↑ 1.0 7,073 1

Hash (cost=365.73..365.73 rows=7,073 width=33) (actual time=1.657..1.657 rows=7,073 loops=1)

  • Buckets: 8,192 Batches: 1 Memory Usage: 530kB
97. 0.756 0.756 ↑ 1.0 7,073 1

Seq Scan on organisation organisation_owner (cost=0.00..365.73 rows=7,073 width=33) (actual time=0.007..0.756 rows=7,073 loops=1)

98. 0.005 0.012 ↑ 1.0 4 1

Hash (cost=1.04..1.04 rows=4 width=30) (actual time=0.012..0.012 rows=4 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
99. 0.007 0.007 ↑ 1.0 4 1

Seq Scan on worktype (cost=0.00..1.04 rows=4 width=30) (actual time=0.007..0.007 rows=4 loops=1)

100. 0.008 0.024 ↑ 1.0 23 1

Hash (cost=1.23..1.23 rows=23 width=17) (actual time=0.024..0.024 rows=23 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
101. 0.016 0.016 ↑ 1.0 23 1

Seq Scan on roadworkstatustype (cost=0.00..1.23 rows=23 width=17) (actual time=0.015..0.016 rows=23 loops=1)

102. 1.117 25.213 ↑ 1.0 7,073 1

Hash (cost=365.73..365.73 rows=7,073 width=33) (actual time=25.213..25.213 rows=7,073 loops=1)

  • Buckets: 8,192 Batches: 1 Memory Usage: 530kB
103. 24.096 24.096 ↑ 1.0 7,073 1

Seq Scan on organisation parentorg (cost=0.00..365.73 rows=7,073 width=33) (actual time=0.016..24.096 rows=7,073 loops=1)

104. 2.612 3,682.458 ↓ 3.0 23,402 1

Hash (cost=170,464.83..170,464.83 rows=7,750 width=36) (actual time=3,682.458..3,682.458 rows=23,402 loops=1)

  • Buckets: 32,768 (originally 8192) Batches: 1 (originally 1) Memory Usage: 1,239kB
105. 1.180 3,679.846 ↓ 3.0 23,402 1

Subquery Scan on mi_31_datameldingen_totaaltoeslagenkosten (cost=170,232.33..170,464.83 rows=7,750 width=36) (actual time=3,666.739..3,679.846 rows=23,402 loops=1)

106. 10.617 3,678.666 ↓ 3.0 23,402 1

GroupAggregate (cost=170,232.33..170,387.33 rows=7,750 width=36) (actual time=3,666.737..3,678.666 rows=23,402 loops=1)

  • Group Key: roadworkrequest_4.id
107. 7.068 3,668.049 ↓ 3.5 26,891 1

Sort (cost=170,232.33..170,251.71 rows=7,750 width=9) (actual time=3,666.718..3,668.049 rows=26,891 loops=1)

  • Sort Key: roadworkrequest_4.id
  • Sort Method: quicksort Memory: 2,029kB
108. 74.408 3,660.981 ↓ 3.5 26,891 1

Hash Join (cost=93,304.53..169,731.68 rows=7,750 width=9) (actual time=798.752..3,660.981 rows=26,891 loops=1)

  • Hash Cond: (roadworkrequest_4.id = roadworkrequestpenalty.roadworkrequestid)
109. 14.210 3,328.510 ↑ 1.6 95,942 1

Hash Join (cost=79,559.26..153,747.42 rows=158,266 width=4) (actual time=522.443..3,328.510 rows=95,942 loops=1)

  • Hash Cond: (organisation_psm_1.parentid = parentorg_2.id)
110. 14.292 3,313.013 ↑ 1.6 95,942 1

Hash Join (cost=79,105.12..152,877.58 rows=158,266 width=8) (actual time=521.146..3,313.013 rows=95,942 loops=1)

  • Hash Cond: (roadworkrequest_4.worktypeid = worktype_1.id)
111. 17.112 3,298.707 ↑ 1.6 96,266 1

Hash Join (cost=79,104.03..152,022.29 rows=158,266 width=12) (actual time=521.119..3,298.707 rows=96,266 loops=1)

  • Hash Cond: (roadworkrequest_4.roadworkrequestpsmorganisationid = organisation_psm_1.id)
112. 435.480 3,279.844 ↑ 1.6 96,266 1

Hash Join (cost=78,649.88..151,152.45 rows=158,266 width=12) (actual time=519.363..3,279.844 rows=96,266 loops=1)

  • Hash Cond: (roadworkrequestdates_1.id = roadworkrequest_4.actualroadworkrequestdatesid)
113. 2,329.371 2,329.371 ↓ 1.0 1,475,837 1

Index Scan using ix_roadworkrequestdates_startdate on roadworkrequestdates roadworkrequestdates_1 (cost=0.43..52,383.18 rows=1,465,375 width=4) (actual time=0.044..2,329.371 rows=1,475,837 loops=1)

  • Index Cond: (startdate >= '2017-01-01 00:00:00'::timestamp without time zone)
114. 64.247 514.993 ↑ 1.0 324,725 1

Hash (cost=72,983.38..72,983.38 rows=325,926 width=16) (actual time=514.993..514.993 rows=324,725 loops=1)

  • Buckets: 131,072 Batches: 8 Memory Usage: 2,926kB
115. 41.153 450.746 ↑ 1.0 324,725 1

Hash Join (cost=6,199.47..72,983.38 rows=325,926 width=16) (actual time=33.506..450.746 rows=324,725 loops=1)

  • Hash Cond: (roadworkrequest_4.roadworkstatustypeid = roadworkstatustype_2.id)
116. 73.133 409.577 ↑ 1.0 324,725 1

Hash Join (cost=6,197.95..71,968.65 rows=325,926 width=20) (actual time=33.483..409.577 rows=324,725 loops=1)

  • Hash Cond: (roadworkrequest_4.roadworkrequestownerorganisationid = organisation_owner_1.id)
117. 309.186 335.361 ↑ 1.0 324,725 1

Bitmap Heap Scan on roadworkrequest roadworkrequest_4 (cost=5,743.81..70,658.44 rows=325,926 width=32) (actual time=32.391..335.361 rows=324,725 loops=1)

  • Recheck Cond: (roadworkrequestpsmorganisationid = ANY ('{1792,1793,1655,1656,250,249,251,243,244,248,247,246,245,1034,1035,1036,1033,254,231,240,239,241,242}'::integer[]))
  • Heap Blocks: exact=51,974
118. 26.175 26.175 ↑ 1.0 324,725 1

Bitmap Index Scan on ix_roadworkrequest_roadworkrequestpsmorganisationid (cost=0.00..5,662.33 rows=325,926 width=0) (actual time=26.175..26.175 rows=324,725 loops=1)

  • Index Cond: (roadworkrequestpsmorganisationid = ANY ('{1792,1793,1655,1656,250,249,251,243,244,248,247,246,245,1034,1035,1036,1033,254,231,240,239,241,242}'::integer[]))
119. 0.547 1.083 ↑ 1.0 7,073 1

Hash (cost=365.73..365.73 rows=7,073 width=4) (actual time=1.083..1.083 rows=7,073 loops=1)

  • Buckets: 8,192 Batches: 1 Memory Usage: 313kB
120. 0.536 0.536 ↑ 1.0 7,073 1

Seq Scan on organisation organisation_owner_1 (cost=0.00..365.73 rows=7,073 width=4) (actual time=0.007..0.536 rows=7,073 loops=1)

121. 0.004 0.016 ↑ 1.0 23 1

Hash (cost=1.23..1.23 rows=23 width=4) (actual time=0.016..0.016 rows=23 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
122. 0.012 0.012 ↑ 1.0 23 1

Seq Scan on roadworkstatustype roadworkstatustype_2 (cost=0.00..1.23 rows=23 width=4) (actual time=0.011..0.012 rows=23 loops=1)

123. 0.608 1.751 ↑ 1.0 7,073 1

Hash (cost=365.73..365.73 rows=7,073 width=8) (actual time=1.751..1.751 rows=7,073 loops=1)

  • Buckets: 8,192 Batches: 1 Memory Usage: 341kB
124. 1.143 1.143 ↑ 1.0 7,073 1

Seq Scan on organisation organisation_psm_1 (cost=0.00..365.73 rows=7,073 width=8) (actual time=0.007..1.143 rows=7,073 loops=1)

125. 0.001 0.014 ↑ 1.0 4 1

Hash (cost=1.04..1.04 rows=4 width=4) (actual time=0.014..0.014 rows=4 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
126. 0.013 0.013 ↑ 1.0 4 1

Seq Scan on worktype worktype_1 (cost=0.00..1.04 rows=4 width=4) (actual time=0.012..0.013 rows=4 loops=1)

127. 0.555 1.287 ↑ 1.0 7,073 1

Hash (cost=365.73..365.73 rows=7,073 width=4) (actual time=1.287..1.287 rows=7,073 loops=1)

  • Buckets: 8,192 Batches: 1 Memory Usage: 313kB
128. 0.732 0.732 ↑ 1.0 7,073 1

Seq Scan on organisation parentorg_2 (cost=0.00..365.73 rows=7,073 width=4) (actual time=0.012..0.732 rows=7,073 loops=1)

129. 56.579 258.063 ↓ 4.4 298,856 1

Hash (cost=12,572.69..12,572.69 rows=67,406 width=9) (actual time=258.063..258.063 rows=298,856 loops=1)

  • Buckets: 131,072 (originally 131072) Batches: 8 (originally 2) Memory Usage: 3,073kB
130. 72.225 201.484 ↓ 4.4 298,856 1

Hash Join (cost=395.19..12,572.69 rows=67,406 width=9) (actual time=14.439..201.484 rows=298,856 loops=1)

  • Hash Cond: (roadworkrequestpenalty.organisationid = orgfromroadworkhistory.id)
131. 126.896 126.896 ↑ 1.0 506,115 1

Seq Scan on roadworkrequestpenalty (cost=0.00..10,848.15 rows=506,115 width=13) (actual time=12.063..126.896 rows=506,115 loops=1)

132. 0.097 2.363 ↑ 1.0 942 1

Hash (cost=383.41..383.41 rows=942 width=4) (actual time=2.363..2.363 rows=942 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 42kB
133. 2.266 2.266 ↑ 1.0 942 1

Seq Scan on organisation orgfromroadworkhistory (cost=0.00..383.41 rows=942 width=4) (actual time=0.036..2.266 rows=942 loops=1)

  • Filter: (organisationtypeid = 4)
  • Rows Removed by Filter: 6,131
134. 254.894 6,707.325 ↓ 1.0 682,163 1

Hash (cost=24,284.13..24,284.13 rows=657,967 width=28) (actual time=6,707.325..6,707.325 rows=682,163 loops=1)

  • Buckets: 65,536 Batches: 16 Memory Usage: 2,957kB
135. 6,452.431 6,452.431 ↓ 1.0 682,163 1

Index Scan using ix_roadworkrequestdates_startdate on roadworkrequestdates roadworkrequestdates_planned (cost=0.43..24,284.13 rows=657,967 width=28) (actual time=25.809..6,452.431 rows=682,163 loops=1)

  • Index Cond: (startdate >= '2019-01-01 00:00:00'::timestamp without time zone)
136. 3.036 3,339.050 ↓ 1.7 28,533 1

Hash (cost=194,457.58..194,457.58 rows=16,703 width=36) (actual time=3,339.050..3,339.050 rows=28,533 loops=1)

  • Buckets: 32,768 Batches: 1 Memory Usage: 1,483kB
137. 1.402 3,336.014 ↓ 1.7 28,533 1

Subquery Scan on mi_31_datamaterialen (cost=193,956.49..194,457.58 rows=16,703 width=36) (actual time=3,317.146..3,336.014 rows=28,533 loops=1)

138. 15.386 3,334.612 ↓ 1.7 28,533 1

GroupAggregate (cost=193,956.49..194,290.55 rows=16,703 width=36) (actual time=3,317.145..3,334.612 rows=28,533 loops=1)

  • Group Key: roadworkrequest_5.id
139. 13.363 3,319.226 ↓ 2.8 47,327 1

Sort (cost=193,956.49..193,998.25 rows=16,703 width=10) (actual time=3,317.137..3,319.226 rows=47,327 loops=1)

  • Sort Key: roadworkrequest_5.id
  • Sort Method: quicksort Memory: 3,584kB
140. 186.949 3,305.863 ↓ 2.8 47,327 1

Hash Join (cost=112,054.82..192,784.96 rows=16,703 width=10) (actual time=1,753.692..3,305.863 rows=47,327 loops=1)

  • Hash Cond: (roadworkrequestmaterial.roadworkrequestid = roadworkrequest_5.id)
141. 508.594 2,289.335 ↓ 4.1 1,403,168 1

Hash Join (cost=395.19..79,674.36 rows=342,383 width=10) (actual time=3.173..2,289.335 rows=1,403,168 loops=1)

  • Hash Cond: (roadworkrequestmaterial.organisationid = orgfromroadworkhistory_1.id)
142. 1,777.609 1,777.609 ↑ 1.0 2,570,598 1

Seq Scan on roadworkrequestmaterial (cost=0.00..72,526.83 rows=2,570,783 width=14) (actual time=0.031..1,777.609 rows=2,570,598 loops=1)

143. 0.141 3.132 ↑ 1.0 942 1

Hash (cost=383.41..383.41 rows=942 width=4) (actual time=3.132..3.132 rows=942 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 42kB
144. 2.991 2.991 ↑ 1.0 942 1

Seq Scan on organisation orgfromroadworkhistory_1 (cost=0.00..383.41 rows=942 width=4) (actual time=0.023..2.991 rows=942 loops=1)

  • Filter: (organisationtypeid = 4)
  • Rows Removed by Filter: 6,131
145. 6.300 829.579 ↑ 1.8 36,442 1

Hash (cost=110,820.26..110,820.26 rows=67,150 width=4) (actual time=829.579..829.579 rows=36,442 loops=1)

  • Buckets: 131,072 Batches: 1 Memory Usage: 2,306kB
146. 174.829 823.279 ↑ 1.8 36,442 1

Hash Join (cost=77,745.15..110,820.26 rows=67,150 width=4) (actual time=444.421..823.279 rows=36,442 loops=1)

  • Hash Cond: (roadworkrequestdates_2.id = roadworkrequest_5.actualroadworkrequestdatesid)
147. 208.734 208.734 ↓ 1.0 682,163 1

Index Scan using ix_roadworkrequestdates_startdate on roadworkrequestdates roadworkrequestdates_2 (cost=0.43..23,590.66 rows=657,967 width=4) (actual time=0.057..208.734 rows=682,163 loops=1)

  • Index Cond: (startdate >= '2019-01-01 00:00:00'::timestamp without time zone)
148. 52.837 439.716 ↑ 1.0 304,913 1

Hash (cost=72,690.99..72,690.99 rows=307,978 width=8) (actual time=439.716..439.716 rows=304,913 loops=1)

  • Buckets: 131,072 Batches: 8 Memory Usage: 2,514kB
149. 361.573 386.879 ↑ 1.0 304,913 1

Bitmap Heap Scan on roadworkrequest roadworkrequest_5 (cost=5,739.32..72,690.99 rows=307,978 width=8) (actual time=31.904..386.879 rows=304,913 loops=1)

  • Recheck Cond: (roadworkrequestpsmorganisationid = ANY ('{1792,1793,1655,1656,250,249,251,243,244,248,247,246,245,1034,1035,1036,1033,254,231,240,239,241,242}'::integer[]))
  • Filter: (roadworkstatustypeid <> ALL ('{2,3,9,11,14}'::integer[]))
  • Rows Removed by Filter: 19,812
  • Heap Blocks: exact=51,974
150. 25.306 25.306 ↑ 1.0 324,725 1

Bitmap Index Scan on ix_roadworkrequest_roadworkrequestpsmorganisationid (cost=0.00..5,662.33 rows=325,926 width=0) (actual time=25.306..25.306 rows=324,725 loops=1)

  • Index Cond: (roadworkrequestpsmorganisationid = ANY ('{1792,1793,1655,1656,250,249,251,243,244,248,247,246,245,1034,1035,1036,1033,254,231,240,239,241,242}'::integer[]))
151. 14.094 2,975.604 ↓ 2.6 88,668 1

Hash (cost=209,934.52..209,934.52 rows=34,539 width=36) (actual time=2,975.604..2,975.604 rows=88,668 loops=1)

  • Buckets: 131,072 (originally 65536) Batches: 2 (originally 1) Memory Usage: 3,073kB
152. 4.355 2,961.510 ↓ 2.6 88,668 1

Subquery Scan on mi_31_datameldingen_totaaladditionelekosten (cost=208,898.35..209,934.52 rows=34,539 width=36) (actual time=2,914.220..2,961.510 rows=88,668 loops=1)

153. 36.910 2,957.155 ↓ 2.6 88,668 1

GroupAggregate (cost=208,898.35..209,589.13 rows=34,539 width=36) (actual time=2,914.219..2,957.155 rows=88,668 loops=1)

  • Group Key: roadworkrequest_6.id
154. 29.835 2,920.245 ↓ 2.6 88,676 1

Sort (cost=208,898.35..208,984.70 rows=34,539 width=7) (actual time=2,914.209..2,920.245 rows=88,676 loops=1)

  • Sort Key: roadworkrequest_6.id
  • Sort Method: external merge Disk: 1,488kB
155. 174.165 2,890.410 ↓ 2.6 88,676 1

Hash Join (cost=128,755.76..206,294.81 rows=34,539 width=7) (actual time=1,865.451..2,890.410 rows=88,676 loops=1)

  • Hash Cond: (roadworkrequest_6.id = roadworkrequestadditional.roadworkrequestid)
156. 12.843 1,393.493 ↑ 1.6 95,942 1

Hash Join (cost=79,559.26..153,747.42 rows=158,266 width=4) (actual time=526.047..1,393.493 rows=95,942 loops=1)

  • Hash Cond: (organisation_psm_2.parentid = parentorg_3.id)
157. 12.907 1,379.303 ↑ 1.6 95,942 1

Hash Join (cost=79,105.12..152,877.58 rows=158,266 width=8) (actual time=524.682..1,379.303 rows=95,942 loops=1)

  • Hash Cond: (roadworkrequest_6.worktypeid = worktype_2.id)
158. 15.230 1,366.372 ↑ 1.6 96,266 1

Hash Join (cost=79,104.03..152,022.29 rows=158,266 width=12) (actual time=524.649..1,366.372 rows=96,266 loops=1)

  • Hash Cond: (roadworkrequest_6.roadworkrequestpsmorganisationid = organisation_psm_2.id)
159. 365.821 1,349.362 ↑ 1.6 96,266 1

Hash Join (cost=78,649.88..151,152.45 rows=158,266 width=12) (actual time=522.842..1,349.362 rows=96,266 loops=1)

  • Hash Cond: (roadworkrequestdates_3.id = roadworkrequest_6.actualroadworkrequestdatesid)
160. 465.216 465.216 ↓ 1.0 1,475,837 1

Index Scan using ix_roadworkrequestdates_startdate on roadworkrequestdates roadworkrequestdates_3 (cost=0.43..52,383.18 rows=1,465,375 width=4) (actual time=0.035..465.216 rows=1,475,837 loops=1)

  • Index Cond: (startdate >= '2017-01-01 00:00:00'::timestamp without time zone)
161. 63.672 518.325 ↑ 1.0 324,725 1

Hash (cost=72,983.38..72,983.38 rows=325,926 width=16) (actual time=518.325..518.325 rows=324,725 loops=1)

  • Buckets: 131,072 Batches: 8 Memory Usage: 2,926kB
162. 40.494 454.653 ↑ 1.0 324,725 1

Hash Join (cost=6,199.47..72,983.38 rows=325,926 width=16) (actual time=33.155..454.653 rows=324,725 loops=1)

  • Hash Cond: (roadworkrequest_6.roadworkstatustypeid = roadworkstatustype_3.id)
163. 73.538 414.144 ↑ 1.0 324,725 1

Hash Join (cost=6,197.95..71,968.65 rows=325,926 width=20) (actual time=33.133..414.144 rows=324,725 loops=1)

  • Hash Cond: (roadworkrequest_6.roadworkrequestownerorganisationid = organisation_owner_2.id)
164. 313.831 339.422 ↑ 1.0 324,725 1

Bitmap Heap Scan on roadworkrequest roadworkrequest_6 (cost=5,743.81..70,658.44 rows=325,926 width=32) (actual time=31.915..339.422 rows=324,725 loops=1)

  • Recheck Cond: (roadworkrequestpsmorganisationid = ANY ('{1792,1793,1655,1656,250,249,251,243,244,248,247,246,245,1034,1035,1036,1033,254,231,240,239,241,242}'::integer[]))
  • Heap Blocks: exact=51,974
165. 25.591 25.591 ↑ 1.0 324,725 1

Bitmap Index Scan on ix_roadworkrequest_roadworkrequestpsmorganisationid (cost=0.00..5,662.33 rows=325,926 width=0) (actual time=25.591..25.591 rows=324,725 loops=1)

  • Index Cond: (roadworkrequestpsmorganisationid = ANY ('{1792,1793,1655,1656,250,249,251,243,244,248,247,246,245,1034,1035,1036,1033,254,231,240,239,241,242}'::integer[]))
166. 0.653 1.184 ↑ 1.0 7,073 1

Hash (cost=365.73..365.73 rows=7,073 width=4) (actual time=1.184..1.184 rows=7,073 loops=1)

  • Buckets: 8,192 Batches: 1 Memory Usage: 313kB
167. 0.531 0.531 ↑ 1.0 7,073 1

Seq Scan on organisation organisation_owner_2 (cost=0.00..365.73 rows=7,073 width=4) (actual time=0.007..0.531 rows=7,073 loops=1)

168. 0.004 0.015 ↑ 1.0 23 1

Hash (cost=1.23..1.23 rows=23 width=4) (actual time=0.015..0.015 rows=23 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
169. 0.011 0.011 ↑ 1.0 23 1

Seq Scan on roadworkstatustype roadworkstatustype_3 (cost=0.00..1.23 rows=23 width=4) (actual time=0.010..0.011 rows=23 loops=1)

170. 0.698 1.780 ↑ 1.0 7,073 1

Hash (cost=365.73..365.73 rows=7,073 width=8) (actual time=1.780..1.780 rows=7,073 loops=1)

  • Buckets: 8,192 Batches: 1 Memory Usage: 341kB
171. 1.082 1.082 ↑ 1.0 7,073 1

Seq Scan on organisation organisation_psm_2 (cost=0.00..365.73 rows=7,073 width=8) (actual time=0.008..1.082 rows=7,073 loops=1)

172. 0.007 0.024 ↑ 1.0 4 1

Hash (cost=1.04..1.04 rows=4 width=4) (actual time=0.024..0.024 rows=4 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
173. 0.017 0.017 ↑ 1.0 4 1

Seq Scan on worktype worktype_2 (cost=0.00..1.04 rows=4 width=4) (actual time=0.016..0.017 rows=4 loops=1)

174. 0.622 1.347 ↑ 1.0 7,073 1

Hash (cost=365.73..365.73 rows=7,073 width=4) (actual time=1.347..1.347 rows=7,073 loops=1)

  • Buckets: 8,192 Batches: 1 Memory Usage: 313kB
175. 0.725 0.725 ↑ 1.0 7,073 1

Seq Scan on organisation parentorg_3 (cost=0.00..365.73 rows=7,073 width=4) (actual time=0.017..0.725 rows=7,073 loops=1)

176. 191.282 1,322.752 ↓ 4.0 1,195,343 1

Hash (cost=44,267.69..44,267.69 rows=300,385 width=7) (actual time=1,322.752..1,322.752 rows=1,195,343 loops=1)

  • Buckets: 131,072 (originally 131072) Batches: 16 (originally 4) Memory Usage: 3,950kB
177. 296.100 1,131.470 ↓ 4.0 1,195,343 1

Hash Join (cost=395.19..44,267.69 rows=300,385 width=7) (actual time=10.615..1,131.470 rows=1,195,343 loops=1)

  • Hash Cond: (roadworkrequestadditional.organisationid = orgfromroadworkhistory_2.id)
178. 833.039 833.039 ↑ 1.0 2,255,442 1

Seq Scan on roadworkrequestadditional (cost=0.00..37,948.42 rows=2,255,442 width=11) (actual time=8.267..833.039 rows=2,255,442 loops=1)

179. 0.101 2.331 ↑ 1.0 942 1

Hash (cost=383.41..383.41 rows=942 width=4) (actual time=2.331..2.331 rows=942 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 42kB
180. 2.230 2.230 ↑ 1.0 942 1

Seq Scan on organisation orgfromroadworkhistory_2 (cost=0.00..383.41 rows=942 width=4) (actual time=0.035..2.230 rows=942 loops=1)

  • Filter: (organisationtypeid = 4)
  • Rows Removed by Filter: 6,131
181. 9.694 2,711.746 ↓ 1.9 71,402 1

Hash (cost=247,792.44..247,792.44 rows=37,200 width=36) (actual time=2,711.746..2,711.746 rows=71,402 loops=1)

  • Buckets: 131,072 (originally 65536) Batches: 1 (originally 1) Memory Usage: 4,084kB
182. 3.576 2,702.052 ↓ 1.9 71,402 1

Subquery Scan on mi_31_datameldingen_helper (cost=246,304.44..247,792.44 rows=37,200 width=36) (actual time=2,629.158..2,702.052 rows=71,402 loops=1)

183. 60.597 2,698.476 ↓ 1.9 71,402 1

GroupAggregate (cost=246,304.44..247,420.44 rows=37,200 width=36) (actual time=2,629.157..2,698.476 rows=71,402 loops=1)

  • Group Key: roadworkrequest_7.id
184. 46.790 2,637.879 ↓ 3.2 118,301 1

Sort (cost=246,304.44..246,397.44 rows=37,200 width=17) (actual time=2,629.139..2,637.879 rows=118,301 loops=1)

  • Sort Key: roadworkrequest_7.id
  • Sort Method: external merge Disk: 3,264kB
185. 355.823 2,591.089 ↓ 3.2 118,301 1

Hash Join (cost=157,946.29..243,480.40 rows=37,200 width=17) (actual time=1,422.092..2,591.089 rows=118,301 loops=1)

  • Hash Cond: (roadworkrequestmaterial_1.roadworkrequestid = roadworkrequest_7.id)
186. 479.433 824.626 ↓ 4.1 1,403,168 1

Hash Join (cost=395.19..79,674.36 rows=342,383 width=17) (actual time=2.402..824.626 rows=1,403,168 loops=1)

  • Hash Cond: (roadworkrequestmaterial_1.organisationid = orgfromroadworkhistory_3.id)
187. 342.850 342.850 ↑ 1.0 2,570,598 1

Seq Scan on roadworkrequestmaterial roadworkrequestmaterial_1 (cost=0.00..72,526.83 rows=2,570,783 width=21) (actual time=0.038..342.850 rows=2,570,598 loops=1)

188. 0.088 2.343 ↑ 1.0 942 1

Hash (cost=383.41..383.41 rows=942 width=4) (actual time=2.343..2.343 rows=942 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 42kB
189. 2.255 2.255 ↑ 1.0 942 1

Seq Scan on organisation orgfromroadworkhistory_3 (cost=0.00..383.41 rows=942 width=4) (actual time=0.018..2.255 rows=942 loops=1)

  • Filter: (organisationtypeid = 4)
  • Rows Removed by Filter: 6,131
190. 15.472 1,410.640 ↑ 1.7 89,953 1

Hash (cost=155,096.72..155,096.72 rows=149,551 width=4) (actual time=1,410.640..1,410.640 rows=89,953 loops=1)

  • Buckets: 131,072 Batches: 2 Memory Usage: 2,598kB
191. 11.707 1,395.168 ↑ 1.7 89,953 1

Hash Join (cost=81,176.52..155,096.72 rows=149,551 width=4) (actual time=538.838..1,395.168 rows=89,953 loops=1)

  • Hash Cond: (organisation_psm_3.parentid = parentorg_4.id)
192. 11.630 1,382.080 ↑ 1.7 89,953 1

Hash Join (cost=80,722.38..154,249.77 rows=149,551 width=8) (actual time=537.449..1,382.080 rows=89,953 loops=1)

  • Hash Cond: (roadworkrequest_7.worktypeid = worktype_3.id)
193. 14.622 1,370.424 ↑ 1.7 89,953 1

Hash Join (cost=80,721.29..153,441.51 rows=149,551 width=12) (actual time=537.414..1,370.424 rows=89,953 loops=1)

  • Hash Cond: (roadworkrequest_7.roadworkrequestpsmorganisationid = organisation_psm_3.id)
194. 361.258 1,354.024 ↑ 1.7 89,953 1

Hash Join (cost=80,267.15..152,594.56 rows=149,551 width=12) (actual time=535.630..1,354.024 rows=89,953 loops=1)

  • Hash Cond: (roadworkrequestdates_4.id = roadworkrequest_7.actualroadworkrequestdatesid)
195. 461.622 461.622 ↓ 1.0 1,475,837 1

Index Scan using ix_roadworkrequestdates_startdate on roadworkrequestdates roadworkrequestdates_4 (cost=0.43..52,383.18 rows=1,465,375 width=4) (actual time=0.041..461.622 rows=1,475,837 loops=1)

  • Index Cond: (startdate >= '2017-01-01 00:00:00'::timestamp without time zone)
196. 57.706 531.144 ↑ 1.0 304,913 1

Hash (cost=74,912.99..74,912.99 rows=307,978 width=16) (actual time=531.144..531.144 rows=304,913 loops=1)

  • Buckets: 131,072 Batches: 8 Memory Usage: 2,812kB
197. 38.225 473.438 ↑ 1.0 304,913 1

Hash Join (cost=6,194.98..74,912.99 rows=307,978 width=16) (actual time=32.603..473.438 rows=304,913 loops=1)

  • Hash Cond: (roadworkrequest_7.roadworkstatustypeid = roadworkstatustype_4.id)
198. 61.057 435.193 ↑ 1.0 304,913 1

Hash Join (cost=6,193.47..73,954.07 rows=307,978 width=20) (actual time=32.576..435.193 rows=304,913 loops=1)

  • Hash Cond: (roadworkrequest_7.roadworkrequestownerorganisationid = organisation_owner_3.id)
199. 348.380 372.771 ↑ 1.0 304,913 1

Bitmap Heap Scan on roadworkrequest roadworkrequest_7 (cost=5,739.32..72,690.99 rows=307,978 width=32) (actual time=31.201..372.771 rows=304,913 loops=1)

  • Recheck Cond: (roadworkrequestpsmorganisationid = ANY ('{1792,1793,1655,1656,250,249,251,243,244,248,247,246,245,1034,1035,1036,1033,254,231,240,239,241,242}'::integer[]))
  • Filter: (roadworkstatustypeid <> ALL ('{2,3,9,11,14}'::integer[]))
  • Rows Removed by Filter: 19,812
  • Heap Blocks: exact=51,974
200. 24.391 24.391 ↑ 1.0 324,725 1

Bitmap Index Scan on ix_roadworkrequest_roadworkrequestpsmorganisationid (cost=0.00..5,662.33 rows=325,926 width=0) (actual time=24.391..24.391 rows=324,725 loops=1)

  • Index Cond: (roadworkrequestpsmorganisationid = ANY ('{1792,1793,1655,1656,250,249,251,243,244,248,247,246,245,1034,1035,1036,1033,254,231,240,239,241,242}'::integer[]))
201. 0.629 1.365 ↑ 1.0 7,073 1

Hash (cost=365.73..365.73 rows=7,073 width=4) (actual time=1.365..1.365 rows=7,073 loops=1)

  • Buckets: 8,192 Batches: 1 Memory Usage: 313kB
202. 0.736 0.736 ↑ 1.0 7,073 1

Seq Scan on organisation organisation_owner_3 (cost=0.00..365.73 rows=7,073 width=4) (actual time=0.008..0.736 rows=7,073 loops=1)

203. 0.005 0.020 ↑ 1.0 23 1

Hash (cost=1.23..1.23 rows=23 width=4) (actual time=0.020..0.020 rows=23 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
204. 0.015 0.015 ↑ 1.0 23 1

Seq Scan on roadworkstatustype roadworkstatustype_4 (cost=0.00..1.23 rows=23 width=4) (actual time=0.013..0.015 rows=23 loops=1)

205. 0.622 1.778 ↑ 1.0 7,073 1

Hash (cost=365.73..365.73 rows=7,073 width=8) (actual time=1.778..1.778 rows=7,073 loops=1)

  • Buckets: 8,192 Batches: 1 Memory Usage: 341kB
206. 1.156 1.156 ↑ 1.0 7,073 1

Seq Scan on organisation organisation_psm_3 (cost=0.00..365.73 rows=7,073 width=8) (actual time=0.011..1.156 rows=7,073 loops=1)

207. 0.002 0.026 ↑ 1.0 4 1

Hash (cost=1.04..1.04 rows=4 width=4) (actual time=0.026..0.026 rows=4 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
208. 0.024 0.024 ↑ 1.0 4 1

Seq Scan on worktype worktype_3 (cost=0.00..1.04 rows=4 width=4) (actual time=0.024..0.024 rows=4 loops=1)

209. 0.573 1.381 ↑ 1.0 7,073 1

Hash (cost=365.73..365.73 rows=7,073 width=4) (actual time=1.381..1.381 rows=7,073 loops=1)

  • Buckets: 8,192 Batches: 1 Memory Usage: 313kB
210. 0.808 0.808 ↑ 1.0 7,073 1

Seq Scan on organisation parentorg_4 (cost=0.00..365.73 rows=7,073 width=4) (actual time=0.014..0.808 rows=7,073 loops=1)

211. 7.310 1,261.472 ↑ 6.1 42,681 1

Hash (cost=349,408.78..349,408.78 rows=258,366 width=4) (actual time=1,261.472..1,261.472 rows=42,681 loops=1)

  • Buckets: 131,072 Batches: 4 Memory Usage: 1,400kB
212. 4.340 1,254.162 ↑ 6.1 42,681 1

Group (cost=345,533.29..346,825.12 rows=258,366 width=4) (actual time=1,247.107..1,254.162 rows=42,681 loops=1)

  • Group Key: rw.id
213. 11.974 1,249.822 ↑ 5.4 47,757 1

Sort (cost=345,533.29..346,179.21 rows=258,366 width=4) (actual time=1,247.105..1,249.822 rows=47,757 loops=1)

  • Sort Key: rw.id
  • Sort Method: quicksort Memory: 3,604kB
214. 11.182 1,237.848 ↑ 5.4 47,757 1

Hash Join (cost=307,867.25..318,772.41 rows=258,366 width=4) (actual time=1,227.528..1,237.848 rows=47,757 loops=1)

  • Hash Cond: (org.id = rw.roadworkrequestpsmorganisationid)
215. 75.989 75.989 ↑ 26.3 38 1

Function Scan on getorgtree org (cost=0.25..10.25 rows=1,000 width=4) (actual time=75.983..75.989 rows=38 loops=1)

216. 18.273 1,150.677 ↑ 1.0 140,947 1

Hash (cost=305,492.43..305,492.43 rows=144,685 width=8) (actual time=1,150.677..1,150.677 rows=140,947 loops=1)

  • Buckets: 131,072 Batches: 2 Memory Usage: 3,475kB
217. 207.737 1,132.404 ↑ 1.0 140,947 1

Hash Join (cost=91,345.73..305,492.43 rows=144,685 width=8) (actual time=591.381..1,132.404 rows=140,947 loops=1)

  • Hash Cond: (rwh.roadworkrequestid = rw.id)
218. 345.342 378.749 ↑ 1.0 140,947 1

Bitmap Heap Scan on roadworkrequesthistory rwh (cost=2,713.74..209,971.65 rows=144,685 width=4) (actual time=41.525..378.749 rows=140,947 loops=1)

  • Recheck Cond: (roadworkstatustypeid = 17)
  • Heap Blocks: exact=60,572
219. 33.407 33.407 ↑ 1.0 140,947 1

Bitmap Index Scan on ix_roadworkrequesttypeid (cost=0.00..2,677.57 rows=144,685 width=0) (actual time=33.407..33.407 rows=140,947 loops=1)

  • Index Cond: (roadworkstatustypeid = 17)
220. 183.684 545.918 ↓ 1.0 1,376,619 1

Hash (cost=66,049.44..66,049.44 rows=1,376,444 width=8) (actual time=545.918..545.918 rows=1,376,619 loops=1)

  • Buckets: 131,072 Batches: 32 Memory Usage: 2,707kB
221. 362.234 362.234 ↓ 1.0 1,376,619 1

Seq Scan on roadworkrequest rw (cost=0.00..66,049.44 rows=1,376,444 width=8) (actual time=0.030..362.234 rows=1,376,619 loops=1)

222. 0.726 2.743 ↑ 1.0 7,073 1

Hash (cost=365.73..365.73 rows=7,073 width=33) (actual time=2.743..2.743 rows=7,073 loops=1)

  • Buckets: 8,192 Batches: 1 Memory Usage: 530kB
223. 2.017 2.017 ↑ 1.0 7,073 1

Seq Scan on organisation organisation_owner_contractor (cost=0.00..365.73 rows=7,073 width=33) (actual time=0.034..2.017 rows=7,073 loops=1)

224. 147.940 147.940 ↑ 1.0 1 36,985

Index Scan using pk_roadworkrequestdates on roadworkrequestdates roadworkrequestdates_actuel (cost=0.43..2.28 rows=1 width=28) (actual time=0.004..0.004 rows=1 loops=36,985)

  • Index Cond: (id = roadworkrequest.actualroadworkrequestdatesid)
Planning time : 565.229 ms