explain.depesz.com

PostgreSQL's explain analyze made readable

Result: uPll

Settings
# exclusive inclusive rows x rows loops node
1. 1.109 15,299.982 ↑ 118.6 6,370 1

Unique (cost=26,310,836.60..26,318,390.12 rows=755,352 width=40) (actual time=15,298.587..15,299.982 rows=6,370 loops=1)

2. 3.795 15,298.873 ↑ 118.6 6,370 1

Sort (cost=26,310,836.60..26,312,724.98 rows=755,352 width=40) (actual time=15,298.586..15,298.873 rows=6,370 loops=1)

  • Sort Key: rw.id, (st_asgeojson(st_centroid(st_union(rwgis.geom)), 15, 0)), (1)
  • Sort Method: quicksort Memory: 1088kB
3. 0.512 15,295.078 ↑ 118.6 6,370 1

Append (cost=24,133,666.06..26,216,431.60 rows=755,352 width=40) (actual time=13,370.961..15,295.078 rows=6,370 loops=1)

4. 19.956 13,588.107 ↑ 272.7 2,760 1

GroupAggregate (cost=24,133,666.06..26,093,129.72 rows=752,561 width=40) (actual time=13,370.961..13,588.107 rows=2,760 loops=1)

  • Group Key: rw.id
5. 55.213 13,568.151 ↑ 281.7 2,773 1

Merge Join (cost=24,133,666.06..24,196,533.54 rows=781,052 width=50) (actual time=13,370.866..13,568.151 rows=2,773 loops=1)

  • Merge Cond: (rw.id = rwgis.entityid)
6. 0.411 13,364.977 ↑ 272.7 2,760 1

Unique (cost=24,133,665.63..24,137,428.44 rows=752,561 width=4) (actual time=13,364.234..13,364.977 rows=2,760 loops=1)

7. 1.079 13,364.566 ↑ 271.4 2,773 1

Sort (cost=24,133,665.63..24,135,547.03 rows=752,561 width=4) (actual time=13,364.232..13,364.566 rows=2,773 loops=1)

  • Sort Key: rw.id
  • Sort Method: quicksort Memory: 226kB
8. 0.202 13,363.487 ↑ 271.4 2,773 1

Result (cost=117,582.87..24,049,920.22 rows=752,561 width=4) (actual time=6,199.866..13,363.487 rows=2,773 loops=1)

  • One-Time Filter: ((to_timestamp('20110103 12:00:00 AM'::text, 'yyyyMMdd HH24:MI:SS AM'::text))::timestamp without time zone <= (to_timestamp('20190808 11:59:59 PM'::text, 'yyyyMMdd HH24:MI:SS AM'::text))::timestamp without time zone)
9. 0.542 13,363.285 ↑ 271.4 2,773 1

Hash Left Join (cost=117,582.87..24,049,920.22 rows=752,561 width=4) (actual time=6,199.856..13,363.285 rows=2,773 loops=1)

  • Hash Cond: (rw.roadworkrequestownercontractororganisationid = c.id)
10. 0.940 13,361.681 ↑ 271.4 2,773 1

Hash Join (cost=117,338.17..24,040,175.53 rows=752,561 width=8) (actual time=6,198.762..13,361.681 rows=2,773 loops=1)

  • Hash Cond: (rw.roadworkrequestpsmorganisationid = psm.id)
11. 254.581 13,359.659 ↑ 271.4 2,773 1

Hash Join (cost=117,093.47..24,030,430.85 rows=752,561 width=12) (actual time=6,197.657..13,359.659 rows=2,773 loops=1)

  • Hash Cond: (rw.actualroadworkrequestdatesid = ad.id)
12. 10.711 7,286.208 ↑ 279.9 2,802 1

Hash Join (cost=4,655.65..23,891,638.21 rows=784,143 width=16) (actual time=77.805..7,286.208 rows=2,802 loops=1)

  • Hash Cond: ((rw.roadworkstatustypeid = rs.id) AND (rw.roadworkrequestownerorganisationid = ownr.id))
13. 43.695 7,232.750 ↑ 279.9 2,802 1

Merge Join (cost=61.79..23,820,452.99 rows=784,143 width=24) (actual time=34.707..7,232.750 rows=2,802 loops=1)

  • Merge Cond: (rw.id = gis.entityid)
14. 3,993.559 7,116.315 ↑ 279.9 2,789 1

Index Scan using pk_request on roadworkrequest rw (cost=0.42..23,780,884.45 rows=780,535 width=24) (actual time=34.124..7,116.315 rows=2,789 loops=1)

  • Filter: ((SubPlan 5) OR (alternatives: SubPlan 6 or hashed SubPlan 7))
  • Rows Removed by Filter: 1038128
15.          

SubPlan (forIndex Scan)

