explain.depesz.com

PostgreSQL's explain analyze made readable

Result: GK2T

Settings
# exclusive inclusive rows x rows loops node
1. 0.004 3,339.879 ↑ 10.5 2 1

Unique (cost=242,685.73..242,686.46 rows=21 width=256) (actual time=3,339.875..3,339.879 rows=2 loops=1)

2. 0.016 3,339.875 ↑ 10.5 2 1

Sort (cost=242,685.73..242,685.78 rows=21 width=256) (actual time=3,339.875..3,339.875 rows=2 loops=1)

  • Sort Key: "*SELECT* 1".gid, "*SELECT* 1".nrroadworks, (("*SELECT* 1".nrpermits)::bigint), "*SELECT* 1".nrprojectprep, "*SELECT* 1".nrproject, "*SELECT* 1".nrspecialareas, "*SELECT* 1".roadworkcluster, "*SELECT* 1".permitcluster, "*SELECT* 1".projectprepcluster, "*SELECT* 1".projectcluster, "*SELECT* 1".specialareacluster, "*SELECT* 1".provincebounds, "*SELECT* 1".coordinates
  • Sort Method: quicksort Memory: 26kB
3. 0.002 3,339.859 ↑ 10.5 2 1

Append (cost=237,414.91..242,685.26 rows=21 width=256) (actual time=2,751.993..3,339.859 rows=2 loops=1)

4. 0.001 2,751.993 ↑ 2.0 1 1

Subquery Scan on *SELECT* 1 (cost=237,414.91..237,426.53 rows=2 width=256) (actual time=2,751.993..2,751.993 rows=1 loops=1)

5. 0.155 2,751.992 ↑ 2.0 1 1

GroupAggregate (cost=237,414.91..237,426.48 rows=2 width=252) (actual time=2,751.992..2,751.992 rows=1 loops=1)

  • Group Key: sq.gid
6.          

CTE rw_data

7. 0.599 2,751.709 ↓ 6.0 12 1

GroupAggregate (cost=237,335.67..237,352.22 rows=2 width=36) (actual time=2,751.130..2,751.709 rows=12 loops=1)

  • Group Key: rw.id
8. 0.006 2,751.110 ↓ 7.0 14 1

Nested Loop (cost=237,335.67..237,352.18 rows=2 width=49) (actual time=2,751.082..2,751.110 rows=14 loops=1)

9. 0.004 2,751.080 ↓ 6.0 12 1

Unique (cost=237,335.24..237,335.25 rows=2 width=4) (actual time=2,751.077..2,751.080 rows=12 loops=1)

10. 0.009 2,751.076 ↓ 7.0 14 1

Sort (cost=237,335.24..237,335.25 rows=2 width=4) (actual time=2,751.075..2,751.076 rows=14 loops=1)

  • Sort Key: rw.id
  • Sort Method: quicksort Memory: 25kB
11. 0.045 2,751.067 ↓ 7.0 14 1

Result (cost=1.71..237,335.23 rows=2 width=4) (actual time=2,750.978..2,751.067 rows=14 loops=1)

  • One-Time Filter: ((to_timestamp('20181220 12:00:00 AM'::text, 'yyyyMMdd HH24:MI:SS AM'::text))::timestamp without time zone <= (to_timestamp('20190710 11:59:59 PM'::text, 'yyyyMMdd HH24:MI:SS AM'::text))::timestamp without time zone)
12. 0.005 2,751.022 ↓ 7.0 14 1

Nested Loop (cost=1.71..237,335.23 rows=2 width=4) (actual time=2,750.934..2,751.022 rows=14 loops=1)

13. 0.002 2,750.981 ↓ 6.0 12 1

Nested Loop Left Join (cost=1.27..237,319.98 rows=2 width=4) (actual time=2,750.923..2,750.981 rows=12 loops=1)

14. 0.025 2,750.967 ↓ 6.0 12 1

