explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Stii

Settings
# exclusive inclusive rows x rows loops node
1. 16.892 9,777.816 ↓ 13.8 2,760 1

GroupAggregate (cost=1,862,091.69..1,862,596.25 rows=200 width=40) (actual time=9,760.705..9,777.816 rows=2,760 loops=1)

  • Group Key: rw.id
2. 1.021 9,760.924 ↓ 13.3 2,773 1

Sort (cost=1,862,091.69..1,862,092.21 rows=208 width=50) (actual time=9,760.630..9,760.924 rows=2,773 loops=1)

  • Sort Key: rw.id
  • Sort Method: quicksort Memory: 319kB
3. 1.402 9,759.903 ↓ 13.3 2,773 1

Nested Loop (cost=1,860,417.61..1,862,083.68 rows=208 width=50) (actual time=9,752.539..9,759.903 rows=2,773 loops=1)

4. 1.458 9,752.981 ↓ 13.8 2,760 1

HashAggregate (cost=1,860,417.18..1,860,419.18 rows=200 width=4) (actual time=9,752.513..9,752.981 rows=2,760 loops=1)

  • Group Key: rw.id
5. 238.657 9,751.523 ↑ 723.2 2,760 1

Hash Join (cost=1,767,673.48..1,855,427.43 rows=1,995,901 width=4) (actual time=9,521.429..9,751.523 rows=2,760 loops=1)

  • Hash Cond: (rw.actualroadworkrequestdatesid = ad.id)
6. 0.449 3,694.188 ↑ 746.3 2,789 1

Unique (cost=1,655,267.52..1,670,878.21 rows=2,081,426 width=8) (actual time=3,693.530..3,694.188 rows=2,789 loops=1)

7. 1.495 3,693.739 ↑ 374.8 5,554 1

Sort (cost=1,655,267.52..1,660,471.08 rows=2,081,426 width=8) (actual time=3,693.529..3,693.739 rows=5,554 loops=1)

  • Sort Key: rw.id, rw.actualroadworkrequestdatesid
  • Sort Method: quicksort Memory: 453kB
8. 0.200 3,692.244 ↑ 374.8 5,554 1

Append (cost=793,682.80..1,379,913.80 rows=2,081,426 width=8) (actual time=2,258.473..3,692.244 rows=5,554 loops=1)

9. 0.333 2,258.929 ↑ 373.1 2,789 1

Unique (cost=793,682.80..816,306.86 rows=1,040,713 width=8) (actual time=2,258.472..2,258.929 rows=2,789 loops=1)

10. 0.743 2,258.596 ↑ 1,076.6 2,802 1

Sort (cost=793,682.80..801,224.15 rows=3,016,541 width=8) (actual time=2,258.471..2,258.596 rows=2,802 loops=1)

  • Sort Key: rw.id, rw.actualroadworkrequestdatesid
  • Sort Method: quicksort Memory: 228kB
11. 18.302 2,257.853 ↑ 1,076.6 2,802 1

Merge Join (cost=338,524.59..386,547.66 rows=3,016,541 width=8) (actual time=2,257.174..2,257.853 rows=2,802 loops=1)

  • Merge Cond: (uo.id = c.organisationid)
12. 0.014 0.029 ↑ 2,550.0 1 1

Sort (cost=179.78..186.16 rows=2,550 width=4) (actual time=0.028..0.029 rows=1 loops=1)

  • Sort Key: uo.id
  • Sort Method: quicksort Memory: 25kB
13. 0.015 0.015 ↑ 2,550.0 1 1

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

14. 14.902 2,239.522 ↑ 4.5 245,463 1

Materialize (cost=338,344.81..343,869.22 rows=1,104,882 width=12) (actual time=2,201.815..2,239.522 rows=245,463 loops=1)

15. 363.018 2,224.620 ↑ 4.5 245,463 1

Sort (cost=338,344.81..341,107.02 rows=1,104,882 width=12) (actual time=2,201.812..2,224.620 rows=245,463 loops=1)

  • Sort Key: c.organisationid
  • Sort Method: external merge Disk: 23856kB
16. 642.918 1,861.602 ↓ 1.0 1,105,703 1

Hash Join (cost=103,954.35..208,557.23 rows=1,104,882 width=12) (actual time=841.348..1,861.602 rows=1,105,703 loops=1)

  • Hash Cond: (gis.entityid = rw.id)
17. 244.274 405.408 ↑ 1.0 1,105,703 1

Merge Join (cost=0.99..73,695.19 rows=1,141,432 width=12) (actual time=0.032..405.408 rows=1,105,703 loops=1)

  • Merge Cond: (gis.entityid = c.roadworkrequestid)
18. 75.297 75.297 ↑ 1.0 1,045,523 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.017..75.297 rows=1,045,523 loops=1)

  • Heap Fetches: 0
19. 85.837 85.837 ↓ 1.0 1,105,898 1

Index Only Scan using ix_combipartners_roadworkrequestid on combipartners c (cost=0.43..28,569.41 rows=1,099,799 width=8) (actual time=0.013..85.837 rows=1,105,898 loops=1)

  • Heap Fetches: 0
20. 169.740 813.276 ↓ 1.0 1,040,917 1

Hash (cost=86,878.44..86,878.44 rows=1,040,713 width=8) (actual time=813.276..813.276 rows=1,040,917 loops=1)

  • Buckets: 131072 Batches: 16 Memory Usage: 3572kB
21. 156.637 643.536 ↓ 1.0 1,040,917 1

Hash Join (cost=490.90..86,878.44 rows=1,040,713 width=8) (actual time=2.353..643.536 rows=1,040,917 loops=1)

  • Hash Cond: (rw.roadworkrequestownerorganisationid = ownr.id)
