explain.depesz.com

PostgreSQL's explain analyze made readable

Result: paJ3

Settings
# exclusive inclusive rows x rows loops node
1. 1.652 189,374.081 ↓ 1.3 10,000 1

Limit (cost=114,024.24..257,376.46 rows=7,951 width=1,668) (actual time=174,098.466..189,374.081 rows=10,000 loops=1)

2. 2,222.799 189,372.429 ↓ 1.3 10,000 1

Result (cost=114,024.24..257,376.46 rows=7,951 width=1,668) (actual time=174,098.465..189,372.429 rows=10,000 loops=1)

3. 51.638 174,077.028 ↓ 1.3 10,000 1

Sort (cost=114,024.24..114,044.11 rows=7,951 width=1,494) (actual time=174,074.892..174,077.028 rows=10,000 loops=1)

  • Sort Key: o.tracking
  • Sort Method: quicksort Memory: 12,455kB
4. 175.526 174,025.390 ↓ 1.3 10,000 1

Nested Loop Left Join (cost=4.41..113,509.13 rows=7,951 width=1,494) (actual time=66.837..174,025.390 rows=10,000 loops=1)

5. 14.576 173,819.864 ↓ 1.3 10,000 1

Nested Loop Left Join (cost=3.99..109,479.17 rows=7,951 width=1,252) (actual time=66.792..173,819.864 rows=10,000 loops=1)

6. 16.570 171,825.288 ↓ 1.3 10,000 1

Nested Loop Left Join (cost=3.55..105,526.13 rows=7,951 width=1,173) (actual time=66.787..171,825.288 rows=10,000 loops=1)

7. 18.947 171,418.718 ↓ 1.3 10,000 1

Nested Loop Left Join (cost=3.12..85,619.11 rows=7,951 width=1,165) (actual time=66.781..171,418.718 rows=10,000 loops=1)

8. 18.013 170,959.771 ↓ 1.3 10,000 1

Nested Loop Left Join (cost=2.70..74,567.81 rows=7,951 width=1,147) (actual time=66.775..170,959.771 rows=10,000 loops=1)

9. 15.666 170,801.758 ↓ 1.3 10,000 1

Nested Loop (cost=2.27..61,475.66 rows=7,951 width=1,047) (actual time=66.748..170,801.758 rows=10,000 loops=1)

10. 29.469 167,196.092 ↓ 1.3 10,000 1

Nested Loop (cost=1.85..57,716.18 rows=7,951 width=1,035) (actual time=66.688..167,196.092 rows=10,000 loops=1)

11. 25.758 165,426.623 ↓ 1.3 10,000 1

Nested Loop (cost=1.43..47,220.43 rows=7,951 width=1,023) (actual time=66.603..165,426.623 rows=10,000 loops=1)

12. 26.608 115,470.865 ↓ 1.3 10,000 1

Nested Loop (cost=1.00..26,789.42 rows=7,951 width=748) (actual time=51.978..115,470.865 rows=10,000 loops=1)

  • Join Filter: (o.id = onf.id)
13. 36.054 58,574.257 ↓ 1.3 10,000 1

Nested Loop (cost=0.43..21,723.63 rows=7,951 width=416) (actual time=27.064..58,574.257 rows=10,000 loops=1)

14. 18.203 18.203 ↓ 1.2 10,000 1

Seq Scan on filtered_occurrences o (cost=0.00..664.62 rows=8,162 width=406) (actual time=0.037..18.203 rows=10,000 loops=1)

15. 58,520.000 58,520.000 ↑ 1.0 1 10,000

Index Scan using pk_occurrences on occurrences occ (cost=0.43..2.58 rows=1 width=10) (actual time=5.852..5.852 rows=1 loops=10,000)

  • Index Cond: (id = o.id)
  • Filter: (NOT deleted)
16. 56,870.000 56,870.000 ↑ 1.0 1 10,000

