explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 9rG0 : Optimization for: Optimization for: Elasticsearch updates mode; plan #KB22 (temp table); plan #sarw

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 2.209 2,195.681 ↓ 1.4 10,000 1

Limit (cost=213,998.09..231,597.23 rows=7,176 width=1,185) (actual time=1,244.564..2,195.681 rows=10,000 loops=1)

2. 947.279 2,193.472 ↓ 1.4 10,000 1

Result (cost=213,998.09..231,597.23 rows=7,176 width=1,185) (actual time=1,244.563..2,193.472 rows=10,000 loops=1)

3. 39.520 1,246.193 ↓ 1.4 10,000 1

Sort (cost=213,998.09..214,016.03 rows=7,176 width=1,139) (actual time=1,243.381..1,246.193 rows=10,000 loops=1)

  • Sort Key: o.updated_on
  • Sort Method: quicksort Memory: 6848kB
4. 123.940 1,206.673 ↓ 1.4 10,000 1

Nested Loop Left Join (cost=35,905.04..213,538.51 rows=7,176 width=1,139) (actual time=497.144..1,206.673 rows=10,000 loops=1)

5. 7.814 1,072.733 ↓ 1.4 10,000 1

Nested Loop Left Join (cost=35,904.62..209,816.02 rows=7,176 width=860) (actual time=497.099..1,072.733 rows=10,000 loops=1)

6. 16.532 1,034.919 ↓ 1.4 10,000 1

Nested Loop (cost=35,904.18..156,416.95 rows=7,176 width=856) (actual time=497.089..1,034.919 rows=10,000 loops=1)

7. 21.028 928.387 ↓ 1.4 10,000 1

Nested Loop (cost=35,903.75..102,924.69 rows=7,176 width=583) (actual time=497.058..928.387 rows=10,000 loops=1)

  • Join Filter: (o.id = onf.id)
8. 39.523 757.359 ↓ 1.4 10,000 1

Hash Left Join (cost=35,903.32..98,801.36 rows=7,176 width=352) (actual time=496.992..757.359 rows=10,000 loops=1)

  • Hash Cond: (o.location_id = l.id)
9. 63.830 535.084 ↓ 1.4 10,000 1

Hash Join (cost=21,036.25..82,172.45 rows=7,176 width=338) (actual time=311.013..535.084 rows=10,000 loops=1)

  • Hash Cond: (o.taxa_taxon_list_id = cttl.id)
10. 15.839 161.566 ↓ 1.4 10,000 1

Nested Loop (cost=0.43..58,970.80 rows=7,176 width=334) (actual time=0.121..161.566 rows=10,000 loops=1)

11. 5.727 5.727 ↓ 1.4 10,000 1

Seq Scan on occslist o (cost=0.00..601.92 rows=7,392 width=326) (actual time=0.041..5.727 rows=10,000 loops=1)

12. 140.000 140.000 ↑ 1.0 1 10,000

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

  • Index Cond: (id = o.id)
  • Filter: (NOT deleted)
13. 73.790 309.688 ↑ 1.0 310,194 1

Hash (cost=15,643.14..15,643.14 rows=310,214 width=12) (actual time=309.688..309.688 rows=310,194 loops=1)

  • Buckets: 262144 Batches: 2 Memory Usage: 8731kB
14. 235.898 235.898 ↑ 1.0 310,194 1

Seq Scan on cache_taxa_taxon_lists cttl (cost=0.00..15,643.14 rows=310,214 width=12) (actual time=0.036..235.898 rows=310,194 loops=1)

15. 50.816 182.752 ↑ 1.0 183,551 1

Hash (cost=11,430.18..11,430.18 rows=187,191 width=18) (actual time=182.751..182.752 rows=183,551 loops=1)

  • Buckets: 262144 Batches: 2 Memory Usage: 6544kB
16. 131.936 131.936 ↑ 1.0 183,551 1

Seq Scan on locations l (cost=0.00..11,430.18 rows=187,191 width=18) (actual time=0.040..131.936 rows=183,551 loops=1)

  • Filter: (NOT deleted)
  • Rows Removed by Filter: 8805
17. 150.000 150.000 ↑ 1.0 1 10,000

Index Scan using pk_cache_occurrences_nonfunctional on cache_occurrences_nonfunctional onf (cost=0.43..0.56 rows=1 width=239) (actual time=0.015..0.015 rows=1 loops=10,000)

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

Index Scan using pk_cache_samples_nonfunctional on cache_samples_nonfunctional snf (cost=0.43..7.45 rows=1 width=277) (actual time=0.009..0.009 rows=1 loops=10,000)

  • Index Cond: (id = o.sample_id)
19. 30.000 30.000 ↓ 0.0 0 10,000

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

  • Index Cond: (id = o.parent_sample_id)
  • Filter: (NOT deleted)
  • Rows Removed by Filter: 0
20. 10.000 10.000 ↓ 0.0 0 10,000

Index Scan using pk_locations on locations lp (cost=0.42..0.45 rows=1 width=18) (actual time=0.001..0.001 rows=0 loops=10,000)

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