22. 136.442 485.782 ↓ 1.0 1,040,917 1

Hash Join (cost=246.21..73,496.26 rows=1,040,713 width=12) (actual time=1.203..485.782 rows=1,040,917 loops=1)

  • Hash Cond: (rw.roadworkrequestpsmorganisationid = psm.id)
23. 254.585 348.216 ↓ 1.0 1,040,917 1

Hash Join (cost=1.52..60,114.07 rows=1,040,713 width=16) (actual time=0.043..348.216 rows=1,040,917 loops=1)

  • Hash Cond: (rw.roadworkstatustypeid = rs.id)
24. 93.603 93.603 ↓ 1.0 1,040,917 1

Seq Scan on roadworkrequest rw (cost=0.00..47,375.13 rows=1,040,713 width=24) (actual time=0.010..93.603 rows=1,040,917 loops=1)

25. 0.019 0.028 ↑ 1.0 23 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
26. 0.009 0.009 ↑ 1.0 23 1

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

27. 0.690 1.124 ↑ 1.0 6,124 1

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

  • Buckets: 8192 Batches: 1 Memory Usage: 280kB
28. 0.434 0.434 ↑ 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.434 rows=6,124 loops=1)

  • Heap Fetches: 0
29. 0.650 1.117 ↑ 1.0 6,124 1

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

  • Buckets: 8192 Batches: 1 Memory Usage: 280kB
30. 0.467 0.467 ↑ 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.015..0.467 rows=6,124 loops=1)

  • Heap Fetches: 0
31. 0.318 1,433.115 ↑ 376.4 2,765 1

Unique (cost=522,071.83..542,792.68 rows=1,040,713 width=8) (actual time=1,432.689..1,433.115 rows=2,765 loops=1)

32. 1.173 1,432.797 ↑ 994.5 2,778 1

Sort (cost=522,071.83..528,978.78 rows=2,762,781 width=8) (actual time=1,432.688..1,432.797 rows=2,778 loops=1)

  • Sort Key: rw_1.id, rw_1.actualroadworkrequestdatesid
  • Sort Method: quicksort Memory: 227kB
33. 59.243 1,431.624 ↑ 994.5 2,778 1

Hash Join (cost=67,072.51..150,935.17 rows=2,762,781 width=8) (actual time=419.477..1,431.624 rows=2,778 loops=1)

  • Hash Cond: (rw_1.roadworkrequestownerorganisationid = ownr_1.id)
34. 161.322 1,371.103 ↑ 1.0 1,045,523 1

Hash Join (cost=66,728.25..142,316.70 rows=1,045,523 width=12) (actual time=397.907..1,371.103 rows=1,045,523 loops=1)

  • Hash Cond: (rw_1.roadworkrequestpsmorganisationid = psm_1.id)
35. 140.517 1,208.578 ↑ 1.0 1,045,523 1

Hash Join (cost=66,483.56..128,873.80 rows=1,045,523 width=16) (actual time=396.677..1,208.578 rows=1,045,523 loops=1)

  • Hash Cond: (rw_1.roadworkstatustypeid = rs_1.id)
36. 570.757 1,068.040 ↑ 1.0 1,045,523 1

Hash Join (cost=66,482.04..116,075.99 rows=1,045,523 width=20) (actual time=396.650..1,068.040 rows=1,045,523 loops=1)

  • Hash Cond: (gis_1.entityid = rw_1.id)
37. 106.355 106.355 ↑ 1.0 1,045,523 1

Seq Scan on geometryroadwork gis_1 (cost=0.00..22,126.23 rows=1,045,523 width=4) (actual time=0.006..106.355 rows=1,045,523 loops=1)

38. 206.316 390.928 ↓ 1.0 1,040,917 1

Hash (cost=47,375.13..47,375.13 rows=1,040,713 width=24) (actual time=390.928..390.928 rows=1,040,917 loops=1)

  • Buckets: 65536 Batches: 32 Memory Usage: 2299kB
39. 184.612 184.612 ↓ 1.0 1,040,917 1

Seq Scan on roadworkrequest rw_1 (cost=0.00..47,375.13 rows=1,040,713 width=24) (actual time=0.012..184.612 rows=1,040,917 loops=1)

40. 0.007 0.021 ↑ 1.0 23 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
41. 0.014 0.014 ↑ 1.0 23 1

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

42. 0.709 1.203 ↑ 1.0 6,124 1

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

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

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

  • Heap Fetches: 0
44. 0.006 1.278 ↑ 2,550.0 1 1

Hash (cost=312.39..312.39 rows=2,550 width=8) (actual time=1.278..1.278 rows=1 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 33kB
45. 0.025 1.272 ↑ 2,550.0 1 1

Hash Join (cost=244.69..312.39 rows=2,550 width=8) (actual time=1.272..1.272 rows=1 loops=1)

  • Hash Cond: (uo_1.id = ownr_1.id)
46. 0.016 0.016 ↑ 2,550.0 1 1

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

47. 0.725 1.231 ↑ 1.0 6,124 1

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

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

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

  • Heap Fetches: 0
49. 352.798 5,818.678 ↓ 1.0 2,259,470 1

Hash (cost=75,493.05..75,493.05 rows=2,249,913 width=4) (actual time=5,818.678..5,818.678 rows=2,259,470 loops=1)

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

Seq Scan on roadworkrequestdates ad (cost=0.00..75,493.05 rows=2,249,913 width=4) (actual time=6.720..5,465.880 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
51. 5.520 5.520 ↑ 1.0 1 2,760

Index Scan using ix_geometryroadworkid on geometryroadwork rwgis (cost=0.42..8.30 rows=1 width=50) (actual time=0.002..0.002 rows=1 loops=2,760)

  • Index Cond: (entityid = rw.id)