Nested Loop (cost=0.99..237,319.09 rows=2 width=8) (actual time=2,750.920..2,750.967 rows=12 loops=1)

15. 0.004 2,750.930 ↓ 6.0 12 1

Nested Loop (cost=0.71..237,318.20 rows=2 width=12) (actual time=2,750.892..2,750.930 rows=12 loops=1)

16. 0.018 2,750.902 ↓ 6.0 12 1

Nested Loop (cost=0.42..237,317.31 rows=2 width=16) (actual time=2,750.883..2,750.902 rows=12 loops=1)

  • Join Filter: (rw.roadworkstatustypeid = rs.id)
  • Rows Removed by Join Filter: 264
17. 0.015 0.015 ↑ 1.0 23 1

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

18. 0.031 2,750.869 ↓ 6.0 12 23

Materialize (cost=0.42..237,315.40 rows=2 width=20) (actual time=115.222..119.603 rows=12 loops=23)

19. 0.534 2,750.838 ↓ 6.0 12 1

Nested Loop (cost=0.42..237,315.39 rows=2 width=20) (actual time=2,650.099..2,750.838 rows=12 loops=1)

20. 2,741.604 2,741.604 ↑ 9.1 580 1

Seq Scan on roadworkrequestdates ad (cost=0.00..64,836.78 rows=5,269 width=4) (actual time=576.686..2,741.604 rows=580 loops=1)

  • Filter: ((completeddate >= (to_timestamp('20181220 12:00:00 AM'::text, 'yyyyMMdd HH24:MI:SS AM'::text))::timestamp without time zone) AND (startdate <= (to_timestamp('20190710 11:59:59 PM'::text, 'yyyyMMdd HH24:MI:SS AM'::text))::timestamp without time zone))
  • Rows Removed by Filter: 2014290
21. 5.917 8.700 ↓ 0.0 0 580

Index Scan using ix_actualdates on roadworkrequest rw (cost=0.42..32.72 rows=1 width=24) (actual time=0.015..0.015 rows=0 loops=580)

  • Index Cond: (actualroadworkrequestdatesid = ad.id)
  • Filter: ((roadworkstatustypeid <> ALL ('{15,14}'::integer[])) AND (roadworkstatustypeid = ANY ('{8,10,12,13,2,11,9}'::integer[])) AND ((SubPlan 4) OR (alternatives: SubPlan 5 or hashed SubPlan 6)))
  • Rows Removed by Filter: 0
22.          

SubPlan (forIndex Scan)

23. 0.860 2.580 ↓ 0.0 0 215

Hash Join (cost=8.46..53.55 rows=3 width=0) (actual time=0.012..0.012 rows=0 loops=215)

  • Hash Cond: (uo.id = c.organisationid)
24. 0.215 0.215 ↑ 2,550.0 1 215

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

25. 0.430 1.505 ↑ 1.0 1 215

Hash (cost=8.44..8.44 rows=1 width=4) (actual time=0.007..0.007 rows=1 loops=215)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
26. 1.075 1.075 ↑ 1.0 1 215

Index Only Scan using ix_combipartners_roadworkrequestid on combipartners c (cost=0.42..8.44 rows=1 width=4) (actual time=0.005..0.005 rows=1 loops=215)

  • Index Cond: (roadworkrequestid = rw.id)
  • Heap Fetches: 220
27. 0.203 0.203 ↓ 0.0 0 203

Seq Scan on userorgids uo_1 (cost=0.00..41.88 rows=13 width=0) (actual time=0.001..0.001 rows=0 loops=203)

  • Filter: (id = rw.roadworkrequestownerorganisationid)
  • Rows Removed by Filter: 1
28. 0.000 0.000 ↓ 0.0 0

Seq Scan on userorgids uo_2 (cost=0.00..35.50 rows=2,550 width=4) (never executed)

