explain.depesz.com

PostgreSQL's explain analyze made readable

Result: O2Rt

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.010 7,063.028 ↓ 8.0 8 1

Limit (cost=3,284.20..3,284.21 rows=1 width=401) (actual time=7,063.018..7,063.028 rows=8 loops=1)

2. 1.023 7,063.018 ↓ 8.0 8 1

Sort (cost=3,284.20..3,284.21 rows=1 width=401) (actual time=7,063.017..7,063.018 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. 6.973 7,061.995 ↓ 2,429.0 2,429 1

Subquery Scan on vw_occurrences_by_location (cost=3,284.16..3,284.19 rows=1 width=401) (actual time=7,054.974..7,061.995 rows=2,429 loops=1)

4. 4.428 7,055.022 ↓ 2,429.0 2,429 1

Sort (cost=3,284.16..3,284.17 rows=1 width=367) (actual time=7,054.947..7,055.022 rows=2,429 loops=1)

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

Nested Loop Left Join (cost=3,109.39..3,284.15 rows=1 width=367) (actual time=1,028.000..7,050.594 rows=2,429 loops=1)

6. 1.428 1,956.883 ↓ 2,429.0 2,429 1

Nested Loop (cost=3,108.95..3,269.02 rows=1 width=309) (actual time=1,017.673..1,956.883 rows=2,429 loops=1)

7. 1.958 1,922.142 ↓ 4,759.0 4,759 1

Nested Loop (cost=3,108.52..3,261.48 rows=1 width=237) (actual time=1,017.631..1,922.142 rows=4,759 loops=1)

8. 5.977 1,168.495 ↓ 4,913.0 4,913 1

Nested Loop (cost=3,108.09..3,253.17 rows=1 width=222) (actual time=1,017.575..1,168.495 rows=4,913 loops=1)

9. 1.186 1,133.040 ↓ 4,913.0 4,913 1

Nested Loop (cost=3,107.67..3,247.12 rows=1 width=175) (actual time=1,017.536..1,133.040 rows=4,913 loops=1)

10. 0.003 10.184 ↑ 1.0 1 1

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

11. 10.103 10.103 ↓ 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=10.017..10.103 rows=3 loops=1)

  • Index Cond: ((name)::text = 'Bradgate Park'::text)
  • Filter: (NOT deleted)
  • Rows Removed by Filter: 1
12. 0.078 0.078 ↓ 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.025..0.026 rows=0 loops=3)

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

Bitmap Heap Scan on cache_occurrences_functional o (cost=3,106.83..3,230.22 rows=1 width=161) (actual time=1,007.448..1,121.670 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
14. 1.534 1,006.628 ↓ 0.0 0 1

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

15. 958.324 958.324 ↓ 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=958.324..958.324 rows=8,818 loops=1)

  • Index Cond: (public_geom && l.boundary_geom)
16. 46.770 46.770 ↓ 1.1 306,584 1

Bitmap Index Scan on ix_cache_occurrences_functional_location_ids (cost=0.00..2,936.07 rows=291,743 width=0) (actual time=46.770..46.770 rows=306,584 loops=1)

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

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

  • Index Cond: (id = o.taxa_taxon_list_id)
18. 751.689 751.689 ↑ 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=19) (actual time=0.153..0.153 rows=1 loops=4,913)

  • Index Cond: (id = o.sample_id)
  • Filter: (length((public_entered_sref)::text) >= 8)
  • Rows Removed by Filter: 0
19. 33.313 33.313 ↑ 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.006..0.007 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
20. 5,088.755 5,088.755 ↑ 4.0 1 2,429

Index Scan using fki_sample_attribute_values_sample on sample_attribute_values sav (cost=0.44..15.09 rows=4 width=16) (actual time=2.063..2.095 rows=1 loops=2,429)

  • Index Cond: (sample_id = o.sample_id)
  • Filter: ((NOT deleted) AND (sample_attribute_id = 22))
  • Rows Removed by Filter: 2