explain.depesz.com

PostgreSQL's explain analyze made readable

Result: D5Cu : Optimization for: plan #GRfc

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.015 0.338 ↑ 547.0 2 1

HashAggregate (cost=13,117.48..13,128.42 rows=1,094 width=58) (actual time=0.335..0.338 rows=2 loops=1)

  • Group Key: molecules.rec_id
2. 0.001 0.323 ↑ 547.0 2 1

Append (cost=17.72..13,114.74 rows=1,094 width=58) (actual time=0.144..0.323 rows=2 loops=1)

3. 0.006 0.205 ↑ 1.0 2 1

Nested Loop (cost=17.72..34.90 rows=2 width=13) (actual time=0.143..0.205 rows=2 loops=1)

4. 0.006 0.101 ↑ 1.0 2 1

Nested Loop (cost=17.30..33.81 rows=2 width=12) (actual time=0.070..0.101 rows=2 loops=1)

5. 0.001 0.057 ↑ 1.0 2 1

Unique (cost=16.88..16.89 rows=2 width=4) (actual time=0.055..0.057 rows=2 loops=1)

6. 0.012 0.056 ↑ 1.0 2 1

Sort (cost=16.88..16.89 rows=2 width=4) (actual time=0.055..0.056 rows=2 loops=1)

  • Sort Key: batches_1.id
  • Sort Method: quicksort Memory: 25kB
7. 0.044 0.044 ↑ 1.0 2 1

Index Scan using batches_hash_key on batches batches_1 (cost=0.42..16.87 rows=2 width=4) (actual time=0.025..0.044 rows=2 loops=1)

  • Index Cond: ((hash)::text = ANY ('{3e268ce3-1480-4fd0-bf48-004fe835fe29,63beffc3-524a-4a7a-b70c-163f3336ba44}'::text[]))
8. 0.038 0.038 ↑ 1.0 1 2

Index Scan using batches_pkey on batches (cost=0.42..8.44 rows=1 width=16) (actual time=0.018..0.019 rows=1 loops=2)

  • Index Cond: (id = batches_1.id)
9. 0.098 0.098 ↑ 1.0 1 2

Index Only Scan using molecules_rec_id_key on molecules (cost=0.42..0.53 rows=1 width=13) (actual time=0.048..0.049 rows=1 loops=2)

  • Index Cond: (rec_id = (batches.rec_id)::text)
  • Heap Fetches: 2
10. 0.001 0.117 ↓ 0.0 0 1

Nested Loop Left Join (cost=3,258.35..13,068.90 rows=1,092 width=13) (actual time=0.117..0.117 rows=0 loops=1)

  • Filter: (NOT (hashed SubPlan 1))
11. 0.049 0.116 ↓ 0.0 0 1

Hash Join (cost=3,241.03..9,984.51 rows=2,183 width=13) (actual time=0.116..0.116 rows=0 loops=1)

  • Hash Cond: ((molecules_1.rec_id)::text = (molecules_2.rec_id)::text)
12. 0.015 0.015 ↑ 248,847.0 1 1

Seq Scan on molecules molecules_1 (cost=0.00..5,788.47 rows=248,847 width=13) (actual time=0.015..0.015 rows=1 loops=1)

13. 0.001 0.052 ↓ 0.0 0 1

Hash (cost=3,213.75..3,213.75 rows=2,183 width=13) (actual time=0.052..0.052 rows=0 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 32kB
14. 0.010 0.051 ↓ 0.0 0 1

HashAggregate (cost=3,170.09..3,191.92 rows=2,183 width=13) (actual time=0.051..0.051 rows=0 loops=1)

  • Group Key: molecules_2.rec_id
15. 0.001 0.041 ↓ 0.0 0 1

Bitmap Heap Scan on molecules molecules_2 (cost=44.92..3,164.63 rows=2,183 width=13) (actual time=0.041..0.041 rows=0 loops=1)

  • Recheck Cond: (synonyms && '{3e268ce3-1480-4fd0-bf48-004fe835fe29,63beffc3-524a-4a7a-b70c-163f3336ba44}'::character varying[])
16. 0.040 0.040 ↓ 0.0 0 1

Bitmap Index Scan on ix_gin_cauldron_molecules_synonyms (cost=0.00..44.37 rows=2,183 width=0) (actual time=0.040..0.040 rows=0 loops=1)

  • Index Cond: (synonyms && '{3e268ce3-1480-4fd0-bf48-004fe835fe29,63beffc3-524a-4a7a-b70c-163f3336ba44}'::character varying[])
17. 0.000 0.000 ↓ 0.0 0

Index Scan using ix_cauldron_batches_rec_id on batches batches_2 (cost=0.42..1.39 rows=1 width=16) (never executed)

  • Index Cond: ((molecules_1.rec_id)::text = (rec_id)::text)
18.          

SubPlan (for Nested Loop Left Join)

19. 0.000 0.000 ↓ 0.0 0

Unique (cost=16.88..16.89 rows=2 width=4) (never executed)

20. 0.000 0.000 ↓ 0.0 0

Sort (cost=16.88..16.89 rows=2 width=4) (never executed)

  • Sort Key: batches_3.id
21. 0.000 0.000 ↓ 0.0 0

Index Scan using batches_hash_key on batches batches_3 (cost=0.42..16.87 rows=2 width=4) (never executed)

  • Index Cond: ((hash)::text = ANY ('{3e268ce3-1480-4fd0-bf48-004fe835fe29,63beffc3-524a-4a7a-b70c-163f3336ba44}'::text[]))