explain.depesz.com

PostgreSQL's explain analyze made readable

Result: VH2U

Settings
# exclusive inclusive rows x rows loops node
1. 1.487 3,150.871 ↓ 1.3 10,000 1

Limit (cost=108,534.87..244,260.63 rows=7,528 width=1,668) (actual time=836.649..3,150.871 rows=10,000 loops=1)

2. 2,276.707 3,149.384 ↓ 1.3 10,000 1

Result (cost=108,534.87..244,260.63 rows=7,528 width=1,668) (actual time=836.648..3,149.384 rows=10,000 loops=1)

3. 22.984 832.609 ↓ 1.3 10,000 1

Sort (cost=108,534.87..108,553.69 rows=7,528 width=1,494) (actual time=831.116..832.609 rows=10,000 loops=1)

  • Sort Key: o.tracking
  • Sort Method: quicksort Memory: 11,214kB
4. 50.312 809.625 ↓ 1.3 10,000 1

Nested Loop Left Join (cost=4.41..108,050.14 rows=7,528 width=1,494) (actual time=11.367..809.625 rows=10,000 loops=1)

5. 0.415 749.313 ↓ 1.3 10,000 1

Nested Loop Left Join (cost=3.99..104,234.53 rows=7,528 width=1,252) (actual time=11.119..749.313 rows=10,000 loops=1)

6. 4.060 738.898 ↓ 1.3 10,000 1

Nested Loop Left Join (cost=3.55..100,491.10 rows=7,528 width=1,173) (actual time=11.113..738.898 rows=10,000 loops=1)

7. 0.911 724.838 ↓ 1.3 10,000 1

Nested Loop Left Join (cost=3.12..81,592.47 rows=7,528 width=1,165) (actual time=11.103..724.838 rows=10,000 loops=1)

8. 8.903 703.927 ↓ 1.3 10,000 1

Nested Loop Left Join (cost=2.70..71,014.69 rows=7,528 width=1,147) (actual time=11.098..703.927 rows=10,000 loops=1)

9. 5.223 665.024 ↓ 1.3 10,000 1

Nested Loop (cost=2.27..58,455.05 rows=7,528 width=1,047) (actual time=11.029..665.024 rows=10,000 loops=1)

10. 5.889 619.801 ↓ 1.3 10,000 1

Nested Loop (cost=1.85..54,895.58 rows=7,528 width=1,035) (actual time=10.965..619.801 rows=10,000 loops=1)

11. 4.054 573.912 ↓ 1.3 10,000 1

Nested Loop (cost=1.43..44,767.24 rows=7,528 width=1,023) (actual time=10.912..573.912 rows=10,000 loops=1)

12. 2.788 419.858 ↓ 1.3 10,000 1

Nested Loop (cost=1.00..25,391.78 rows=7,528 width=748) (actual time=10.861..419.858 rows=10,000 loops=1)

  • Join Filter: (o.id = onf.id)
13. 14.361 217.070 ↓ 1.3 10,000 1

Nested Loop (cost=0.43..20,595.40 rows=7,528 width=416) (actual time=10.819..217.070 rows=10,000 loops=1)

14. 2.709 2.709 ↓ 1.3 10,000 1

Seq Scan on filtered_occurrences o (cost=0.00..629.28 rows=7,728 width=406) (actual time=0.012..2.709 rows=10,000 loops=1)

15. 200.000 200.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=0.020..0.020 rows=1 loops=10,000)

  • Index Cond: (id = o.id)
  • Filter: (NOT deleted)
16. 200.000 200.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=0.020..0.020 rows=1 loops=10,000)

  • Index Cond: (id = occ.id)
17. 150.000 150.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=0.015..0.015 rows=1 loops=10,000)

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

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

  • Index Cond: (id = o.taxa_taxon_list_id)
19. 40.000 40.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.004..0.004 rows=1 loops=10,000)

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

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

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

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

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

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

  • Index Cond: (id = o.parent_sample_id)
  • Filter: (NOT deleted)
23. 10.000 10.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.001..0.001 rows=0 loops=10,000)

  • Index Cond: (id = sp.id)
24. 10.000 10.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.001..0.001 rows=0 loops=10,000)

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

SubPlan (for Result)

26. 4.293 40.068 ↑ 1.0 1 1,431

Aggregate (cost=8.93..8.94 rows=1 width=32) (actual time=0.028..0.028 rows=1 loops=1,431)

27. 1.417 35.775 ↑ 2.0 1 1,431

Hash Left Join (cost=1.89..8.89 rows=2 width=128) (actual time=0.024..0.025 rows=1 loops=1,431)

  • Hash Cond: (om.licence_id = l_1.id)
28. 0.595 34.344 ↑ 2.0 1 1,431

Nested Loop Left Join (cost=0.71..7.71 rows=2 width=100) (actual time=0.023..0.024 rows=1 loops=1,431)

29. 30.051 30.051 ↑ 2.0 1 1,431

Index Scan using fki_occurrence_images_occurrence on occurrence_media om (cost=0.43..2.70 rows=2 width=84) (actual time=0.020..0.021 rows=1 loops=1,431)

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

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.002..0.002 rows=1 loops=1,849)

  • Index Cond: (id = om.media_type_id)
31. 0.005 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
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.007..0.009 rows=9 loops=1)