explain.depesz.com

PostgreSQL's explain analyze made readable

Result: s8vi

Settings
# exclusive inclusive rows x rows loops node
1. 0.025 32,776.832 ↑ 1.4 43 1

Sort (cost=295,106.48..295,106.63 rows=61 width=27) (actual time=32,776.830..32,776.832 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=71,355
  • Functions: 29
  • Options: Inlining false, Optimization false, Expressions true, Deforming true
  • Timing: Generation 8.647 ms, Inlining 0.000 ms, Optimization 2.738 ms, Emission 49.464 ms, Total 60.848 ms
2. 0.034 32,776.807 ↑ 1.4 43 1

Hash Join (cost=295,099.06..295,104.67 rows=61 width=27) (actual time=32,776.784..32,776.807 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=71,355
3. 2.855 32,723.570 ↑ 4.7 43 1

HashAggregate (cost=295,096.69..295,098.69 rows=200 width=16) (actual time=32,723.563..32,723.570 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=71,354
4. 1.799 32,720.715 ↑ 28.3 10,060 1

Result (cost=0.43..290,821.69 rows=285,000 width=12) (actual time=20.627..32,720.715 rows=10,060 loops=1)

  • Buffers: shared hit=71,354
5. 32,608.833 32,718.916 ↑ 28.3 10,060 1

ProjectSet (cost=0.43..2,971.69 rows=285,000 width=32) (actual time=20.615..32,718.916 rows=10,060 loops=1)

  • Buffers: shared hit=71,354
6. 1.222 110.083 ↓ 3.1 872 1

Nested Loop (cost=0.43..1,402.77 rows=285 width=992) (actual time=2.732..110.083 rows=872 loops=1)

  • Buffers: shared hit=36,849
7. 0.858 0.858 ↑ 1.0 417 1

Index Scan using ind_district_2014 on congdistrictsdicesimple50_2014 lp (cost=0.28..226.73 rows=417 width=961) (actual time=0.059..0.858 rows=417 loops=1)

  • Index Cond: (district = '04'::text)
  • Buffers: shared hit=115
8. 108.003 108.003 ↓ 2.0 2 417

Index Scan using calveg_whrtype_20m_st_convexhull_idx on calveg_whrtype_20m cv (cost=0.14..2.81 rows=1 width=31) (actual time=0.124..0.259 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,717
9. 0.056 53.203 ↑ 1.0 61 1

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

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

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

  • Buffers: shared hit=1
Execution time : 32,786.040 ms