16. 1,040.917 3,122.751 ↓ 0.0 0 1,040,917

Hash Join (cost=4.46..49.55 rows=3 width=0) (actual time=0.003..0.003 rows=0 loops=1,040,917)

  • Hash Cond: (uo.id = c_1.organisationid)
17. 0.000 0.000 ↑ 2,550.0 1 1,039,350

Seq Scan on userorgids uo (cost=0.00..35.50 rows=2,550 width=4) (actual time=0.000..0.000 rows=1 loops=1,039,350)

18. 1,040.917 2,081.834 ↑ 1.0 1 1,040,917

Hash (cost=4.45..4.45 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=1,040,917)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
19. 1,040.917 1,040.917 ↑ 1.0 1 1,040,917

Index Only Scan using ix_combipartners_roadworkrequestid on combipartners c_1 (cost=0.43..4.45 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=1,040,917)

  • Index Cond: (roadworkrequestid = rw.id)
  • Heap Fetches: 0
20. 0.000 0.000 ↓ 0.0 0

Seq Scan on userorgids uo_1 (cost=0.00..41.88 rows=13 width=0) (never executed)

  • Filter: (id = rw.roadworkrequestownerorganisationid)
21. 0.005 0.005 ↑ 2,550.0 1 1

Seq Scan on userorgids uo_2 (cost=0.00..35.50 rows=2,550 width=4) (actual time=0.005..0.005 rows=1 loops=1)

22. 72.740 72.740 ↑ 1.0 1,045,497 1

Index Only Scan using ix_geometryroadworkid on geometryroadwork gis (cost=0.42..27,163.27 rows=1,045,523 width=4) (actual time=0.016..72.740 rows=1,045,497 loops=1)

  • Heap Fetches: 0
23. 26.765 42.747 ↑ 1.0 140,852 1

Hash (cost=1,930.08..1,930.08 rows=140,852 width=8) (actual time=42.747..42.747 rows=140,852 loops=1)

  • Buckets: 131072 Batches: 2 Memory Usage: 3773kB
24. 9.287 15.982 ↑ 1.0 140,852 1

Nested Loop (cost=0.28..1,930.08 rows=140,852 width=8) (actual time=0.037..15.982 rows=140,852 loops=1)

25. 0.571 0.571 ↑ 1.0 6,124 1

Index Only Scan using pk_organisation on organisation ownr (cost=0.28..168.14 rows=6,124 width=4) (actual time=0.016..0.571 rows=6,124 loops=1)

  • Heap Fetches: 0
26. 6.106 6.124 ↑ 1.0 23 6,124

Materialize (cost=0.00..1.35 rows=23 width=4) (actual time=0.000..0.001 rows=23 loops=6,124)

27. 0.018 0.018 ↑ 1.0 23 1

Seq Scan on roadworkstatustype rs (cost=0.00..1.23 rows=23 width=4) (actual time=0.014..0.018 rows=23 loops=1)

28. 340.517 5,818.870 ↓ 1.0 2,259,470 1

Hash (cost=75,493.05..75,493.05 rows=2,251,822 width=4) (actual time=5,818.870..5,818.870 rows=2,259,470 loops=1)

  • Buckets: 131072 Batches: 32 Memory Usage: 3506kB
29. 5,478.353 5,478.353 ↓ 1.0 2,259,470 1

Seq Scan on roadworkrequestdates ad (cost=0.00..75,493.05 rows=2,251,822 width=4) (actual time=6.559..5,478.353 rows=2,259,470 loops=1)

  • Filter: ((completeddate >= (to_timestamp('20110103 12:00:00 AM'::text, 'yyyyMMdd HH24:MI:SS AM'::text))::timestamp without time zone) AND (startdate <= (to_timestamp('20190808 11:59:59 PM'::text, 'yyyyMMdd HH24:MI:SS AM'::text))::timestamp without time zone))
  • Rows Removed by Filter: 86852
30. 0.618 1.082 ↑ 1.0 6,124 1

