explain.depesz.com

PostgreSQL's explain analyze made readable

Result: usbM

Settings
# exclusive inclusive rows x rows loops node
1. 0.021 81,680.003 ↑ 1.4 43 1

Sort (cost=7,620.70..7,620.85 rows=61 width=27) (actual time=81,679.998..81,680.003 rows=43 loops=1)

  • Sort Key: (sum(((st_valuecount(st_clip(cv.rast, NULL::integer[], lp.the_geom, NULL::double precision[], true), 1, true, NULL::double precision[], '0'::double precision)).count))) DESC
  • Sort Method: quicksort Memory: 28kB
  • Buffers: shared hit=105,835 read=10
2. 0.033 81,679.982 ↑ 1.4 43 1

Hash Join (cost=7,613.28..7,618.89 rows=61 width=27) (actual time=81,679.957..81,679.982 rows=43 loops=1)

  • Hash Cond: (((st_valuecount(st_clip(cv.rast, NULL::integer[], lp.the_geom, NULL::double precision[], true), 1, true, NULL::double precision[], '0'::double precision)).value) = (calveg_values_whrtype.pixel_value)::double precision)
  • Buffers: shared hit=105,835 read=10
3. 4.399 81,679.910 ↑ 4.7 43 1

HashAggregate (cost=7,610.91..7,612.91 rows=200 width=16) (actual time=81,679.901..81,679.910 rows=43 loops=1)

  • Group Key: (st_valuecount(st_clip(cv.rast, NULL::integer[], lp.the_geom, NULL::double precision[], true), 1, true, NULL::double precision[], '0'::double precision)).value
  • Buffers: shared hit=105,834 read=10
4.          

CTE lp

5. 3.498 3.498 ↑ 1.0 417 1

Index Scan using ind_district_2014 on congdistrictsdicesimple50_2014 lp_1 (cost=0.28..392.56 rows=417 width=961) (actual time=0.373..3.498 rows=417 loops=1)

  • Index Cond: (district = '04'::text)
  • Buffers: shared hit=113 read=2
6. 78,918.263 81,675.511 ↑ 28.3 10,060 1

Nested Loop (cost=0.14..2,943.35 rows=285,000 width=12) (actual time=19.309..81,675.511 rows=10,060 loops=1)

  • Buffers: shared hit=105,834 read=10
7. 4.214 4.214 ↑ 1.0 417 1

CTE Scan on lp (cost=0.00..8.34 rows=417 width=32) (actual time=0.378..4.214 rows=417 loops=1)

  • Buffers: shared hit=113 read=2
8. 2,753.034 2,753.034 ↓ 2.0 2 417

Index Scan using calveg_whrtype_20m_st_convexhull_idx on calveg_whrtype_20m cv (cost=0.14..2.93 rows=1 width=31) (actual time=3.205..6.602 rows=2 loops=417)

  • Index Cond: ((rast)::geometry && lp.the_geom)
  • Filter: _st_intersects(lp.the_geom, rast, NULL::integer)
  • Rows Removed by Filter: 0
  • Buffers: shared hit=36,713 read=8
9. 0.017 0.039 ↑ 1.0 61 1

Hash (cost=1.61..1.61 rows=61 width=23) (actual time=0.039..0.039 rows=61 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 12kB
  • Buffers: shared hit=1
10. 0.022 0.022 ↑ 1.0 61 1

Seq Scan on calveg_values_whrtype (cost=0.00..1.61 rows=61 width=23) (actual time=0.015..0.022 rows=61 loops=1)

  • Buffers: shared hit=1
Planning time : 2.246 ms
Execution time : 81,680.292 ms