explain.depesz.com

PostgreSQL's explain analyze made readable

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

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 72.089 2,354.190 ↓ 1.0 10,000 1

Sort (cost=296,939.27..296,963.54 rows=9,708 width=1,185) (actual time=2,346.699..2,354.190 rows=10,000 loops=1)

  • Sort Key: o.updated_on
  • Sort Method: external merge Disk: 6176kB
2. 1,197.192 2,282.101 ↓ 1.0 10,000 1

Nested Loop Left Join (cost=35,905.04..291,249.36 rows=9,708 width=1,185) (actual time=432.179..2,282.101 rows=10,000 loops=1)

3. 13.597 1,074.909 ↓ 1.0 10,000 1

Nested Loop Left Join (cost=35,904.62..263,229.74 rows=9,708 width=860) (actual time=431.116..1,074.909 rows=10,000 loops=1)

4. 17.844 1,031.312 ↓ 1.0 10,000 1

Nested Loop (cost=35,904.18..193,561.24 rows=9,708 width=856) (actual time=431.111..1,031.312 rows=10,000 loops=1)

5. 27.473 913.468 ↓ 1.0 10,000 1

Nested Loop (cost=35,903.75..123,749.07 rows=9,708 width=583) (actual time=431.050..913.468 rows=10,000 loops=1)

  • Join Filter: (o.id = onf.id)
6. 41.607 715.995 ↓ 1.0 10,000 1

Hash Left Join (cost=35,903.32..118,170.85 rows=9,708 width=352) (actual time=430.984..715.995 rows=10,000 loops=1)

  • Hash Cond: (o.location_id = l.id)
7. 66.786 485.968 ↓ 1.0 10,000 1

Hash Join (cost=21,036.25..101,307.30 rows=9,708 width=338) (actual time=241.826..485.968 rows=10,000 loops=1)

  • Hash Cond: (o.taxa_taxon_list_id = cttl.id)
8. 22.934 178.723 ↓ 1.0 10,000 1

Nested Loop (cost=0.43..77,877.00 rows=9,708 width=334) (actual time=0.107..178.723 rows=10,000 loops=1)

9. 5.789 5.789 ↑ 1.0 10,000 1

Seq Scan on occslist o (cost=0.00..628.00 rows=10,000 width=326) (actual time=0.025..5.789 rows=10,000 loops=1)

10. 150.000 150.000 ↑ 1.0 1 10,000

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

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

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

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

13. 52.695 188.420 ↑ 1.0 183,551 1

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

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

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

  • Filter: (NOT deleted)
  • Rows Removed by Filter: 8805
15. 170.000 170.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.017..0.017 rows=1 loops=10,000)

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

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

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

Index Scan using pk_samples on samples sp (cost=0.43..7.18 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
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)