29. 0.024 0.024 ↑ 1.0 1 12

Index Only Scan using pk_organisation on organisation psm (cost=0.28..0.44 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=12)

  • Index Cond: (id = rw.roadworkrequestpsmorganisationid)
  • Heap Fetches: 0
30. 0.012 0.012 ↑ 1.0 1 12

Index Only Scan using pk_organisation on organisation ownr (cost=0.28..0.44 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=12)

  • Index Cond: (id = rw.roadworkrequestownerorganisationid)
  • Heap Fetches: 0
31. 0.012 0.012 ↑ 1.0 1 12

Index Only Scan using pk_organisation on organisation c_1 (cost=0.28..0.44 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=12)

  • Index Cond: (id = rw.roadworkrequestownercontractororganisationid)
  • Heap Fetches: 0
32. 0.036 0.036 ↑ 1.0 1 12

Index Only Scan using ix_geometryroadworkid on geometryroadwork gis_1 (cost=0.42..7.61 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=12)

  • Index Cond: (entityid = rw.id)
  • Heap Fetches: 14
33. 0.024 0.024 ↑ 1.0 1 12

Index Scan using ix_geometryroadworkid on geometryroadwork rwgis (cost=0.42..8.44 rows=1 width=49) (actual time=0.002..0.002 rows=1 loops=12)

  • Index Cond: (entityid = rw.id)
34. 0.003 2,751.837 ↓ 4.0 8 1

Sort (cost=62.69..62.69 rows=2 width=36) (actual time=2,751.837..2,751.837 rows=8 loops=1)

  • Sort Key: sq.gid
  • Sort Method: quicksort Memory: 25kB
35. 0.007 2,751.834 ↓ 4.0 8 1

Hash Join (cost=62.56..62.68 rows=2 width=36) (actual time=2,751.829..2,751.834 rows=8 loops=1)

  • Hash Cond: (sq.id = rw_data.id)
36. 0.001 0.103 ↓ 4.0 8 1

Subquery Scan on sq (cost=62.50..62.59 rows=2 width=8) (actual time=0.100..0.103 rows=8 loops=1)

  • Filter: (sq.gid IS NOT NULL)
  • Rows Removed by Filter: 4
37. 0.078 0.102 ↓ 6.0 12 1

WindowAgg (cost=62.50..62.56 rows=2 width=8) (actual time=0.099..0.102 rows=12 loops=1)

38.          

Initplan (forWindowAgg)

39. 0.001 0.017 ↑ 1.0 1 1

Limit (cost=0.00..31.25 rows=1 width=8) (actual time=0.016..0.017 rows=1 loops=1)

40. 0.016 0.016 ↑ 1.0 1 1

Seq Scan on moor_clusterzoomepsminratio moor_clusterzoomepsminratio_2 (cost=0.00..31.25 rows=1 width=8) (actual time=0.016..0.016 rows=1 loops=1)

  • Filter: (mapzoomlevel = 8)
  • Rows Removed by Filter: 1
41. 0.000 0.005 ↑ 1.0 1 1

Limit (cost=0.00..31.25 rows=1 width=4) (actual time=0.005..0.005 rows=1 loops=1)

42. 0.005 0.005 ↑ 1.0 1 1

Seq Scan on moor_clusterzoomepsminratio moor_clusterzoomepsminratio_3 (cost=0.00..31.25 rows=1 width=4) (actual time=0.004..0.005 rows=1 loops=1)

  • Filter: (mapzoomlevel = 8)
  • Rows Removed by Filter: 1
43. 0.002 0.002 ↓ 6.0 12 1

CTE Scan on rw_data rw_data_1 (cost=0.00..0.04 rows=2 width=36) (actual time=0.000..0.002 rows=12 loops=1)

44. 0.006 2,751.724 ↓ 6.0 12 1

