explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 5TDt

Settings
# exclusive inclusive rows x rows loops node
1. 0.005 5,340.642 ↑ 363.6 11 1

Nested Loop (cost=127,152,422,180.10..127,152,422,340.12 rows=4,000 width=104) (actual time=5,340.631..5,340.642 rows=11 loops=1)

2.          

CTE sample_well

3. 0.004 0.436 ↑ 1.0 1 1

Nested Loop (cost=0.71..153.66 rows=1 width=2,279) (actual time=0.347..0.436 rows=1 loops=1)

4. 0.035 0.035 ↑ 1.0 1 1

Index Scan using well_completion_report_parameters_pkey on well_completion_report_parameters pm (cost=0.42..8.44 rows=1 width=117) (actual time=0.035..0.035 rows=1 loops=1)

  • Index Cond: (wcrnumber = 'WCR2013-002893'::text)
5. 0.397 0.397 ↑ 11.0 1 1

Index Scan using geom_4326_idx on nhdpluscatchment b (cost=0.28..145.11 rows=11 width=2,162) (actual time=0.309..0.397 rows=1 loops=1)

  • Index Cond: (pm.geom && geom_4326)
  • Filter: _st_intersects(pm.geom, geom_4326)
  • Rows Removed by Filter: 1
6.          

CTE expanding

7. 0.027 60.096 ↑ 1.0 1 1

Nested Loop (cost=0.41..149.47 rows=1 width=8) (actual time=59.208..60.096 rows=1 loops=1)

8. 0.001 0.001 ↑ 1.0 1 1

CTE Scan on sample_well a (cost=0.00..0.02 rows=1 width=32) (actual time=0.001..0.001 rows=1 loops=1)

9. 60.068 60.068 ↑ 1.0 1 1

Index Scan using nhdpluscatchment_geog_4326_idx on nhdpluscatchment b_1 (cost=0.41..149.44 rows=1 width=2,170) (actual time=59.182..60.068 rows=1 loops=1)

  • Index Cond: (geog_4326 && _st_expand(a.geog_4326, '500'::double precision))
  • Filter: ((a.geog_4326 && _st_expand(geog_4326, '500'::double precision)) AND _st_dwithin(a.geog_4326, geog_4326, '500'::double precision, true))
  • Rows Removed by Filter: 3
10.          

CTE neighbours

11. 0.019 54.209 ↑ 10.0 11 1

Nested Loop (cost=0.28..145.24 rows=110 width=8) (actual time=24.039..54.209 rows=11 loops=1)

12. 0.001 0.001 ↑ 1.0 1 1

CTE Scan on sample_well (cost=0.00..0.02 rows=1 width=32) (actual time=0.000..0.001 rows=1 loops=1)

13. 54.189 54.189 ↑ 1.0 11 1

Index Scan using geom_4326_idx on nhdpluscatchment basins (cost=0.28..145.11 rows=11 width=2,162) (actual time=24.024..54.189 rows=11 loops=1)

  • Index Cond: (sample_well.catchment_geom_4326 && geom_4326)
  • Filter: _st_intersects(sample_well.catchment_geom_4326, geom_4326)
  • Rows Removed by Filter: 12
14.          

CTE all_catchments

15. 0.023 114.441 ↑ 10.2 11 1

HashAggregate (cost=4.20..5.32 rows=112 width=8) (actual time=114.439..114.441 rows=11 loops=1)

  • Group Key: sample_well_1.nhdplusid
16. 0.006 114.418 ↑ 8.6 13 1

Append (cost=0.00..3.92 rows=112 width=8) (actual time=0.004..114.418 rows=13 loops=1)

17. 0.092 0.092 ↑ 1.0 1 1

CTE Scan on sample_well sample_well_1 (cost=0.00..0.02 rows=1 width=8) (actual time=0.003..0.092 rows=1 loops=1)

18. 54.221 54.221 ↑ 10.0 11 1

CTE Scan on neighbours (cost=0.00..2.20 rows=110 width=8) (actual time=24.041..54.221 rows=11 loops=1)

19. 60.099 60.099 ↑ 1.0 1 1

CTE Scan on expanding (cost=0.00..0.02 rows=1 width=8) (actual time=59.211..60.099 rows=1 loops=1)

20.          

CTE stream_lines

21. 0.011 2,088.864 ↑ 10,181.8 11 1

Result (cost=130,116.43..159,826.39 rows=112,000 width=48) (actual time=2,088.650..2,088.864 rows=11 loops=1)

22. 6.805 2,088.853 ↑ 10,181.8 11 1

ProjectSet (cost=130,116.43..130,706.39 rows=112,000 width=48) (actual time=2,088.646..2,088.853 rows=11 loops=1)

23. 0.010 2,082.048 ↑ 10.2 11 1

Result (cost=130,116.43..130,117.83 rows=112 width=299) (actual time=2,082.027..2,082.048 rows=11 loops=1)

24. 0.072 2,082.038 ↑ 10.2 11 1

Sort (cost=130,116.43..130,116.71 rows=112 width=299) (actual time=2,082.024..2,082.038 rows=11 loops=1)

  • Sort Key: streams.nhdplusid
  • Sort Method: quicksort Memory: 38kB
