explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ekzJ : Optimization for: plan #mTrt using temp table of just indexed locations

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.063 54,446.959 ↑ 12.8 200 1

Unique (cost=23,002.17..23,021.29 rows=2,550 width=36) (actual time=54,446.887..54,446.959 rows=200 loops=1)

2. 0.062 54,446.896 ↑ 12.8 200 1

Sort (cost=23,002.17..23,008.54 rows=2,550 width=36) (actual time=54,446.886..54,446.896 rows=200 loops=1)

  • Sort Key: s.id, (array_agg(l.id))
  • Sort Method: quicksort Memory: 40kB
3. 0.281 54,446.834 ↑ 12.8 200 1

GroupAggregate (cost=22,806.88..22,857.88 rows=2,550 width=36) (actual time=54,446.539..54,446.834 rows=200 loops=1)

  • Group Key: s.id
4. 2.403 54,446.553 ↑ 3.1 831 1

Sort (cost=22,806.88..22,813.26 rows=2,550 width=8) (actual time=54,446.523..54,446.553 rows=831 loops=1)

  • Sort Key: s.id
  • Sort Method: quicksort Memory: 63kB
5. 2.430 54,444.150 ↑ 3.1 831 1

Nested Loop Left Join (cost=0.57..22,662.60 rows=2,550 width=8) (actual time=16.592..54,444.150 rows=831 loops=1)

6. 1.390 38.120 ↑ 12.8 200 1

Nested Loop (cost=0.43..20,919.00 rows=2,550 width=108) (actual time=0.082..38.120 rows=200 loops=1)

7. 0.330 0.330 ↑ 12.8 200 1

Seq Scan on smplist sl (cost=0.00..35.50 rows=2,550 width=4) (actual time=0.020..0.330 rows=200 loops=1)

8. 36.400 36.400 ↑ 1.0 1 200

Index Scan using pk_cache_samples_functional on cache_samples_functional s (cost=0.43..8.19 rows=1 width=108) (actual time=0.182..0.182 rows=1 loops=200)

  • Index Cond: (id = sl.record_id)
9. 54,403.600 54,403.600 ↓ 4.0 4 200

Index Scan using iloc_geom on iloc l (cost=0.14..0.67 rows=1 width=40) (actual time=59.961..272.018 rows=4 loops=200)

  • Index Cond: (boundary_geom && s.public_geom)
  • Filter: (((location_type_id <> 4996) OR (s.survey_id = 257)) AND (location_type_id = ANY ('{15,1370,2188,4839,4980,4996,1103}'::integer[])) AND _st_intersects(boundary_geom, s.public_geom) AND ((NOT (boundary_geom && s.public_geom)) OR (NOT _st_touches(boundary_geom, s.public_geom))))
  • Rows Removed by Filter: 1