Index Scan using pk_cache_occurrences_nonfunctional on cache_occurrences_nonfunctional onf (cost=0.56..0.62 rows=1 width=340) (actual time=5.687..5.687 rows=1 loops=10,000)

  • Index Cond: (id = occ.id)
17. 49,930.000 49,930.000 ↑ 1.0 1 10,000

Index Scan using pk_cache_samples_nonfunctional on cache_samples_nonfunctional snf (cost=0.43..2.57 rows=1 width=279) (actual time=4.993..4.993 rows=1 loops=10,000)

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

Index Scan using pk_taxa_taxon_lists on taxa_taxon_lists ttl (cost=0.42..1.32 rows=1 width=16) (actual time=0.174..0.174 rows=1 loops=10,000)

  • Index Cond: (id = o.taxa_taxon_list_id)
19. 3,590.000 3,590.000 ↑ 1.0 1 10,000

Index Scan using pk_taxa on taxa t (cost=0.42..0.47 rows=1 width=20) (actual time=0.359..0.359 rows=1 loops=10,000)

  • Index Cond: (id = ttl.taxon_id)
20. 140.000 140.000 ↑ 1.0 1 10,000

Index Scan using pk_cache_taxa_taxon_lists on cache_taxa_taxon_lists cttl (cost=0.42..1.65 rows=1 width=104) (actual time=0.014..0.014 rows=1 loops=10,000)

  • Index Cond: (id = o.taxa_taxon_list_id)
21. 440.000 440.000 ↓ 0.0 0 10,000

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

  • Index Cond: (id = o.location_id)
  • Filter: (NOT deleted)
22. 390.000 390.000 ↓ 0.0 0 10,000

Index Scan using pk_samples on samples sp (cost=0.43..2.50 rows=1 width=8) (actual time=0.039..0.039 rows=0 loops=10,000)

  • Index Cond: (id = o.parent_sample_id)
  • Filter: (NOT deleted)
23. 1,980.000 1,980.000 ↓ 0.0 0 10,000

Index Scan using pk_cache_samples_nonfunctional on cache_samples_nonfunctional snfp (cost=0.43..0.50 rows=1 width=87) (actual time=0.198..0.198 rows=0 loops=10,000)

  • Index Cond: (id = sp.id)
24. 30.000 30.000 ↓ 0.0 0 10,000

Index Scan using pk_locations on locations lp (cost=0.42..0.44 rows=1 width=22) (actual time=0.003..0.003 rows=0 loops=10,000)

  • Index Cond: (id = sp.location_id)
  • Filter: (NOT deleted)
25.          

SubPlan (for Result)

26. 8.812 13,072.602 ↑ 1.0 1 2,203

Aggregate (cost=8.93..8.94 rows=1 width=32) (actual time=5.933..5.934 rows=1 loops=2,203)

27. 2.191 13,063.790 ↑ 1.0 2 2,203

Hash Left Join (cost=1.89..8.89 rows=2 width=128) (actual time=5.827..5.930 rows=2 loops=2,203)

  • Hash Cond: (om.licence_id = l_1.id)
28. 9.054 13,061.587 ↑ 1.0 2 2,203

Nested Loop Left Join (cost=0.71..7.71 rows=2 width=100) (actual time=5.826..5.929 rows=2 loops=2,203)

29. 13,041.760 13,041.760 ↑ 1.0 2 2,203

Index Scan using fki_occurrence_images_occurrence on occurrence_media om (cost=0.43..2.70 rows=2 width=84) (actual time=5.819..5.920 rows=2 loops=2,203)

  • Index Cond: (occurrence_id = o.id)
  • Filter: (NOT deleted)
  • Rows Removed by Filter: 0
30. 10.773 10.773 ↑ 1.0 1 3,591

Index Scan using pk_cache_termlists_terms on cache_termlists_terms t_1 (cost=0.29..2.50 rows=1 width=24) (actual time=0.003..0.003 rows=1 loops=3,591)

  • Index Cond: (id = om.media_type_id)
31. 0.003 0.012 ↓ 1.1 9 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
32. 0.009 0.009 ↓ 1.1 9 1

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