explain.depesz.com

PostgreSQL's explain analyze made readable

Result: yxWi

Settings
# exclusive inclusive rows x rows loops node
1. 1.364 7,560.689 ↓ 1.4 10,000 1

Limit (cost=328,083.97..578,525.65 rows=7,006 width=1,625) (actual time=4,163.012..7,560.689 rows=10,000 loops=1)

2. 3,080.236 7,559.325 ↓ 1.4 10,000 1

Result (cost=328,083.97..578,525.65 rows=7,006 width=1,625) (actual time=4,163.011..7,559.325 rows=10,000 loops=1)

3. 51.632 4,170.209 ↓ 1.4 10,000 1

Sort (cost=328,083.97..328,101.49 rows=7,006 width=1,451) (actual time=4,158.717..4,170.209 rows=10,000 loops=1)

  • Sort Key: o.tracking
  • Sort Method: external merge Disk: 7,688kB
4. 56.740 4,118.577 ↓ 1.4 10,000 1

Nested Loop Left Join (cost=39,651.05..327,636.49 rows=7,006 width=1,451) (actual time=385.968..4,118.577 rows=10,000 loops=1)

5. 9.702 4,031.837 ↓ 1.4 10,000 1

Nested Loop Left Join (cost=39,650.63..309,880.28 rows=7,006 width=1,263) (actual time=385.852..4,031.837 rows=10,000 loops=1)

6. 6.220 3,942.135 ↓ 1.4 10,000 1

Nested Loop Left Join (cost=39,650.20..290,802.82 rows=7,006 width=1,185) (actual time=385.841..3,942.135 rows=10,000 loops=1)

7. 7.237 3,835.915 ↓ 1.4 10,000 1

Nested Loop Left Join (cost=39,649.77..235,698.11 rows=7,006 width=1,177) (actual time=385.809..3,835.915 rows=10,000 loops=1)

8. 11.066 3,808.678 ↓ 1.4 10,000 1

Nested Loop (cost=39,649.35..199,589.11 rows=7,006 width=1,077) (actual time=385.781..3,808.678 rows=10,000 loops=1)

9. 4.130 3,757.612 ↓ 1.4 10,000 1

Nested Loop (cost=39,648.92..181,999.04 rows=7,006 width=1,064) (actual time=385.751..3,757.612 rows=10,000 loops=1)

10. 7.131 3,283.482 ↓ 1.4 10,000 1

Nested Loop (cost=39,648.49..125,027.61 rows=7,006 width=799) (actual time=385.711..3,283.482 rows=10,000 loops=1)

  • Join Filter: (o.id = onf.id)
11. 37.791 576.351 ↓ 1.4 10,000 1

Hash Left Join (cost=39,648.06..104,691.93 rows=7,006 width=446) (actual time=385.652..576.351 rows=10,000 loops=1)

  • Hash Cond: (o.location_id = l.id)
12. 54.046 306.056 ↓ 1.4 10,000 1

Hash Join (cost=18,769.81..81,755.30 rows=7,006 width=428) (actual time=151.974..306.056 rows=10,000 loops=1)

  • Hash Cond: (o.taxa_taxon_list_id = ttl.id)
13. 10.053 101.757 ↓ 1.4 10,000 1

Nested Loop (cost=0.43..60,019.53 rows=7,006 width=416) (actual time=0.062..101.757 rows=10,000 loops=1)

14. 1.704 1.704 ↓ 1.4 10,000 1

Seq Scan on filtered_occurrences o (cost=0.00..591.66 rows=7,266 width=406) (actual time=0.012..1.704 rows=10,000 loops=1)

15. 90.000 90.000 ↑ 1.0 1 10,000

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

  • Index Cond: (id = o.id)
  • Filter: (NOT deleted)
16. 59.550 150.253 ↑ 1.0 446,015 1

Hash (cost=10,961.28..10,961.28 rows=449,128 width=16) (actual time=150.253..150.253 rows=446,015 loops=1)

  • Buckets: 262,144 Batches: 4 Memory Usage: 7,277kB
