explain.depesz.com

PostgreSQL's explain analyze made readable

Result: KB22 : Elasticsearch updates mode

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 2.683 635,584.990 ↑ 1.0 10,000 1

Limit (cost=3,655,177.08..3,679,702.08 rows=10,000 width=1,051) (actual time=634,531.399..635,584.990 rows=10,000 loops=1)

2. 1,047.823 635,582.307 ↑ 63.0 10,000 1

Result (cost=3,655,177.08..5,201,237.98 rows=630,402 width=1,051) (actual time=634,531.397..635,582.307 rows=10,000 loops=1)

3. 3,606.809 634,534.484 ↑ 63.0 10,000 1

Sort (cost=3,655,177.08..3,656,753.08 rows=630,402 width=1,080) (actual time=634,530.175..634,534.484 rows=10,000 loops=1)

  • Sort Key: (to_char(GREATEST(o.updated_on, cttl.cache_updated_on), 'yyyy-mm-dd HH24:MI:SS.MS'::text)), o.id
  • Sort Method: top-N heapsort Memory: 8938kB
4. 10,421.121 630,927.675 ↓ 1.1 694,567 1

Nested Loop (cost=1,542,293.51..2,999,751.73 rows=630,402 width=1,080) (actual time=205,600.514..630,927.675 rows=694,567 loops=1)

  • Join Filter: (o.id = onf.id)
5. 32,513.287 282,946.992 ↓ 1.1 694,567 1

Hash Join (cost=1,542,293.07..2,358,150.54 rows=646,973 width=580) (actual time=205,577.317..282,946.992 rows=694,567 loops=1)

  • Hash Cond: (o.sample_id = snf.id)
6. 708.821 244,654.171 ↓ 1.1 694,567 1

Hash Left Join (cost=1,136,525.70..1,727,880.86 rows=646,973 width=307) (actual time=199,081.931..244,654.171 rows=694,567 loops=1)

  • Hash Cond: (sp.location_id = lp.id)
7. 778.734 243,767.064 ↓ 1.1 694,567 1

Hash Left Join (cost=1,121,658.63..1,659,672.46 rows=646,973 width=293) (actual time=198,902.234..243,767.064 rows=694,567 loops=1)

  • Hash Cond: (o.location_id = l.id)
8. 1,787.381 241,646.802 ↓ 1.1 694,567 1

Hash Left Join (cost=1,106,791.56..1,593,992.08 rows=646,973 width=279) (actual time=197,558.895..241,646.802 rows=694,567 loops=1)

  • Hash Cond: (o.parent_sample_id = sp.id)
9. 1,160.046 228,074.220 ↓ 1.1 694,567 1

Hash Join (cost=848,356.12..1,267,959.33 rows=646,973 width=275) (actual time=185,761.338..228,074.220 rows=694,567 loops=1)

  • Hash Cond: (o.taxa_taxon_list_id = cttl.id)
10. 5,078.489 226,615.365 ↓ 1.1 694,567 1

Hash Join (cost=827,320.30..1,196,956.19 rows=646,973 width=271) (actual time=185,460.880..226,615.365 rows=694,567 loops=1)

  • Hash Cond: (occ.id = o.id)
11. 36,105.717 36,105.717 ↑ 1.0 8,708,805 1

Seq Scan on occurrences occ (cost=0.00..252,954.70 rows=8,934,162 width=8) (actual time=0.023..36,105.717 rows=8,708,805 loops=1)

  • Filter: (NOT deleted)
  • Rows Removed by Filter: 255514
12. 1,128.505 185,431.159 ↓ 1.0 694,567 1

Hash (cost=795,561.18..795,561.18 rows=666,410 width=263) (actual time=185,431.159..185,431.159 rows=694,567 loops=1)

  • Buckets: 65536 Batches: 32 Memory Usage: 7033kB
13. 184,302.654 184,302.654 ↓ 1.0 694,567 1

Seq Scan on cache_occurrences_functional o (cost=0.00..795,561.18 rows=666,410 width=263) (actual time=16,718.133..184,302.654 rows=694,567 loops=1)

  • Filter: ((updated_on < '2019-02-09 12:30:13'::timestamp without time zone) AND (updated_on >= '2019-02-06 10:50:00'::timestamp without time zone))
  • Rows Removed by Filter: 8007034
14. 74.081 298.809 ↑ 1.0 310,194 1

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

  • Buckets: 262144 Batches: 2 Memory Usage: 8739kB
15. 224.728 224.728 ↑ 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.058..224.728 rows=310,194 loops=1)

16. 1,035.493 11,785.201 ↑ 1.0 4,548,154 1

Hash (cost=183,336.51..183,336.51 rows=4,577,435 width=8) (actual time=11,785.201..11,785.201 rows=4,548,154 loops=1)

  • Buckets: 262144 Batches: 32 Memory Usage: 7286kB
17. 10,749.708 10,749.708 ↑ 1.0 4,548,154 1

Seq Scan on samples sp (cost=0.00..183,336.51 rows=4,577,435 width=8) (actual time=4.194..10,749.708 rows=4,548,154 loops=1)

  • Filter: (NOT deleted)
  • Rows Removed by Filter: 39455
18. 57.702 1,341.528 ↑ 1.0 183,550 1

Hash (cost=11,430.18..11,430.18 rows=187,191 width=18) (actual time=1,341.527..1,341.528 rows=183,550 loops=1)

  • Buckets: 262144 Batches: 2 Memory Usage: 6551kB
19. 1,283.826 1,283.826 ↑ 1.0 183,550 1

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

  • Filter: (NOT deleted)
  • Rows Removed by Filter: 8805
20. 47.163 178.286 ↑ 1.0 183,550 1

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

  • Buckets: 262144 Batches: 2 Memory Usage: 6544kB
21. 131.123 131.123 ↑ 1.0 183,550 1

Seq Scan on locations lp (cost=0.00..11,430.18 rows=187,191 width=18) (actual time=0.036..131.123 rows=183,550 loops=1)

  • Filter: (NOT deleted)
  • Rows Removed by Filter: 8805
22. 2,873.197 5,779.534 ↑ 1.0 4,537,948 1

Hash (cost=178,865.50..178,865.50 rows=4,573,750 width=277) (actual time=5,779.534..5,779.534 rows=4,537,948 loops=1)

  • Buckets: 32768 Batches: 256 Memory Usage: 2215kB
23. 2,906.337 2,906.337 ↑ 1.0 4,537,948 1

Seq Scan on cache_samples_nonfunctional snf (cost=0.00..178,865.50 rows=4,573,750 width=277) (actual time=0.060..2,906.337 rows=4,537,948 loops=1)

24. 337,559.562 337,559.562 ↑ 1.0 1 694,567

Index Scan using pk_cache_occurrences_nonfunctional on cache_occurrences_nonfunctional onf (cost=0.43..0.91 rows=1 width=240) (actual time=0.486..0.486 rows=1 loops=694,567)

  • Index Cond: (id = occ.id)