explain.depesz.com

PostgreSQL's explain analyze made readable

Result: I3n9 : Optimization for: plan #O2Rt

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.006 185.936 ↓ 8.0 8 1

Limit (cost=3,289.20..3,289.21 rows=1 width=401) (actual time=185.931..185.936 rows=8 loops=1)

2. 0.829 185.930 ↓ 8.0 8 1

Sort (cost=3,289.20..3,289.21 rows=1 width=401) (actual time=185.930..185.930 rows=8 loops=1)

  • Sort Key: (COALESCE(vw_occurrences_by_location.verified_on, vw_occurrences_by_location.created_on)) DESC, vw_occurrences_by_location.image_path
  • Sort Method: top-N heapsort Memory: 29kB
3. 4.826 185.101 ↓ 2,429.0 2,429 1

Subquery Scan on vw_occurrences_by_location (cost=3,289.16..3,289.19 rows=1 width=401) (actual time=180.221..185.101 rows=2,429 loops=1)

4. 1.876 180.275 ↓ 2,429.0 2,429 1

Sort (cost=3,289.16..3,289.17 rows=1 width=367) (actual time=180.212..180.275 rows=2,429 loops=1)

  • Sort Key: o.date_start DESC
  • Sort Method: quicksort Memory: 1349kB
5. 2.266 178.399 ↓ 2,429.0 2,429 1

Nested Loop (cost=3,128.95..3,289.15 rows=1 width=367) (actual time=53.743..178.399 rows=2,429 loops=1)

6. 0.203 157.097 ↓ 4,759.0 4,759 1

Nested Loop (cost=3,128.52..3,281.61 rows=1 width=313) (actual time=53.645..157.097 rows=4,759 loops=1)

7. 3.511 132.329 ↓ 4,913.0 4,913 1

Nested Loop (cost=3,128.09..3,273.30 rows=1 width=222) (actual time=53.572..132.329 rows=4,913 loops=1)

8. 0.631 114.079 ↓ 4,913.0 4,913 1

Nested Loop (cost=3,127.67..3,267.12 rows=1 width=175) (actual time=53.528..114.079 rows=4,913 loops=1)

9. 0.003 0.154 ↑ 1.0 1 1

Nested Loop (cost=0.84..16.89 rows=1 width=3,054) (actual time=0.106..0.154 rows=1 loops=1)

10. 0.088 0.088 ↓ 3.0 3 1

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

  • Index Cond: ((name)::text = 'Bradgate Park'::text)
  • Filter: (NOT deleted)
  • Rows Removed by Filter: 1
11. 0.063 0.063 ↓ 0.0 0 3

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

  • Index Cond: (location_id = l.id)
  • Filter: ((NOT deleted) AND (website_id = 8))
  • Rows Removed by Filter: 1
12. 60.679 113.294 ↓ 4,913.0 4,913 1

Bitmap Heap Scan on cache_occurrences_functional o (cost=3,126.83..3,250.22 rows=1 width=161) (actual time=53.418..113.294 rows=4,913 loops=1)

  • Recheck Cond: ((public_geom && l.boundary_geom) AND (website_id = 8))
  • Filter: ((record_status <> 'R'::bpchar) AND (survey_id = 15) AND (record_status = 'V'::bpchar) AND _st_intersects(public_geom, l.boundary_geom))
  • Rows Removed by Filter: 261
  • Heap Blocks: exact=3627
13. 1.678 52.615 ↓ 0.0 0 1

BitmapAnd (cost=3,126.83..3,126.83 rows=29 width=0) (actual time=52.615..52.615 rows=0 loops=1)

14. 4.130 4.130 ↓ 7.1 8,818 1

Bitmap Index Scan on ix_cache_occurrences_functional_public_geom (cost=0.00..169.74 rows=1,243 width=0) (actual time=4.129..4.130 rows=8,818 loops=1)

  • Index Cond: (public_geom && l.boundary_geom)
15. 46.807 46.807 ↓ 1.1 306,593 1

Bitmap Index Scan on ix_cache_occurrences_functional_location_ids (cost=0.00..2,956.07 rows=291,743 width=0) (actual time=46.807..46.807 rows=306,593 loops=1)

  • Index Cond: (website_id = 8)
16. 14.739 14.739 ↑ 1.0 1 4,913

Index Scan using pk_cache_taxa_taxon_lists on cache_taxa_taxon_lists cttl (cost=0.42..6.18 rows=1 width=55) (actual time=0.003..0.003 rows=1 loops=4,913)

  • Index Cond: (id = o.taxa_taxon_list_id)
17. 24.565 24.565 ↑ 1.0 1 4,913

Index Scan using pk_cache_samples_nonfunctional on cache_samples_nonfunctional snf (cost=0.43..8.31 rows=1 width=99) (actual time=0.005..0.005 rows=1 loops=4,913)

  • Index Cond: (id = o.sample_id)
  • Filter: (length((public_entered_sref)::text) >= 8)
  • Rows Removed by Filter: 0
18. 19.036 19.036 ↑ 1.0 1 4,759

Index Scan using fki_occurrence_images_occurrence on occurrence_media oi (cost=0.43..7.53 rows=1 width=80) (actual time=0.003..0.004 rows=1 loops=4,759)

  • Index Cond: (occurrence_id = o.id)
  • Filter: ((path IS NOT NULL) AND ((external_details)::text = 'Copied to Drupal'::text))
  • Rows Removed by Filter: 0