explain.depesz.com

PostgreSQL's explain analyze made readable

Result: X7BZ

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

Nested Loop (cost=126,312,422,180.10..126,312,422,340.12 rows=4,000 width=104) (actual time=5,542.791..5,542.800 rows=11 loops=1)

2.          

CTE sample_well

3. 0.004 0.165 ↑ 1.0 1 1

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

4. 0.031 0.031 ↑ 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.030..0.031 rows=1 loops=1)

  • Index Cond: (wcrnumber = 'WCR2013-002893'::text)
5. 0.130 0.130 ↑ 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.080..0.130 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.010 32.015 ↑ 1.0 1 1

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

8. 0.002 0.002 ↑ 1.0 1 1

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

9. 32.003 32.003 ↑ 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=31.146..32.003 rows=1 loops=1)

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

CTE neighbours

11. 0.017 13.245 ↑ 10.0 11 1

Nested Loop (cost=0.28..145.24 rows=110 width=8) (actual time=11.336..13.245 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. 13.227 13.227 ↑ 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=11.323..13.227 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.018 45.347 ↑ 10.2 11 1

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

  • Group Key: sample_well_1.nhdplusid
16. 0.004 45.329 ↑ 8.6 13 1

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

17. 0.052 0.052 ↑ 1.0 1 1

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

18. 13.255 13.255 ↑ 10.0 11 1

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

19. 32.018 32.018 ↑ 1.0 1 1

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

20.          

CTE stream_lines

21. 0.017 2,572.334 ↑ 10,181.8 11 1

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

22. 0.275 2,572.317 ↑ 10,181.8 11 1

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

23. 0.009 2,572.042 ↑ 10.2 11 1

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

24. 0.065 2,572.033 ↑ 10.2 11 1

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

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

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

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

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

27. 0.005 45.356 ↑ 10.2 11 1

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

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

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

29.          

CTE line_measure

30. 2,535.101 5,107.455 ↑ 35,364.7 3,167 1

ProjectSet (cost=0.00..620,200.00 rows=112,000,000 width=52) (actual time=2,573.007..5,107.455 rows=3,167 loops=1)

31. 2,572.354 2,572.354 ↑ 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,572.082..2,572.354 rows=11 loops=1)

32.          

CTE geometries

33. 1.551 5,273.142 ↑ 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,573.031..5,273.142 rows=3,167 loops=1)

34. 119.428 5,271.591 ↑ 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,573.029..5,271.591 rows=3,167 loops=1)

35. 5,152.163 5,152.163 ↑ 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,573.011..5,152.163 rows=3,167 loops=1)

36.          

CTE points_10m

37. 5,307.582 5,307.582 ↑ 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,573.034..5,307.582 rows=3,167 loops=1)

38.          

CTE distances

39. 182.617 5,493.220 ↑ 35,364,698.5 3,167 1

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

40. 0.002 0.002 ↑ 1.0 1 1

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

41. 5,310.601 5,310.601 ↑ 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,573.035..5,310.601 rows=3,167 loops=1)

42.          

CTE sum_dist_reachcode

43. 23.545 5,542.610 ↑ 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,542.434..5,542.610 rows=11 loops=1)

  • Group Key: distances.nhdplusid, distances.stream_width_reeves_m
44. 5,519.065 5,519.065 ↑ 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,573.259..5,519.065 rows=3,167 loops=1)

45.          

CTE d

46. 0.014 5,542.674 ↑ 363.6 11 1

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

47. 0.046 5,542.660 ↑ 363.6 11 1

HashAggregate (cost=1,200.00..1,240.00 rows=4,000 width=32) (actual time=5,542.635..5,542.660 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,542.614 5,542.614 ↑ 3,636.4 11 1

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

49. 0.117 0.117 ↑ 1.0 1 1

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

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

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

Planning time : 1.537 ms