explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ZqnD

Settings
# exclusive inclusive rows x rows loops node
1. 0.057 210.240 ↑ 770.0 10 1

Sort (cost=41,853.78..41,873.03 rows=7,700 width=808) (actual time=210.236..210.240 rows=10 loops=1)

  • Sort Key: batches_1.rec_id, batches.hash
  • Sort Method: quicksort Memory: 35kB
2. 0.092 210.183 ↑ 770.0 10 1

Hash Left Join (cost=19,557.14..38,616.22 rows=7,700 width=808) (actual time=167.294..210.183 rows=10 loops=1)

  • Hash Cond: ((batches_1.rec_id)::text = (guides.rec_id)::text)
3. 0.058 208.934 ↑ 770.0 10 1

Nested Loop (cost=19,401.32..38,316.03 rows=7,700 width=514) (actual time=166.102..208.934 rows=10 loops=1)

  • Join Filter: ((batches_1.rec_id)::text = (batches.rec_id)::text)
4. 37.721 208.556 ↑ 1,071.0 10 1

Hash Join (cost=19,400.90..23,266.63 rows=10,710 width=119) (actual time=166.061..208.556 rows=10 loops=1)

  • Hash Cond: ((batches_1.rec_id)::text = (molecules.rec_id)::text)
5. 5.041 42.028 ↑ 1,071.0 10 1

Hash Left Join (cost=8,070.84..9,106.31 rows=10,710 width=65) (actual time=37.146..42.028 rows=10 loops=1)

  • Hash Cond: ((batches_1.rec_id)::text = (sirnas.rec_id)::text)
6. 0.049 0.371 ↑ 1,071.0 10 1

HashAggregate (cost=4,004.89..4,111.99 rows=10,710 width=58) (actual time=0.335..0.371 rows=10 loops=1)

  • Group Key: batches_1.rec_id
7. 0.000 0.322 ↑ 1,071.0 10 1

Append (cost=84.54..3,978.12 rows=10,710 width=58) (actual time=0.184..0.322 rows=10 loops=1)

8. 0.002 0.186 ↑ 1.0 10 1

Unique (cost=84.54..84.59 rows=10 width=12) (actual time=0.184..0.186 rows=10 loops=1)

9. 0.031 0.184 ↑ 1.0 10 1

Sort (cost=84.54..84.57 rows=10 width=12) (actual time=0.183..0.184 rows=10 loops=1)

  • Sort Key: batches_1.rec_id
  • Sort Method: quicksort Memory: 25kB
10. 0.153 0.153 ↑ 1.0 10 1

Index Scan using batches_hash_key on batches batches_1 (cost=0.42..84.37 rows=10 width=12) (actual time=0.030..0.153 rows=10 loops=1)

  • Index Cond: ((hash)::text = ANY ('{75510f04-b7a8-4217-8118-ef09e6562663,a94e57f8-f25d-463c-aa20-887030f4c9c3,b0ea1af2-50e1-4919-bf29-ec78ffdc0072,10bca36f-c24d-48bd-a048-7d6a881f42b7,be5f8370-bacc-4118-a064-030e1b993cd9,413a8a65-567d-4526-ba67-835703c91fac,3e5b1900-eddf-4609-afbb-414056f01faf,d8a33ee8-fa4d-4502-a078-c30a1742a0db,d77cbe02-c438-4dba-a4f8-26cf672e4148,082bb222-a373-4ee2-86fe-d3ed2749811a}'::text[]))
11. 0.033 0.136 ↓ 0.0 0 1

HashAggregate (cost=3,679.43..3,786.43 rows=10,700 width=13) (actual time=0.136..0.136 rows=0 loops=1)

  • Group Key: molecules_1.rec_id
12. 0.002 0.103 ↓ 0.0 0 1

Bitmap Heap Scan on molecules molecules_1 (cost=218.93..3,652.68 rows=10,700 width=13) (actual time=0.103..0.103 rows=0 loops=1)

  • Recheck Cond: (synonyms && '{75510f04-b7a8-4217-8118-ef09e6562663,a94e57f8-f25d-463c-aa20-887030f4c9c3,b0ea1af2-50e1-4919-bf29-ec78ffdc0072,10bca36f-c24d-48bd-a048-7d6a881f42b7,be5f8370-bacc-4118-a064-030e1b993cd9,413a8a65-567d-4526-ba67-835703c91fac,3e5b1900-eddf-4609-afbb-414056f01faf,d8a33ee8-fa4d-4502-a078-c30a1742a0db,d77cbe02-c438-4dba-a4f8-26cf672e4148,082bb222-a373-4ee2-86fe-d3ed2749811a}'::character varying[])
13. 0.101 0.101 ↓ 0.0 0 1

Bitmap Index Scan on ix_gin_cauldron_molecules_synonyms (cost=0.00..216.25 rows=10,700 width=0) (actual time=0.101..0.101 rows=0 loops=1)

  • Index Cond: (synonyms && '{75510f04-b7a8-4217-8118-ef09e6562663,a94e57f8-f25d-463c-aa20-887030f4c9c3,b0ea1af2-50e1-4919-bf29-ec78ffdc0072,10bca36f-c24d-48bd-a048-7d6a881f42b7,be5f8370-bacc-4118-a064-030e1b993cd9,413a8a65-567d-4526-ba67-835703c91fac,3e5b1900-eddf-4609-afbb-414056f01faf,d8a33ee8-fa4d-4502-a078-c30a1742a0db,d77cbe02-c438-4dba-a4f8-26cf672e4148,082bb222-a373-4ee2-86fe-d3ed2749811a}'::character varying[])
14. 17.144 36.616 ↑ 1.0 75,049 1

Hash (cost=2,681.20..2,681.20 rows=75,420 width=23) (actual time=36.615..36.616 rows=75,049 loops=1)

  • Buckets: 65536 Batches: 2 Memory Usage: 2568kB
15. 19.472 19.472 ↑ 1.0 75,049 1

Seq Scan on sirnas (cost=0.00..2,681.20 rows=75,420 width=23) (actual time=0.013..19.472 rows=75,049 loops=1)

16. 66.128 128.807 ↑ 1.0 248,847 1

Hash (cost=5,788.47..5,788.47 rows=248,847 width=54) (actual time=128.807..128.807 rows=248,847 loops=1)

  • Buckets: 65536 Batches: 8 Memory Usage: 3088kB
17. 62.679 62.679 ↑ 1.0 248,847 1

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

18. 0.320 0.320 ↑ 1.0 1 10

Index Scan using ix_cauldron_batches_rec_id on batches (cost=0.42..1.39 rows=1 width=407) (actual time=0.032..0.032 rows=1 loops=10)

  • Index Cond: ((rec_id)::text = (molecules.rec_id)::text)
19. 0.441 1.157 ↑ 1.0 3,103 1

Hash (cost=117.03..117.03 rows=3,103 width=22) (actual time=1.157..1.157 rows=3,103 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 198kB
20. 0.716 0.716 ↑ 1.0 3,103 1

Seq Scan on guides (cost=0.00..117.03 rows=3,103 width=22) (actual time=0.013..0.716 rows=3,103 loops=1)