explain.depesz.com

PostgreSQL's explain analyze made readable

Result: PXo1

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 319.458 56,582.139 ↓ 1.4 1,000 1

Nested Loop Left Join (cost=2.27..20,801.05 rows=723 width=1,668) (actual time=1.538..56,582.139 rows=1,000 loops=1)

2. 2.722 53,912.549 ↓ 1.4 1,000 1

Nested Loop (cost=1.85..6,169.80 rows=723 width=1,144) (actual time=0.129..53,912.549 rows=1,000 loops=1)

3. 3.405 53,894.827 ↓ 1.4 1,000 1

Nested Loop (cost=1.43..4,389.70 rows=723 width=1,028) (actual time=0.105..53,894.827 rows=1,000 loops=1)

4. 3.166 53,880.422 ↓ 1.4 1,000 1

Nested Loop (cost=1.00..2,476.23 rows=723 width=752) (actual time=0.084..53,880.422 rows=1,000 loops=1)

5. 4.475 23,688.256 ↓ 1.3 1,000 1

Nested Loop (cost=0.56..2,119.13 rows=742 width=750) (actual time=0.050..23,688.256 rows=1,000 loops=1)

6. 0.781 0.781 ↓ 1.3 1,000 1

Seq Scan on filtered_occurrences o (cost=0.00..60.42 rows=742 width=406) (actual time=0.008..0.781 rows=1,000 loops=1)

7. 23,683.000 23,683.000 ↑ 1.0 1 1,000

Index Scan using pk_cache_occurrences_nonfunctional on cache_occurrences_nonfunctional onf (cost=0.56..2.77 rows=1 width=344) (actual time=23.683..23.683 rows=1 loops=1,000)

  • Index Cond: (id = o.id)
8. 30,189.000 30,189.000 ↑ 1.0 1 1,000

Index Scan using pk_occurrences on occurrences occ (cost=0.44..0.48 rows=1 width=10) (actual time=30.189..30.189 rows=1 loops=1,000)

  • Index Cond: (id = onf.id)
  • Filter: (NOT deleted)
9. 11.000 11.000 ↑ 1.0 1 1,000

Index Scan using pk_cache_samples_nonfunctional on cache_samples_nonfunctional snf (cost=0.43..2.65 rows=1 width=280) (actual time=0.011..0.011 rows=1 loops=1,000)

  • Index Cond: (id = o.sample_id)
10. 15.000 15.000 ↑ 1.0 1 1,000

Index Scan using pk_cache_taxa_taxon_lists on cache_taxa_taxon_lists cttl (cost=0.42..2.46 rows=1 width=120) (actual time=0.015..0.015 rows=1 loops=1,000)

  • Index Cond: (id = o.taxa_taxon_list_id)
11. 556.000 556.000 ↓ 0.0 0 1,000

Index Scan using pk_locations on locations l (cost=0.42..2.22 rows=1 width=22) (actual time=0.556..0.556 rows=0 loops=1,000)

  • Index Cond: (id = o.location_id)
  • Filter: (NOT deleted)
12.          

SubPlan (for Nested Loop Left Join)

13. 0.976 1,794.132 ↑ 1.0 1 244

Aggregate (cost=8.93..8.94 rows=1 width=32) (actual time=7.353..7.353 rows=1 loops=244)

14. 0.474 1,793.156 ↑ 2.0 1 244

Hash Left Join (cost=1.89..8.89 rows=2 width=128) (actual time=7.348..7.349 rows=1 loops=244)

  • Hash Cond: (om.licence_id = l_1.id)
15. 0.808 1,792.668 ↑ 2.0 1 244

Nested Loop Left Join (cost=0.71..7.71 rows=2 width=100) (actual time=7.346..7.347 rows=1 loops=244)

16. 1,790.960 1,790.960 ↑ 2.0 1 244

Index Scan using fki_occurrence_images_occurrence on occurrence_media om (cost=0.43..2.70 rows=2 width=84) (actual time=7.340..7.340 rows=1 loops=244)

  • Index Cond: (occurrence_id = o.id)
  • Filter: (NOT deleted)
17. 0.900 0.900 ↑ 1.0 1 300

Index Scan using pk_cache_termlists_terms on cache_termlists_terms t (cost=0.29..2.50 rows=1 width=24) (actual time=0.003..0.003 rows=1 loops=300)

  • Index Cond: (id = om.media_type_id)
18. 0.006 0.014 ↓ 1.1 9 1

Hash (cost=1.08..1.08 rows=8 width=36) (actual time=0.014..0.014 rows=9 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
19. 0.008 0.008 ↓ 1.1 9 1

Seq Scan on licences l_1 (cost=0.00..1.08 rows=8 width=36) (actual time=0.006..0.008 rows=9 loops=1)