Hash (cost=168.14..168.14 rows=6,124 width=4) (actual time=1.082..1.082 rows=6,124 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 280kB
31. 0.464 0.464 ↑ 1.0 6,124 1

Index Only Scan using pk_organisation on organisation psm (cost=0.28..168.14 rows=6,124 width=4) (actual time=0.011..0.464 rows=6,124 loops=1)

  • Heap Fetches: 0
32. 0.625 1.062 ↑ 1.0 6,124 1

Hash (cost=168.14..168.14 rows=6,124 width=4) (actual time=1.062..1.062 rows=6,124 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 280kB
33. 0.437 0.437 ↑ 1.0 6,124 1

Index Only Scan using pk_organisation on organisation c (cost=0.28..168.14 rows=6,124 width=4) (actual time=0.014..0.437 rows=6,124 loops=1)

  • Heap Fetches: 0
34. 147.961 147.961 ↑ 1.0 1,045,497 1

Index Scan using ix_geometryroadworkid on geometryroadwork rwgis (cost=0.42..39,273.77 rows=1,045,523 width=50) (actual time=0.024..147.961 rows=1,045,497 loops=1)

35. 3.000 820.986 ↑ 1.3 89 1

GroupAggregate (cost=2,971.99..4,214.64 rows=119 width=40) (actual time=755.029..820.986 rows=89 loops=1)

  • Group Key: p.id
36. 0.249 817.986 ↑ 1.7 89 1

Nested Loop (cost=2,971.99..3,914.61 rows=149 width=165) (actual time=754.951..817.986 rows=89 loops=1)

37. 0.051 751.343 ↑ 1.3 89 1

Unique (cost=2,971.57..2,972.16 rows=119 width=4) (actual time=751.255..751.343 rows=89 loops=1)

38. 0.312 751.292 ↑ 1.3 89 1

Sort (cost=2,971.57..2,971.87 rows=119 width=4) (actual time=751.254..751.292 rows=89 loops=1)

  • Sort Key: p.id
  • Sort Method: quicksort Memory: 29kB
39. 0.110 750.980 ↑ 1.3 89 1

Result (cost=1.67..2,967.47 rows=119 width=4) (actual time=447.655..750.980 rows=89 loops=1)

  • One-Time Filter: ((to_timestamp('20110103 12:00:00 AM'::text, 'yyyyMMdd HH24:MI:SS AM'::text))::timestamp without time zone <= (to_timestamp('20190808 11:59:59 PM'::text, 'yyyyMMdd HH24:MI:SS AM'::text))::timestamp without time zone)
40. 0.287 750.870 ↑ 1.3 89 1

Nested Loop (cost=1.67..2,967.47 rows=119 width=4) (actual time=447.641..750.870 rows=89 loops=1)

41. 0.606 750.138 ↑ 1.3 89 1

Nested Loop (cost=1.37..2,929.96 rows=119 width=8) (actual time=447.631..750.138 rows=89 loops=1)

42. 0.511 747.930 ↑ 1.3 89 1

Nested Loop (cost=1.09..2,893.66 rows=119 width=12) (actual time=447.603..747.930 rows=89 loops=1)

43. 0.314 481.398 ↑ 14.0 89 1

Nested Loop (cost=0.67..2,061.75 rows=1,249 width=8) (actual time=435.572..481.398 rows=89 loops=1)

44. 431.244 431.244 ↑ 11.2 89 1

Function Scan on getpermitasownerandcoordinator gp (cost=0.25..10.25 rows=1,000 width=4) (actual time=431.127..431.244 rows=89 loops=1)

45. 49.840 49.840 ↑ 1.0 1 89

Index Only Scan using ix_geometrypermitid on geometrypermit gis_1 (cost=0.42..2.04 rows=1 width=4) (actual time=0.558..0.560 rows=1 loops=89)

  • Index Cond: (entityid = gp.id)
  • Heap Fetches: 0
46. 266.021 266.021 ↑ 1.0 1 89

Index Scan using pk_permit on permit p (cost=0.42..0.67 rows=1 width=12) (actual time=2.989..2.989 rows=1 loops=89)

  • Index Cond: (id = gis_1.entityid)
  • Filter: ((COALESCE(approvedperiodto, requestperiodto) >= (to_timestamp('20110103 12:00:00 AM'::text, 'yyyyMMdd HH24:MI:SS AM'::text))::timestamp without time zone) AND (COALESCE(approvedperiodfrom, requestperiodfrom) <= (to_timestamp('20190808 11:59:59 PM'::text, 'yyyyMMdd HH24:MI:SS AM'::text))::timestamp without time zone))
47. 1.602 1.602 ↑ 1.0 1 89

Index Only Scan using pk_organisation on organisation psmorg (cost=0.28..0.31 rows=1 width=4) (actual time=0.018..0.018 rows=1 loops=89)

  • Index Cond: (id = p.permitpublicspacemanagerid)
  • Heap Fetches: 0
48. 0.445 0.445 ↑ 1.0 1 89

Index Only Scan using pk_organisation on organisation ownerorg (cost=0.28..0.31 rows=1 width=4) (actual time=0.005..0.005 rows=1 loops=89)

  • Index Cond: (id = p.permitownerorganisationid)
  • Heap Fetches: 0
49. 66.394 66.394 ↑ 1.0 1 89

Index Scan using ix_geometrypermitid on geometrypermit pergis (cost=0.42..7.90 rows=1 width=165) (actual time=0.745..0.746 rows=1 loops=89)

  • Index Cond: (entityid = p.id)
50. 21.224 281.622 ↓ 1.0 1,866 1

HashAggregate (cost=6,382.33..10,861.55 rows=1,781 width=40) (actual time=267.842..281.622 rows=1,866 loops=1)

  • Group Key: projectpreparation.id
51. 13.725 260.398 ↑ 1.1 1,873 1

Hash Join (cost=3,926.83..6,371.71 rows=2,125 width=105) (actual time=217.372..260.398 rows=1,873 loops=1)

  • Hash Cond: (gis_2.entityid = projectpreparation.id)
52. 45.331 45.331 ↑ 1.0 60,680 1

Seq Scan on geometryprojectprep gis_2 (cost=0.00..1,678.80 rows=60,680 width=105) (actual time=5.846..45.331 rows=60,680 loops=1)

53. 0.497 201.342 ↓ 1.0 1,866 1

Hash (cost=3,904.57..3,904.57 rows=1,781 width=4) (actual time=201.342..201.342 rows=1,866 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 82kB
54. 2.245 200.845 ↓ 1.0 1,866 1

HashAggregate (cost=3,868.95..3,886.76 rows=1,781 width=4) (actual time=200.351..200.845 rows=1,866 loops=1)

  • Group Key: projectpreparation.id
55. 1.546 198.600 ↑ 1.1 2,163 1

Nested Loop (cost=0.29..3,863.24 rows=2,281 width=4) (actual time=35.650..198.600 rows=2,163 loops=1)

56. 96.290 96.290 ↓ 1.0 1,866 1

Seq Scan on projectpreparation (cost=0.00..2,513.32 rows=1,781 width=4) (actual time=5.766..96.290 rows=1,866 loops=1)

  • Filter: ((ownerorganisationid = 225) AND (enddate >= (to_timestamp('20110103 12:00:00 AM'::text, 'yyyyMMdd HH24:MI:SS AM'::text))::timestamp without time zone) AND (startdate <= (to_timestamp('20190808 11:59:59 PM'::text, 'yyyyMMdd HH24:MI:SS AM'::text))::timestamp without time zone))
  • Rows Removed by Filter: 53164
57. 100.764 100.764 ↑ 1.0 1 1,866

Index Only Scan using ix_projectprepid on projectpreppiorgresolving ppp (cost=0.29..0.75 rows=1 width=4) (actual time=0.054..0.054 rows=1 loops=1,866)

  • Index Cond: (projectpreparationid = projectpreparation.id)
  • Heap Fetches: 0
58. 11.040 126.660 ↓ 1.4 585 1

GroupAggregate (cost=43.54..92,051.84 rows=413 width=40) (actual time=43.741..126.660 rows=585 loops=1)

  • Group Key: p_1.id
59. 0.458 115.620 ↓ 1.4 585 1

Merge Join (cost=43.54..91,011.08 rows=413 width=256) (actual time=36.531..115.620 rows=585 loops=1)

  • Merge Cond: (p_1.id = projgis.entityid)
60. 0.154 104.086 ↓ 1.4 585 1

Unique (cost=43.26..90,908.96 rows=413 width=4) (actual time=36.514..104.086 rows=585 loops=1)

61. 0.489 103.932 ↓ 1.4 585 1

Merge Join (cost=43.26..90,907.93 rows=413 width=4) (actual time=36.513..103.932 rows=585 loops=1)

  • Merge Cond: (p_1.id = gis_3.entityid)
62. 0.295 86.481 ↓ 1.4 585 1

Nested Loop (cost=42.99..90,866.66 rows=413 width=4) (actual time=19.690..86.481 rows=585 loops=1)

  • Join Filter: (p_1.publicutilitytypeid = pu.id)
  • Rows Removed by Join Filter: 801
63. 0.316 86.186 ↓ 1.4 585 1

Nested Loop (cost=42.99..90,826.85 rows=413 width=8) (actual time=19.680..86.186 rows=585 loops=1)

  • Join Filter: (p_1.statustypeid = ps.id)
  • Rows Removed by Join Filter: 1039
64. 0.377 85.870 ↓ 1.4 585 1

Nested Loop (cost=42.99..90,810.33 rows=413 width=12) (actual time=19.669..85.870 rows=585 loops=1)

  • Join Filter: (p_1.typeid = pt.id)
  • Rows Removed by Join Filter: 644
65. 0.152 85.493 ↓ 1.4 585 1

Nested Loop (cost=42.99..90,793.82 rows=413 width=16) (actual time=19.658..85.493 rows=585 loops=1)

66. 0.436 84.756 ↓ 1.4 585 1

Nested Loop (cost=42.71..90,625.77 rows=413 width=20) (actual time=19.654..84.756 rows=585 loops=1)

67. 0.540 83.735 ↓ 1.4 585 1

Nested Loop (cost=42.42..90,432.73 rows=413 width=24) (actual time=19.640..83.735 rows=585 loops=1)

68. 28.363 69.975 ↓ 1.5 661 1

Index Scan using pk_project on project p_1 (cost=42.15..89,947.50 rows=454 width=24) (actual time=19.619..69.975 rows=661 loops=1)

  • Filter: ((statustypeid = ANY ('{1,2,3}'::integer[])) AND (enddate >= to_timestamp('20110103'::text, 'yyyyMMdd'::text)) AND (startdate <= (to_timestamp('20190808 12:00:00 AM'::text, 'yyyyMMdd HH24:MI:SS AM'::text))::timestamp without time zone) AND (ispublic OR ((NOT ispublic) AND ((coordinatororganisationid = 225) OR ((SubPlan 1) > 0)))) AND (((statustypeid <> 1) AND ((SubPlan 2) > 0)) OR (coordinatororganisationid = 225) OR ((statustypeid <> 1) AND (hashed SubPlan 3)) OR ((statustypeid <> 1) AND ((SubPlan 4) = 0))))
  • Rows Removed by Filter: 244
69.          

SubPlan (forIndex Scan)

70. 0.141 0.611 ↑ 1.0 1 47

Aggregate (cost=3.36..3.37 rows=1 width=8) (actual time=0.013..0.013 rows=1 loops=47)

71. 0.470 0.470 ↓ 0.0 0 47

Seq Scan on projectpartnerresolving (cost=0.00..3.36 rows=1 width=0) (actual time=0.010..0.010 rows=0 loops=47)

  • Filter: ((projectid = p_1.id) AND (partnerorganisationid = 225))
  • Rows Removed by Filter: 157
72. 0.000 31.067 ↑ 1.0 1 661

Aggregate (cost=47.88..47.89 rows=1 width=8) (actual time=0.047..0.047 rows=1 loops=661)

73. 1.250 31.067 ↓ 0.0 0 661

Hash Join (cost=44.86..47.88 rows=1 width=0) (actual time=0.047..0.047 rows=0 loops=661)

  • Hash Cond: (userorgids.id = projectpartnerresolving_1.partnerorganisationid)
74. 0.065 0.072 ↑ 200.0 1 36

HashAggregate (cost=41.88..43.88 rows=200 width=4) (actual time=0.001..0.002 rows=1 loops=36)

  • Group Key: userorgids.id
75. 0.007 0.007 ↑ 2,550.0 1 1

Seq Scan on userorgids (cost=0.00..35.50 rows=2,550 width=4) (actual time=0.007..0.007 rows=1 loops=1)

76. 3.305 29.745 ↓ 0.0 0 661

Hash (cost=2.96..2.96 rows=2 width=4) (actual time=0.045..0.045 rows=0 loops=661)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
77. 26.440 26.440 ↓ 0.0 0 661

Seq Scan on projectpartnerresolving projectpartnerresolving_1 (cost=0.00..2.96 rows=2 width=4) (actual time=0.039..0.040 rows=0 loops=661)

  • Filter: (projectid = p_1.id)
  • Rows Removed by Filter: 157
78. 0.019 0.019 ↑ 2,550.0 1 1

Seq Scan on userorgids userorgids_1 (cost=0.00..35.50 rows=2,550 width=4) (actual time=0.019..0.019 rows=1 loops=1)

79. 0.661 9.915 ↑ 1.0 1 661

Aggregate (cost=47.88..47.89 rows=1 width=8) (actual time=0.014..0.015 rows=1 loops=661)

80. 1.286 9.254 ↓ 0.0 0 661

Hash Join (cost=44.86..47.88 rows=1 width=0) (actual time=0.014..0.014 rows=0 loops=661)

  • Hash Cond: (userorgids_2.id = projectpartnerresolving_2.partnerorganisationid)
81. 0.032 0.036 ↑ 200.0 1 36

HashAggregate (cost=41.88..43.88 rows=200 width=4) (actual time=0.001..0.001 rows=1 loops=36)

  • Group Key: userorgids_2.id
82. 0.004 0.004 ↑ 2,550.0 1 1

Seq Scan on userorgids userorgids_2 (cost=0.00..35.50 rows=2,550 width=4) (actual time=0.003..0.004 rows=1 loops=1)

83. 1.322 7.932 ↓ 0.0 0 661

Hash (cost=2.96..2.96 rows=2 width=4) (actual time=0.012..0.012 rows=0 loops=661)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
84. 6.610 6.610 ↓ 0.0 0 661

Seq Scan on projectpartnerresolving projectpartnerresolving_2 (cost=0.00..2.96 rows=2 width=4) (actual time=0.009..0.010 rows=0 loops=661)

  • Filter: (projectid = p_1.id)
  • Rows Removed by Filter: 157
85. 13.220 13.220 ↑ 1.0 1 661

Index Scan using pk_projectgroup on projectgroup g (cost=0.28..1.07 rows=1 width=8) (actual time=0.020..0.020 rows=1 loops=661)

  • Index Cond: (id = p_1.groupid)
  • Filter: (psmid = ANY ('{4382,1070,4683,4398,4862,4684,4685,4686,4710,4399,1088,4512,4809,4529,4401,1072,4680,4530,4810,4513,4661,1338,4479,4811,4691,4688,4812,4597,1307,4856,4838,5463,4813,4663,1308,4406,4514,4408,4665,1095,1330,4667,4532,4712,4386,4692,1344,1312,4641,4387,4818,4819,4820,4672,4821,1315,4391,1098,1076,1077,1096,4703,1103,4644,4393,4678,4693,4679,4694,4518,4599,4704,1346,1347,4675,4674,4668,4816,4394,4824,4594,4825,4817,4851,4706,4721,4396,1318,4600,4714,4670,4671,4423,1104,4822,4724,4586,4649,4697,4884,4728,4464,4806,1322,4677,1334,4801,4424,4814,4603,1080,1106,4640,4428,4650,4465,1107,4808,4429,5505,4807,4695,4430,1108,4520,1335,5026,4771,4772,4773,4774,4775,4776,4754,4755,4756,4757,4758,4759,4760,4761,4762,4763,4764,4765,5751,4787,4766,4803,4767,4768,4770,4777,4860,4778,4780,4782,4783,4784,4785,4786,4769,1945,1944,1943,1939,1465,1462,1464,1463,4939,1001,2230,1707,2345,5968,4583,4584,4585,5969,1101,1105,1724,3770,4390,2286,2287,2288,2221,1282,2331,915,2127,2128,734,736,735,1722,229,238,252,254,5944,230,231,237,251,249,250,232,239,1655,1656,235,243,1793,244,236,248,247,246,245,234,1034,1037,1035,1036,1033,233,240,1792,2363,1445,1482,2953,2512,1607,4711,4699,3316,1073,1645,2736,2732,2737,2733,2738,2739,2740,2741,1310,1314,1319,1090,1074,2210,5258,1354,5269,309,4655,3235,4405,2213,1473,737,444,2124,2066,2132,2139,2138,3730,3731,3732,3733,3143,1093,1109,3063,1094,50,2305,2304,2307,2308,2306,4436,859,4666,1356,4357,2233,4669,2982,1867,2238,1075,3111,1483,3364,2212,2473,1706,4713,4750,3551,1506,1468,2234,1215,1217,1218,1216,1097,766,2039,4579,3517,1700,1840,5018,3519,3502,575,4534,914,942,941,943,1474,1914,1453,1454,1455,1456,2291,1078,1457,1461,4527,1317,1708,1358,1687,4705,747,2175,2176,326,1359,1460,1702,1703,4449,1815,1816,1817,1818,1701,999,4673,3148,3147,701,707,706,708,61,1186,1187,4727,4564,4740,3500,3585,3586,3587,1364,4439,2909,2939,2118,1133,3845,1394,5469,1309,1302,1316,1693,1452,4426,1458,3520,1323,1031,4932,2089,1699,76,2069,3693,1471,3146,4737,3539,5645,2135,2136,2137,2144,2133,2145,1704,1775,1720,1721,457,152,330,331,332,146,99,151,2957,324,325,1755,4659,1746,1747,1748,1752,1749,1750,1751,2080,2082,2938,2475,2086,4642,4861,2083,2081,2084,2085,1666,1340,1341,1342,1343,1345,1360,1351,2046,1339,1350,3132,3134,913,3133,3472,5757,4397,4400,4407,3427,4524,4707,3315,4708,3314,3266,3313,4637,4676,3841,4636,4402,1839,4656,3406,4598,4664,1841,1838,4546,4403,5066,4630,4515,4646,4731,4435,1964,4858,4422,1837,5133,3154,3426,4459,3264,4651,4658,4662,4657,3758,3886,4645,4395,4487,3267,2731,3408,4732,3451,5810,1661,1662,1226,631,461,612,459,1332,1227,458,4823,460,630,5497,1663,1660,5462,4902,1798,2548,5847,2020,4383,3409,3656,3663,3662,3657,3661,3665,3664,3660,3666,3659,3658,3669,3668,3667}'::integer[]))
  • Rows Removed by Filter: 0
86. 0.585 0.585 ↑ 1.0 1 585

Index Only Scan using pk_organisation on organisation co (cost=0.28..0.47 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=585)

  • Index Cond: (id = p_1.coordinatororganisationid)
  • Heap Fetches: 0
87. 0.585 0.585 ↑ 1.0 1 585

Index Only Scan using pk_organisation on organisation psm_1 (cost=0.28..0.41 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=585)

  • Index Cond: (id = g.psmid)
  • Heap Fetches: 0
88. 0.000 0.000 ↑ 1.5 2 585

Materialize (cost=0.00..1.04 rows=3 width=4) (actual time=0.000..0.000 rows=2 loops=585)

89. 0.007 0.007 ↑ 1.0 3 1

Seq Scan on projecttype pt (cost=0.00..1.03 rows=3 width=4) (actual time=0.007..0.007 rows=3 loops=1)

90. 0.000 0.000 ↑ 1.0 3 585

Materialize (cost=0.00..1.04 rows=3 width=4) (actual time=0.000..0.000 rows=3 loops=585)

91. 0.006 0.006 ↑ 1.0 3 1

Seq Scan on projectstatustype ps (cost=0.00..1.03 rows=3 width=4) (actual time=0.006..0.006 rows=3 loops=1)

92. 0.000 0.000 ↑ 3.5 2 585

Materialize (cost=0.00..1.11 rows=7 width=4) (actual time=0.000..0.000 rows=2 loops=585)

93. 0.008 0.008 ↑ 1.0 7 1

Seq Scan on projectpublicutilitytype pu (cost=0.00..1.07 rows=7 width=4) (actual time=0.006..0.008 rows=7 loops=1)

94. 16.962 16.962 ↑ 1.0 904 1

Index Only Scan using ix_geometryprojectid on geometryproject gis_3 (cost=0.28..33.85 rows=905 width=4) (actual time=16.817..16.962 rows=904 loops=1)

  • Heap Fetches: 0
95. 11.076 11.076 ↑ 1.0 904 1

Index Scan using ix_geometryprojectid on geometryproject projgis (cost=0.28..90.56 rows=905 width=256) (actual time=0.014..11.076 rows=904 loops=1)

96. 462.704 477.191 ↓ 2.2 1,070 1

GroupAggregate (cost=7,414.15..8,620.35 rows=478 width=40) (actual time=14.378..477.191 rows=1,070 loops=1)

  • Group Key: sa.id
97. 1.012 14.487 ↓ 2.8 1,497 1

Sort (cost=7,414.15..7,415.49 rows=537 width=456) (actual time=14.301..14.487 rows=1,497 loops=1)

  • Sort Key: sa.id
  • Sort Method: quicksort Memory: 1066kB
98. 1.248 13.475 ↓ 2.8 1,497 1

Hash Join (cost=6,653.66..7,389.80 rows=537 width=456) (actual time=11.566..13.475 rows=1,497 loops=1)

  • Hash Cond: (sageom.entityid = sa.id)
99. 0.675 0.675 ↑ 1.0 9,023 1

Seq Scan on geometryspecialarea sageom (cost=0.00..622.23 rows=9,023 width=456) (actual time=0.008..0.675 rows=9,023 loops=1)

100. 0.127 11.552 ↓ 2.2 1,070 1

Hash (cost=6,647.69..6,647.69 rows=478 width=4) (actual time=11.552..11.552 rows=1,070 loops=1)

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 54kB
101. 0.148 11.425 ↓ 2.2 1,070 1

Unique (cost=6,121.70..6,642.91 rows=478 width=4) (actual time=8.801..11.425 rows=1,070 loops=1)

102. 0.000 11.277 ↓ 2.8 1,497 1

Nested Loop (cost=6,121.70..6,641.56 rows=537 width=4) (actual time=8.800..11.277 rows=1,497 loops=1)

103. 0.559 9.987 ↓ 2.8 1,497 1

Merge Join (cost=6,121.42..6,395.08 rows=537 width=8) (actual time=8.793..9.987 rows=1,497 loops=1)

  • Merge Cond: (sa.id = gis_4.entityid)
104. 0.227 8.818 ↓ 2.2 1,070 1

Sort (cost=6,121.14..6,122.33 rows=478 width=8) (actual time=8.778..8.818 rows=1,070 loops=1)

  • Sort Key: sa.id
  • Sort Method: quicksort Memory: 99kB
105. 8.591 8.591 ↓ 2.2 1,070 1

Seq Scan on specialarea sa (cost=0.00..6,099.86 rows=478 width=8) (actual time=0.014..8.591 rows=1,070 loops=1)

  • Filter: ((NOT isdeleted) AND isactive AND ((todate >= to_timestamp('20190208'::text, 'yyyyMMdd'::text)) OR (todate IS NULL)) AND (fromdate <= (to_timestamp('20190808 11:59:59 PM'::text, 'yyyyMMdd HH24:MI:SS AM'::text))::timestamp without time zone) AND ((todate >= (to_timestamp('20110103 12:00:00 AM'::text, 'yyyyMMdd HH24:MI:SS AM'::text))::timestamp without time zone) OR (todate IS NULL)) AND (organisationid = ANY ('{4382,1070,4683,4398,4862,4684,4685,4686,4710,4399,1088,4512,4809,4529,4401,1072,4680,4530,4810,4513,4661,1338,4479,4811,4691,4688,4812,4597,1307,4856,4838,5463,4813,4663,1308,4406,4514,4408,4665,1095,1330,4667,4532,4712,4386,4692,1344,1312,4641,4387,4818,4819,4820,4672,4821,1315,4391,1098,1076,1077,1096,4703,1103,4644,4393,4678,4693,4679,4694,4518,4599,4704,1346,1347,4675,4674,4668,4816,4394,4824,4594,4825,4817,4851,4706,4721,4396,1318,4600,4714,4670,4671,4423,1104,4822,4724,4586,4649,4697,4884,4728,4464,4806,1322,4677,1334,4801,4424,4814,4603,1080,1106,4640,4428,4650,4465,1107,4808,4429,5505,4807,4695,4430,1108,4520,1335,5026,4771,4772,4773,4774,4775,4776,4754,4755,4756,4757,4758,4759,4760,4761,4762,4763,4764,4765,5751,4787,4766,4803,4767,4768,4770,4777,4860,4778,4780,4782,4783,4784,4785,4786,4769,1945,1944,1943,1939,1465,1462,1464,1463,4939,1001,2230,1707,2345,5968,4583,4584,4585,5969,1101,1105,1724,3770,4390,2286,2287,2288,2221,1282,2331,915,2127,2128,734,736,735,1722,229,238,252,254,5944,230,231,237,251,249,250,232,239,1655,1656,235,243,1793,244,236,248,247,246,245,234,1034,1037,1035,1036,1033,233,240,1792,2363,1445,1482,2953,2512,1607,4711,4699,3316,1073,1645,2736,2732,2737,2733,2738,2739,2740,2741,1310,1314,1319,1090,1074,2210,5258,1354,5269,309,4655,3235,4405,2213,1473,737,444,2124,2066,2132,2139,2138,3730,3731,3732,3733,3143,1093,1109,3063,1094,50,2305,2304,2307,2308,2306,4436,859,4666,1356,4357,2233,4669,2982,1867,2238,1075,3111,1483,3364,2212,2473,1706,4713,4750,3551,1506,1468,2234,1215,1217,1218,1216,1097,766,2039,4579,3517,1700,1840,5018,3519,3502,575,4534,914,942,941,943,1474,1914,1453,1454,1455,1456,2291,1078,1457,1461,4527,1317,1708,1358,1687,4705,747,2175,2176,326,1359,1460,1702,1703,4449,1815,1816,1817,1818,1701,999,4673,3148,3147,701,707,706,708,61,1186,1187,4727,4564,4740,3500,3585,3586,3587,1364,4439,2909,2939,2118,1133,3845,1394,5469,1309,1302,1316,1693,1452,4426,1458,3520,1323,1031,4932,2089,1699,76,2069,3693,1471,3146,4737,3539,5645,2135,2136,2137,2144,2133,2145,1704,1775,1720,1721,457,152,330,331,332,146,99,151,2957,324,325,1755,4659,1746,1747,1748,1752,1749,1750,1751,2080,2082,2938,2475,2086,4642,4861,2083,2081,2084,2085,1666,1340,1341,1342,1343,1345,1360,1351,2046,1339,1350,3132,3134,913,3133,3472,5757,4397,4400,4407,3427,4524,4707,3315,4708,3314,3266,3313,4637,4676,3841,4636,4402,1839,4656,3406,4598,4664,1841,1838,4546,4403,5066,4630,4515,4646,4731,4435,1964,4858,4422,1837,5133,3154,3426,4459,3264,4651,4658,4662,4657,3758,3886,4645,4395,4487,3267,2731,3408,4732,3451,5810,1661,1662,1226,631,461,612,459,1332,1227,458,4823,460,630,5497,1663,1660,5462,4902,1798,2548,5847,2020,4383,3409,3656,3663,3662,3657,3661,3665,3664,3660,3666,3659,3658,3669,3668,3667}'::integer[])))
  • Rows Removed by Filter: 6960
106. 0.610 0.610 ↑ 1.0 9,023 1

Index Only Scan using ix_geometryspecialareaid on geometryspecialarea gis_4 (cost=0.29..243.63 rows=9,023 width=4) (actual time=0.010..0.610 rows=9,023 loops=1)

  • Heap Fetches: 0
107. 1.497 1.497 ↑ 1.0 1 1,497

Index Only Scan using pk_organisation on organisation o (cost=0.28..0.46 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=1,497)

  • Index Cond: (id = sa.organisationid)
  • Heap Fetches: 0