explain.depesz.com

PostgreSQL's explain analyze made readable

Result: mM6S

Settings
# exclusive inclusive rows x rows loops node
1. 0.033 761.851 ↑ 1,089.0 2 1

Sort (cost=93,601.34..93,606.78 rows=2,178 width=767) (actual time=761.850..761.851 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.063 761.818 ↑ 1,089.0 2 1

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

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

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

4. 489.563 760.506 ↑ 1,089.0 2 1

Hash Right Join (cost=12,327.20..92,270.89 rows=2,178 width=461) (actual time=186.129..760.506 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: 265868
5. 128.004 128.004 ↓ 1.0 180,317 1

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

6. 69.953 142.939 ↓ 1.0 248,847 1

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

  • Buckets: 65536 Batches: 8 Memory Usage: 3111kB
7. 72.986 72.986 ↓ 1.0 248,847 1

Seq Scan on molecules (cost=0.00..6,806.20 rows=247,920 width=54) (actual time=0.015..72.986 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.452 1.173 ↑ 1.0 3,103 1

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

  • Buckets: 4096 Batches: 1 Memory Usage: 195kB
10. 0.721 0.721 ↑ 1.0 3,103 1

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