explain.depesz.com

PostgreSQL's explain analyze made readable

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

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 119.374 2,687.532 ↓ 1.4 10,000 1

Sort (cost=231,321.64..231,339.62 rows=7,190 width=1,185) (actual time=2,679.696..2,687.532 rows=10,000 loops=1)

  • Sort Key: o.updated_on
  • Sort Method: external merge Disk: 6016kB
2. 1,338.284 2,568.158 ↓ 1.4 10,000 1

Nested Loop Left Join (cost=35,905.04..230,861.06 rows=7,190 width=1,185) (actual time=474.872..2,568.158 rows=10,000 loops=1)

3. 13.010 1,219.874 ↓ 1.4 10,000 1

Nested Loop Left Join (cost=35,904.62..210,108.99 rows=7,190 width=860) (actual time=473.753..1,219.874 rows=10,000 loops=1)

4. 22.651 1,186.864 ↓ 1.4 10,000 1

Nested Loop (cost=35,904.18..156,619.21 rows=7,190 width=856) (actual time=473.749..1,186.864 rows=10,000 loops=1)

5. 22.411 984.213 ↓ 1.4 10,000 1

Nested Loop (cost=35,903.75..103,036.24 rows=7,190 width=583) (actual time=473.737..984.213 rows=10,000 loops=1)

  • Join Filter: (o.id = onf.id)
6. 42.069 741.802 ↓ 1.4 10,000 1

Hash Left Join (cost=35,903.32..98,904.91 rows=7,190 width=352) (actual time=473.719..741.802 rows=10,000 loops=1)

  • Hash Cond: (o.location_id = l.id)
7. 69.606 509.274 ↓ 1.4 10,000 1

Hash Join (cost=21,036.25..82,275.96 rows=7,190 width=338) (actual time=281.747..509.274 rows=10,000 loops=1)

  • Hash Cond: (o.taxa_taxon_list_id = cttl.id)
8. 22.634 158.824 ↓ 1.4 10,000 1

Nested Loop (cost=0.43..59,074.28 rows=7,190 width=334) (actual time=0.136..158.824 rows=10,000 loops=1)

9. 6.190 6.190 ↓ 1.4 10,000 1

Seq Scan on occslist o (cost=0.00..603.06 rows=7,406 width=326) (actual time=0.019..6.190 rows=10,000 loops=1)

10. 130.000 130.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.013..0.013 rows=1 loops=10,000)

  • Index Cond: (id = o.id)
  • Filter: (NOT deleted)
11. 67.808 280.844 ↑ 1.0 310,194 1

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

  • Buckets: 262144 Batches: 2 Memory Usage: 8731kB
12. 213.036 213.036 ↑ 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.039..213.036 rows=310,194 loops=1)

13. 48.927 190.459 ↑ 1.0 183,551 1

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

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

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

  • Filter: (NOT deleted)
  • Rows Removed by Filter: 8805
15. 220.000 220.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.022..0.022 rows=1 loops=10,000)

  • Index Cond: (id = occ.id)
16. 180.000 180.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.018..0.018 rows=1 loops=10,000)

  • Index Cond: (id = o.sample_id)
17. 20.000 20.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.002..0.002 rows=0 loops=10,000)

  • Index Cond: (id = o.parent_sample_id)
  • Filter: (NOT deleted)
  • Rows Removed by Filter: 0
18. 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)