explain.depesz.com

PostgreSQL's explain analyze made readable

Result: IKyS : test

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 0.020 ↓ 0.0 0 1

GroupAggregate (cost=1,053.10..1,274.95 rows=3,060 width=116) (actual time=0.020..0.020 rows=0 loops=1)

  • Output: w.device_id, p.incident_id, 'pending', r.region, count(w.id), json_agg(w.*), r.id
  • Group Key: w.device_id, p.incident_id, r.id
  • Buffers: shared hit=2
2. 0.007 0.018 ↓ 0.0 0 1

Sort (cost=1,053.10..1,083.70 rows=12,240 width=157) (actual time=0.018..0.018 rows=0 loops=1)

  • Output: w.device_id, p.incident_id, r.id, r.region, w.id, w.*
  • Sort Key: w.device_id, r.id
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=2
3. 0.002 0.011 ↓ 0.0 0 1

Nested Loop (cost=0.14..222.05 rows=12,240 width=157) (actual time=0.011..0.011 rows=0 loops=1)

  • Output: w.device_id, p.incident_id, r.id, r.region, w.id, w.*
  • Buffers: shared hit=2
4. 0.004 0.004 ↑ 1,020.0 1 1

Seq Scan on public.regions r (cost=0.00..20.20 rows=1,020 width=36) (actual time=0.004..0.004 rows=1 loops=1)

  • Output: r.id, r.region, r.created_at, r.updated_at
  • Buffers: shared hit=1
5. 0.001 0.005 ↓ 0.0 0 1

Materialize (cost=0.14..48.88 rows=12 width=121) (actual time=0.005..0.005 rows=0 loops=1)

  • Output: p.incident_id, w.device_id, w.id, w.*
  • Buffers: shared hit=1
6. 0.000 0.004 ↓ 0.0 0 1

Nested Loop (cost=0.14..48.82 rows=12 width=121) (actual time=0.004..0.004 rows=0 loops=1)

  • Output: p.incident_id, w.device_id, w.id, w.*
  • Join Filter: CASE WHEN (p.type = 'polygon'::shape_type) THEN ((p.polygon && _st_expand(w."position", ((w.radius * '1000'::numeric))::double precision)) AND (w."position" && _st_expand(p.polygon, ((w.radius * '1000'::numeric))::double precision)) AND _st_dwithin(p.polygon, w."position", ((w.radius * '1000'::numeric))::double precision, true)) WHEN ((p.type = 'circle'::shape_type) AND (p.radius = '0'::numeric)) THEN ((p."position" && _st_expand(w."position", ((w.radius * '1000'::numeric))::double precision)) AND (w."position" && _st_expand(p."position", ((w.radius * '1000'::numeric))::double precision)) AND _st_dwithin(p."position", w."position", ((w.radius * '1000'::numeric))::double precision, true)) WHEN ((p.type = 'circle'::shape_type) AND (p.radius > '0'::numeric)) THEN ((geography(st_transform(st_buffer(st_transform(geometry(p."position"), _st_bestsrid(p."position", p."position")), ((p.radius * '1000'::numeric))::double precision), 4326)) && _st_expand(w."position", ((w.radius * '1000'::numeric))::double precision)) AND (w."position" && _st_expand(geography(st_transform(st_buffer(st_transform(geometry(p."position"), _st_bestsrid(p."position", p."position")), ((p.radius * '1000'::numeric))::double precision), 4326)), ((w.radius * '1000'::numeric))::double precision)) AND _st_dwithin(geography(st_transform(st_buffer(st_transform(geometry(p."position"), _st_bestsrid(p."position", p."position")), ((p.radius * '1000'::numeric))::double precision), 4326)), w."position", ((w.radius * '1000'::numeric))::double precision, true)) ELSE NULL::boolean END
  • Buffers: shared hit=1
7. 0.004 0.004 ↓ 0.0 0 1

Index Scan using idx_incident_polygons_incident_id on public.incident_polygons p (cost=0.14..8.16 rows=1 width=309,223) (actual time=0.004..0.004 rows=0 loops=1)

  • Output: p.id, p.incident_id, p.polygon, p."position", p.radius, p.type, p.created_at, p.updated_at
  • Index Cond: (p.incident_id = 65009)
  • Buffers: shared hit=1
8. 0.000 0.000 ↓ 0.0 0

Seq Scan on public.watch_zones w (cost=0.00..1.24 rows=24 width=154) (never executed)