explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Dr7V

Settings
# exclusive inclusive rows x rows loops node
1. 5.296 5,015.232 ↑ 1.0 1 1

Aggregate (cost=5,352.59..5,352.60 rows=1 width=20) (actual time=5,015.232..5,015.232 rows=1 loops=1)

2. 1.831 5,009.936 ↓ 10,291.0 10,291 1

Nested Loop (cost=171.01..5,352.59 rows=1 width=8) (actual time=2,955.642..5,009.936 rows=10,291 loops=1)

3. 1.384 3,140.680 ↓ 10,671.0 10,671 1

Nested Loop (cost=170.58..5,347.42 rows=1 width=12) (actual time=2,955.525..3,140.680 rows=10,671 loops=1)

4. 0.003 0.116 ↑ 1.0 1 1

Nested Loop (cost=0.84..16.89 rows=1 width=3,040) (actual time=0.114..0.116 rows=1 loops=1)

5. 0.078 0.078 ↑ 1.0 1 1

Index Scan using ix_location_name on locations l (cost=0.42..8.44 rows=1 width=3,044) (actual time=0.078..0.078 rows=1 loops=1)

  • Index Cond: ((name)::text = 'Broughton Astley CP'::text)
  • Filter: (NOT deleted)
6. 0.035 0.035 ↑ 1.0 1 1

Index Scan using fki_locations_websites_location on locations_websites lw (cost=0.42..8.44 rows=1 width=4) (actual time=0.033..0.035 rows=1 loops=1)

  • Index Cond: (location_id = l.id)
  • Filter: ((NOT deleted) AND (website_id = 8))
7. 185.702 3,139.180 ↓ 2,667.8 10,671 1

Bitmap Heap Scan on cache_occurrences_functional o (cost=169.74..5,330.49 rows=4 width=119) (actual time=2,955.408..3,139.180 rows=10,671 loops=1)

  • Recheck Cond: (l.boundary_geom && public_geom)
  • Filter: ((survey_id = 15) AND (record_status = 'V'::bpchar) AND _st_intersects(l.boundary_geom, public_geom))
  • Rows Removed by Filter: 1816
  • Heap Blocks: exact=9807
8. 2,953.478 2,953.478 ↓ 10.1 12,579 1

Bitmap Index Scan on ix_cache_occurrences_functional_public_geom (cost=0.00..169.74 rows=1,243 width=0) (actual time=2,953.478..2,953.478 rows=12,579 loops=1)

  • Index Cond: (l.boundary_geom && public_geom)
9. 1,867.425 1,867.425 ↑ 1.0 1 10,671

Index Scan using pk_cache_samples_nonfunctional on cache_samples_nonfunctional snf (cost=0.43..5.16 rows=1 width=4) (actual time=0.175..0.175 rows=1 loops=10,671)

  • Index Cond: (id = o.sample_id)
  • Filter: (length((public_entered_sref)::text) >= 8)
  • Rows Removed by Filter: 0