explain.depesz.com

PostgreSQL's explain analyze made readable

Result: mTrt

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.067 25,154.810 ↑ 12.8 200 1

Unique (cost=23,001.52..23,020.64 rows=2,550 width=36) (actual time=25,154.737..25,154.810 rows=200 loops=1)

2. 0.054 25,154.743 ↑ 12.8 200 1

Sort (cost=23,001.52..23,007.89 rows=2,550 width=36) (actual time=25,154.737..25,154.743 rows=200 loops=1)

  • Sort Key: s.id, (array_agg(l.id))
  • Sort Method: quicksort Memory: 40kB
3. 0.304 25,154.689 ↑ 12.8 200 1

GroupAggregate (cost=22,806.23..22,857.23 rows=2,550 width=36) (actual time=25,154.369..25,154.689 rows=200 loops=1)

  • Group Key: s.id
4. 3.281 25,154.385 ↑ 3.1 831 1

Sort (cost=22,806.23..22,812.61 rows=2,550 width=8) (actual time=25,154.352..25,154.385 rows=831 loops=1)

  • Sort Key: s.id
  • Sort Method: quicksort Memory: 63kB
5. 2.631 25,151.104 ↑ 3.1 831 1

Nested Loop Left Join (cost=0.57..22,661.95 rows=2,550 width=8) (actual time=17.558..25,151.104 rows=831 loops=1)

6. 1.563 18.673 ↑ 12.8 200 1

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

7. 0.510 0.510 ↑ 12.8 200 1

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

8. 16.600 16.600 ↑ 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.083..0.083 rows=1 loops=200)

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

Index Scan using ix_locations_boundary_geom_indexed on locations l (cost=0.14..0.67 rows=1 width=2,667) (actual time=26.305..125.649 rows=4 loops=200)

  • Index Cond: (boundary_geom && s.public_geom)
  • Filter: (((location_type_id <> 4996) OR (s.survey_id = 257)) 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