25. 171.463 2,081.966 ↑ 10.2 11 1

Hash Join (cost=3.64..130,112.61 rows=112 width=299) (actual time=261.670..2,081.966 rows=11 loops=1)

  • Hash Cond: (streams.nhdplusid = all_catchments.nhdplusid)
26. 1,796.053 1,796.053 ↑ 1.0 341,762 1

Seq Scan on nhdflowline streams (cost=0.00..128,825.53 rows=341,953 width=299) (actual time=0.017..1,796.053 rows=341,762 loops=1)

27. 0.005 114.450 ↑ 10.2 11 1

Hash (cost=2.24..2.24 rows=112 width=8) (actual time=114.450..114.450 rows=11 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
28. 114.445 114.445 ↑ 10.2 11 1

CTE Scan on all_catchments (cost=0.00..2.24 rows=112 width=8) (actual time=114.441..114.445 rows=11 loops=1)

29.          

CTE line_measure

30. 2,639.855 4,728.745 ↑ 35,364.7 3,167 1

ProjectSet (cost=0.00..620,200.00 rows=112,000,000 width=52) (actual time=2,089.589..4,728.745 rows=3,167 loops=1)

31. 2,088.890 2,088.890 ↑ 10,181.8 11 1

CTE Scan on stream_lines sl (cost=0.00..2,240.00 rows=112,000 width=48) (actual time=2,088.658..2,088.890 rows=11 loops=1)

32.          

CTE geometries

33. 2.883 5,116.816 ↑ 35,364,698.5 3,167 1

Result (cost=0.00..30,551,640,000.00 rows=112,000,000,000 width=52) (actual time=2,089.628..5,116.816 rows=3,167 loops=1)

34. 271.959 5,113.933 ↑ 35,364,698.5 3,167 1

ProjectSet (cost=0.00..591,640,000.00 rows=112,000,000,000 width=52) (actual time=2,089.625..5,113.933 rows=3,167 loops=1)

35. 4,841.974 4,841.974 ↑ 35,364.7 3,167 1

CTE Scan on line_measure (cost=0.00..2,240,000.00 rows=112,000,000 width=52) (actual time=2,089.601..4,841.974 rows=3,167 loops=1)

36.          

CTE points_10m

37. 5,145.840 5,145.840 ↑ 35,364,698.5 3,167 1

CTE Scan on geometries (cost=0.00..3,360,000,000.00 rows=112,000,000,000 width=52) (actual time=2,089.631..5,145.840 rows=3,167 loops=1)

38.          

CTE distances

39. 136.088 5,295.570 ↑ 35,364,698.5 3,167 1

Nested Loop (cost=0.00..89,880,000,000.02 rows=112,000,000,000 width=76) (actual time=2,089.872..5,295.570 rows=3,167 loops=1)

40. 0.001 0.001 ↑ 1.0 1 1

CTE Scan on sample_well sample_well_2 (cost=0.00..0.02 rows=1 width=32) (actual time=0.000..0.001 rows=1 loops=1)

41. 5,159.481 5,159.481 ↑ 35,364,698.5 3,167 1

CTE Scan on points_10m p (cost=0.00..2,240,000,000.00 rows=112,000,000,000 width=52) (actual time=2,089.632..5,159.481 rows=3,167 loops=1)

42.          

CTE sum_dist_reachcode

43. 28.346 5,340.214 ↑ 3,636.4 11 1

HashAggregate (cost=3,360,000,000.00..3,360,000,400.00 rows=40,000 width=32) (actual time=5,340.046..5,340.214 rows=11 loops=1)

  • Group Key: distances.nhdplusid, distances.stream_width_reeves_m
44. 5,311.868 5,311.868 ↑ 35,364,698.5 3,167 1

CTE Scan on distances (cost=0.00..2,240,000,000.00 rows=112,000,000,000 width=32) (actual time=2,089.874..5,311.868 rows=3,167 loops=1)

45.          

CTE d

46. 0.017 5,340.282 ↑ 363.6 11 1

WindowAgg (cost=1,200.00..1,300.00 rows=4,000 width=40) (actual time=5,340.276..5,340.282 rows=11 loops=1)

47. 0.046 5,340.265 ↑ 363.6 11 1

HashAggregate (cost=1,200.00..1,240.00 rows=4,000 width=32) (actual time=5,340.243..5,340.265 rows=11 loops=1)

  • Group Key: sum_dist_reachcode.nhdplusid, sum_dist_reachcode.sum_inv_dist_sq, sum_dist_reachcode.stream_width_reeves_m
48. 5,340.219 5,340.219 ↑ 3,636.4 11 1

CTE Scan on sum_dist_reachcode (cost=0.00..800.00 rows=40,000 width=32) (actual time=5,340.047..5,340.219 rows=11 loops=1)

49. 0.351 0.351 ↑ 1.0 1 1

CTE Scan on sample_well s (cost=0.00..0.02 rows=1 width=72) (actual time=0.350..0.351 rows=1 loops=1)

50. 5,340.286 5,340.286 ↑ 363.6 11 1

CTE Scan on d (cost=0.00..80.00 rows=4,000 width=32) (actual time=5,340.277..5,340.286 rows=11 loops=1)

Planning time : 95.699 ms