explain.depesz.com

PostgreSQL's explain analyze made readable

Result: R6q3

Settings
# exclusive inclusive rows x rows loops node
1. 0.040 767.712 ↑ 1,089.0 2 1

Sort (cost=93,601.34..93,606.78 rows=2,178 width=767) (actual time=767.711..767.712 rows=2 loops=1)

  • Sort Key: (length((COALESCE(molecules.synonyms[1], batches.hash))::text)), molecules.rec_id, batches.hash
  • Sort Method: quicksort Memory: 27kB
2. 0.113 767.672 ↑ 1,089.0 2 1

Hash Left Join (cost=12,440.82..93,480.58 rows=2,178 width=767) (actual time=197.489..767.672 rows=2 loops=1)

  • Hash Cond: ((molecules.rec_id)::text = (sgrnas.rec_id)::text)
3. 0.035 766.066 ↑ 1,089.0 2 1

Nested Loop Left Join (cost=12,327.62..93,342.61 rows=2,178 width=468) (actual time=195.898..766.066 rows=2 loops=1)

4. 463.718 765.981 ↑ 1,089.0 2 1

Hash Right Join (cost=12,327.20..92,270.89 rows=2,178 width=461) (actual time=195.856..765.981 rows=2 loops=1)

  • Hash Cond: ((batches.rec_id)::text = (molecules.rec_id)::text)
  • Filter: ((molecules.synonyms && '{75510f04-b7a8-4217-8118-ef09e6562663,a94e57f8-f25d-463c-aa20-887030f4c9c3}'::character varying[]) OR ((batches.hash)::text = ANY ('{75510f04-b7a8-4217-8118-ef09e6562663,a94e57f8-f25d-463c-aa20-887030f4c9c3}'::text[])))
  • Rows Removed by Filter: 265,868
5. 150.447 150.447 ↓ 1.0 180,317 1

Seq Scan on batches (cost=0.00..55,297.11 rows=178,911 width=407) (actual time=0.025..150.447 rows=180,317 loops=1)

6. 74.498 151.816 ↓ 1.0 248,847 1

Hash (cost=6,806.20..6,806.20 rows=247,920 width=54) (actual time=151.815..151.816 rows=248,847 loops=1)

  • Buckets: 65,536 Batches: 8 Memory Usage: 3,111kB
7. 77.318 77.318 ↓ 1.0 248,847 1

Seq Scan on molecules (cost=0.00..6,806.20 rows=247,920 width=54) (actual time=0.026..77.318 rows=248,847 loops=1)

8. 0.050 0.050 ↓ 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.025..0.025 rows=0 loops=2)

  • Index Cond: ((rec_id)::text = (molecules.rec_id)::text)
9. 0.588 1.493 ↑ 1.0 3,103 1

Hash (cost=74.20..74.20 rows=3,120 width=21) (actual time=1.492..1.493 rows=3,103 loops=1)

  • Buckets: 4,096 Batches: 1 Memory Usage: 195kB
10. 0.905 0.905 ↑ 1.0 3,103 1

Seq Scan on sgrnas (cost=0.00..74.20 rows=3,120 width=21) (actual time=0.011..0.905 rows=3,103 loops=1)

Planning time : 2.211 ms
Execution time : 767.888 ms