explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 6Z3V

Settings
# exclusive inclusive rows x rows loops node
1. 0.044 0.546 ↑ 547.0 2 1

Sort (cost=13,928.93..13,931.67 rows=1,094 width=1,964) (actual time=0.546..0.546 rows=2 loops=1)

  • Sort Key: (length((reagents.label)::text)), reagents.rec_id, reagents.batch_hash
  • Sort Method: quicksort Memory: 27kB
2. 0.006 0.502 ↑ 547.0 2 1

Subquery Scan on reagents (cost=13,849.09..13,873.71 rows=1,094 width=1,964) (actual time=0.502..0.502 rows=2 loops=1)

3. 0.024 0.496 ↑ 547.0 2 1

HashAggregate (cost=13,849.09..13,860.03 rows=1,094 width=1,960) (actual time=0.496..0.496 rows=2 loops=1)

  • Group Key: batches.hash, (COALESCE(batches.hash, molecules.synonyms[1])), (COALESCE(molecules.synonyms[1], batches.hash)), (array_remove(array_cat(molecules.synonyms, ARRAY[batches.hash, molecules.rec_id]), NULL::character varying)), molecules.type, batches.id, batches.name, batches.hash, batches.batch_fields, molecules.rec_id, (COALESCE(sirnas.gene, guides.gene))
4. 0.001 0.472 ↑ 547.0 2 1

Append (cost=18.42..13,819.01 rows=1,094 width=1,960) (actual time=0.241..0.472 rows=2 loops=1)

5. 0.017 0.358 ↑ 1.0 2 1

Nested Loop Left Join (cost=18.42..36.51 rows=2 width=763) (actual time=0.240..0.358 rows=2 loops=1)

6. 0.004 0.331 ↑ 1.0 2 1

Nested Loop Left Join (cost=18.14..35.88 rows=2 width=456) (actual time=0.219..0.331 rows=2 loops=1)

7. 0.008 0.313 ↑ 1.0 2 1

Nested Loop (cost=17.72..34.90 rows=2 width=449) (actual time=0.207..0.313 rows=2 loops=1)

8. 0.007 0.271 ↑ 1.0 2 1

Nested Loop (cost=17.30..33.81 rows=2 width=407) (actual time=0.180..0.271 rows=2 loops=1)

9. 0.001 0.144 ↑ 1.0 2 1

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

10. 0.011 0.143 ↑ 1.0 2 1

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

  • Sort Key: batches_1.id
  • Sort Method: quicksort Memory: 25kB
11. 0.132 0.132 ↑ 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.061..0.132 rows=2 loops=1)

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

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

  • Index Cond: (id = batches_1.id)
13. 0.034 0.034 ↑ 1.0 1 2

Index Scan using molecules_rec_id_key on molecules (cost=0.42..0.53 rows=1 width=54) (actual time=0.017..0.017 rows=1 loops=2)

  • Index Cond: ((rec_id)::text = (batches.rec_id)::text)
14. 0.014 0.014 ↓ 0.0 0 2

Index Scan using ix_cauldron_sirnas_rec_id on sirnas (cost=0.42..0.48 rows=1 width=23) (actual time=0.007..0.007 rows=0 loops=2)

  • Index Cond: ((molecules.rec_id)::text = (rec_id)::text)
15. 0.010 0.010 ↓ 0.0 0 2

Index Scan using ix_cauldron_guides_rec_id on guides (cost=0.28..0.30 rows=1 width=22) (actual time=0.005..0.005 rows=0 loops=2)

  • Index Cond: ((molecules.rec_id)::text = (rec_id)::text)
16. 0.001 0.113 ↓ 0.0 0 1

Hash Left Join (cost=3,414.59..13,771.56 rows=1,092 width=763) (actual time=0.113..0.113 rows=0 loops=1)

  • Hash Cond: ((molecules_1.rec_id)::text = (guides_1.rec_id)::text)
17. 0.000 0.112 ↓ 0.0 0 1

Nested Loop Left Join (cost=3,258.77..13,606.04 rows=1,092 width=456) (actual time=0.112..0.112 rows=0 loops=1)

18. 0.000 0.112 ↓ 0.0 0 1

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

  • Filter: (NOT (hashed SubPlan 1))
19. 0.037 0.113 ↓ 0.0 0 1

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

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

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

21. 0.002 0.064 ↓ 0.0 0 1

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

  • Buckets: 4096 Batches: 1 Memory Usage: 32kB
22. 0.007 0.062 ↓ 0.0 0 1

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

  • Group Key: molecules_2.rec_id
23. 0.003 0.055 ↓ 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.055..0.055 rows=0 loops=1)

  • Recheck Cond: (synonyms && '{3e268ce3-1480-4fd0-bf48-004fe835fe29,63beffc3-524a-4a7a-b70c-163f3336ba44}'::character varying[])
24. 0.052 0.052 ↓ 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.052..0.052 rows=0 loops=1)

  • Index Cond: (synonyms && '{3e268ce3-1480-4fd0-bf48-004fe835fe29,63beffc3-524a-4a7a-b70c-163f3336ba44}'::character varying[])
25. 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=407) (never executed)

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

SubPlan (for Nested Loop Left Join)

27. 0.000 0.000 ↓ 0.0 0

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

28. 0.000 0.000 ↓ 0.0 0

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

  • Sort Key: batches_3.id
29. 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[]))
30. 0.000 0.000 ↓ 0.0 0

Index Scan using ix_cauldron_sirnas_rec_id on sirnas sirnas_1 (cost=0.42..0.48 rows=1 width=23) (never executed)

  • Index Cond: ((molecules_1.rec_id)::text = (rec_id)::text)
31. 0.000 0.000 ↓ 0.0 0

Hash (cost=117.03..117.03 rows=3,103 width=22) (never executed)

32. 0.000 0.000 ↓ 0.0 0

Seq Scan on guides guides_1 (cost=0.00..117.03 rows=3,103 width=22) (never executed)