17. 90.703 90.703 ↑ 1.0 446,015 1

Seq Scan on taxa_taxon_lists ttl (cost=0.00..10,961.28 rows=449,128 width=16) (actual time=1.743..90.703 rows=446,015 loops=1)

18. 46.828 232.504 ↓ 1.0 214,983 1

Hash (cost=16,931.94..16,931.94 rows=214,904 width=22) (actual time=232.504..232.504 rows=214,983 loops=1)

  • Buckets: 262,144 Batches: 2 Memory Usage: 7,646kB
19. 185.676 185.676 ↓ 1.0 214,983 1

Seq Scan on locations l (cost=0.00..16,931.94 rows=214,904 width=22) (actual time=0.028..185.676 rows=214,983 loops=1)

  • Filter: (NOT deleted)
  • Rows Removed by Filter: 15,484
20. 2,700.000 2,700.000 ↑ 1.0 1 10,000

Index Scan using pk_cache_occurrences_nonfunctional on cache_occurrences_nonfunctional onf (cost=0.43..2.89 rows=1 width=361) (actual time=0.270..0.270 rows=1 loops=10,000)

  • Index Cond: (id = occ.id)
21. 470.000 470.000 ↑ 1.0 1 10,000

Index Scan using pk_cache_samples_nonfunctional on cache_samples_nonfunctional snf (cost=0.43..8.13 rows=1 width=269) (actual time=0.047..0.047 rows=1 loops=10,000)

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

Index Scan using pk_taxa on taxa t (cost=0.42..2.51 rows=1 width=21) (actual time=0.004..0.004 rows=1 loops=10,000)

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

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

  • Index Cond: (id = o.taxa_taxon_list_id)
24. 100.000 100.000 ↑ 1.0 1 10,000

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

  • Index Cond: (id = o.parent_sample_id)
  • Filter: (NOT deleted)
25. 80.000 80.000 ↑ 1.0 1 10,000

Index Scan using pk_cache_samples_nonfunctional on cache_samples_nonfunctional snfp (cost=0.43..2.72 rows=1 width=86) (actual time=0.008..0.008 rows=1 loops=10,000)

  • Index Cond: (id = sp.id)
26. 30.000 30.000 ↑ 1.0 1 10,000

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

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

SubPlan (for Result)

28. 0.495 308.880 ↑ 1.0 1 99

Aggregate (cost=26.62..26.63 rows=1 width=32) (actual time=3.120..3.120 rows=1 loops=99)

29. 0.226 308.385 ↓ 1.5 3 99

Nested Loop Left Join (cost=0.71..26.59 rows=2 width=128) (actual time=3.048..3.115 rows=3 loops=99)

  • Join Filter: (l_1.id = om.licence_id)
  • Rows Removed by Join Filter: 24
30. 0.452 307.890 ↓ 1.5 3 99

Nested Loop Left Join (cost=0.71..25.25 rows=2 width=100) (actual time=3.045..3.110 rows=3 loops=99)

31. 306.900 306.900 ↓ 1.5 3 99

Index Scan using fki_occurrence_images_occurrence on occurrence_media om (cost=0.43..8.64 rows=2 width=84) (actual time=3.036..3.100 rows=3 loops=99)

  • Index Cond: (occurrence_id = o.id)
  • Filter: (NOT deleted)
  • Rows Removed by Filter: 0
32. 0.538 0.538 ↑ 1.0 1 269

Index Scan using pk_cache_termlists_terms on cache_termlists_terms t_1 (cost=0.29..8.30 rows=1 width=24) (actual time=0.002..0.002 rows=1 loops=269)

  • Index Cond: (id = om.media_type_id)
33. 0.260 0.269 ↓ 1.1 9 269

Materialize (cost=0.00..1.12 rows=8 width=36) (actual time=0.000..0.001 rows=9 loops=269)

34. 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)