Hash (cost=0.04..0.04 rows=2 width=36) (actual time=2,751.724..2,751.724 rows=12 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
45. 2,751.718 2,751.718 ↓ 6.0 12 1

CTE Scan on rw_data (cost=0.00..0.04 rows=2 width=36) (actual time=2,751.132..2,751.718 rows=12 loops=1)

46. 0.001 587.864 ↑ 19.0 1 1

Subquery Scan on *SELECT* 2 (cost=5,148.63..5,258.74 rows=19 width=256) (actual time=587.864..587.864 rows=1 loops=1)

47. 1.002 587.863 ↑ 19.0 1 1

HashAggregate (cost=5,148.63..5,258.31 rows=19 width=252) (actual time=587.862..587.863 rows=1 loops=1)

  • Group Key: sq_1.gid
48.          

CTE permit_data

49. 0.934 583.569 ↓ 11.7 223 1

GroupAggregate (cost=4,988.09..5,146.54 rows=19 width=36) (actual time=582.150..583.569 rows=223 loops=1)

  • Group Key: p.id
50. 0.041 582.635 ↓ 11.7 223 1

Nested Loop (cost=4,988.09..5,146.16 rows=19 width=156) (actual time=582.109..582.635 rows=223 loops=1)

51. 0.035 582.148 ↓ 11.7 223 1

Unique (cost=4,987.80..4,987.89 rows=19 width=4) (actual time=582.100..582.148 rows=223 loops=1)

52. 0.070 582.113 ↓ 11.7 223 1

Sort (cost=4,987.80..4,987.85 rows=19 width=4) (actual time=582.100..582.113 rows=223 loops=1)

  • Sort Key: p.id
  • Sort Method: quicksort Memory: 35kB
53. 0.026 582.043 ↓ 11.7 223 1

Result (cost=4,367.44..4,987.39 rows=19 width=4) (actual time=572.231..582.043 rows=223 loops=1)

  • One-Time Filter: ((to_timestamp('20181220 12:00:00 AM'::text, 'yyyyMMdd HH24:MI:SS AM'::text))::timestamp without time zone <= (to_timestamp('20190710 11:59:59 PM'::text, 'yyyyMMdd HH24:MI:SS AM'::text))::timestamp without time zone)
54. 0.000 582.017 ↓ 11.7 223 1

Nested Loop (cost=4,367.44..4,987.39 rows=19 width=4) (actual time=572.221..582.017 rows=223 loops=1)

55. 0.134 581.795 ↓ 11.7 223 1

Nested Loop (cost=4,367.14..4,980.06 rows=19 width=8) (actual time=572.216..581.795 rows=223 loops=1)

56. 0.410 580.992 ↓ 11.7 223 1

Nested Loop (cost=4,366.86..4,972.92 rows=19 width=12) (actual time=572.207..580.992 rows=223 loops=1)

57. 1.129 573.062 ↓ 1.9 1,880 1

Hash Join (cost=4,366.57..4,405.34 rows=1,002 width=8) (actual time=572.152..573.062 rows=1,880 loops=1)

  • Hash Cond: (gp.id = gis.entityid)
58. 548.692 548.692 ↓ 1.8 1,815 1

Function Scan on getpermitasownerandcoordinator gp (cost=0.25..10.25 rows=1,000 width=4) (actual time=548.584..548.692 rows=1,815 loops=1)

59. 9.680 23.241 ↓ 1.0 94,195 1

Hash (cost=3,188.92..3,188.92 rows=94,192 width=4) (actual time=23.241..23.241 rows=94,195 loops=1)

  • Buckets: 131072 Batches: 1 Memory Usage: 4336kB
60. 13.561 13.561 ↓ 1.0 94,195 1

Seq Scan on geometrypermit gis (cost=0.00..3,188.92 rows=94,192 width=4) (actual time=0.012..13.561 rows=94,195 loops=1)

61. 7.520 7.520 ↓ 0.0 0 1,880

Index Scan using pk_permit on permit p (cost=0.29..0.57 rows=1 width=12) (actual time=0.004..0.004 rows=0 loops=1,880)

  • Index Cond: (id = gis.entityid)
  • Filter: ((COALESCE(approvedperiodto, requestperiodto) >= (to_timestamp('20181220 12:00:00 AM'::text, 'yyyyMMdd HH24:MI:SS AM'::text))::timestamp without time zone) AND (COALESCE(approvedperiodfrom, requestperiodfrom) <= (to_timestamp('20190710 11:59:59 PM'::text, 'yyyyMMdd HH24:MI:SS AM'::text))::timestamp without time zone) AND (permitstatustypeid = ANY ('{4,6,2,1,3,7}'::integer[])))
  • Rows Removed by Filter: 1
62. 0.669 0.669 ↑ 1.0 1 223

Index Only Scan using pk_organisation on organisation psmorg (cost=0.28..0.38 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=223)

  • Index Cond: (id = p.permitpublicspacemanagerid)
  • Heap Fetches: 0
63. 0.223 0.223 ↑ 1.0 1 223

Index Only Scan using pk_organisation on organisation ownerorg (cost=0.28..0.38 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=223)

  • Index Cond: (id = p.permitownerorganisationid)
  • Heap Fetches: 0
64. 0.446 0.446 ↑ 1.0 1 223

Index Scan using ix_geometrypermitid on geometrypermit pergis (cost=0.29..8.31 rows=1 width=156) (actual time=0.002..0.002 rows=1 loops=223)

  • Index Cond: (entityid = p.id)
65. 0.039 586.861 ↓ 11.7 223 1

Hash Join (cost=0.78..1.85 rows=19 width=36) (actual time=586.792..586.861 rows=223 loops=1)

  • Hash Cond: (sq_1.id = permit_data.id)
66. 0.009 586.764 ↓ 11.7 223 1

Subquery Scan on sq_1 (cost=0.17..0.97 rows=19 width=8) (actual time=586.725..586.764 rows=223 loops=1)

  • Filter: (sq_1.gid IS NOT NULL)
67. 3.098 586.755 ↓ 11.7 223 1

WindowAgg (cost=0.17..0.78 rows=19 width=8) (actual time=586.724..586.755 rows=223 loops=1)

68.          

Initplan (forWindowAgg)

69. 0.001 0.020 ↑ 1.0 1 1

Limit (cost=0.00..0.08 rows=1 width=8) (actual time=0.020..0.020 rows=1 loops=1)

70. 0.019 0.019 ↑ 378.0 1 1

Seq Scan on moor_clusterzoomepsminratio (cost=0.00..31.25 rows=378 width=8) (actual time=0.019..0.019 rows=1 loops=1)

  • Filter: (mapzoomlevel = 7)
71. 0.001 0.005 ↑ 1.0 1 1

Limit (cost=0.00..0.08 rows=1 width=4) (actual time=0.005..0.005 rows=1 loops=1)

72. 0.004 0.004 ↑ 378.0 1 1

Seq Scan on moor_clusterzoomepsminratio moor_clusterzoomepsminratio_1 (cost=0.00..31.25 rows=378 width=4) (actual time=0.004..0.004 rows=1 loops=1)

  • Filter: (mapzoomlevel = 7)
73. 583.632 583.632 ↓ 11.7 223 1

CTE Scan on permit_data permit_data_1 (cost=0.00..0.38 rows=19 width=36) (actual time=582.152..583.632 rows=223 loops=1)

74. 0.037 0.058 ↓ 11.7 223 1

Hash (cost=0.38..0.38 rows=19 width=36) (actual time=0.058..0.058 rows=223 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 23kB
75. 0.021 0.021 ↓ 11.7 223 1

CTE Scan on permit_data (cost=0.00..0.38 rows=19 width=36) (actual time=0.001..0.021 rows=223 loops=1)