explain.depesz.com

PostgreSQL's explain analyze made readable

Result: j4er : Optimization for: Optimization for: Optimization for: plan #GRfc; plan #D5Cu; plan #J8DK

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 1,826.442 119,772.645 ↓ 6.9 9,257 1

Sort (cost=17,301.13..17,304.50 rows=1,350 width=1,964) (actual time=119,628.652..119,772.645 rows=9,257 loops=1)

  • Sort Key: (length((reagents.label)::text)), reagents.rec_id, reagents.batch_hash
  • Sort Method: external merge Disk: 7240kB
2.          

CTE cte_rgt

3. 1.667 1.667 ↓ 92.6 9,257 1

Result (cost=0.00..0.51 rows=100 width=32) (actual time=0.008..1.667 rows=9,257 loops=1)

4. 26.427 117,946.203 ↓ 6.9 9,257 1

Subquery Scan on reagents (cost=17,200.05..17,230.43 rows=1,350 width=1,964) (actual time=117,842.039..117,946.203 rows=9,257 loops=1)

5. 489.725 117,919.776 ↓ 6.9 9,257 1

HashAggregate (cost=17,200.05..17,213.55 rows=1,350 width=1,960) (actual time=117,842.023..117,919.776 rows=9,257 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))
6. 1.458 117,430.051 ↓ 6.9 9,257 1

Append (cost=852.86..17,162.93 rows=1,350 width=1,960) (actual time=1,425.754..117,430.051 rows=9,257 loops=1)

7. 113.784 5,053.541 ↓ 92.5 9,255 1

Nested Loop Left Join (cost=852.86..1,826.94 rows=100 width=763) (actual time=1,425.753..5,053.541 rows=9,255 loops=1)

8. 33.935 4,282.652 ↓ 92.5 9,255 1

Nested Loop Left Join (cost=852.58..1,795.47 rows=100 width=456) (actual time=1,425.711..4,282.652 rows=9,255 loops=1)

9. 6.172 3,165.882 ↓ 92.5 9,255 1

Nested Loop (cost=852.16..1,746.30 rows=100 width=449) (actual time=1,425.690..3,165.882 rows=9,255 loops=1)

10. 27.457 1,743.695 ↓ 92.5 9,255 1

Nested Loop (cost=851.74..1,693.57 rows=100 width=407) (actual time=1,425.645..1,743.695 rows=9,255 loops=1)

11. 3.154 1,457.098 ↓ 92.5 9,255 1

Unique (cost=851.32..851.82 rows=100 width=4) (actual time=1,425.620..1,457.098 rows=9,255 loops=1)

12. 106.266 1,453.944 ↓ 92.5 9,255 1

Sort (cost=851.32..851.57 rows=100 width=4) (actual time=1,425.619..1,453.944 rows=9,255 loops=1)

  • Sort Key: batches_1.id
  • Sort Method: quicksort Memory: 818kB
13. 55.408 1,347.678 ↓ 92.5 9,255 1

Nested Loop (cost=2.67..848.00 rows=100 width=4) (actual time=134.908..1,347.678 rows=9,255 loops=1)

14. 181.643 236.972 ↓ 92.6 9,257 1

HashAggregate (cost=2.25..3.25 rows=100 width=32) (actual time=134.863..236.972 rows=9,257 loops=1)

  • Group Key: (cte_rgt.reagent_id)::text
15. 55.329 55.329 ↓ 92.6 9,257 1

CTE Scan on cte_rgt (cost=0.00..2.00 rows=100 width=32) (actual time=0.014..55.329 rows=9,257 loops=1)

16. 1,055.298 1,055.298 ↑ 1.0 1 9,257

Index Scan using batches_hash_key on batches batches_1 (cost=0.42..8.44 rows=1 width=41) (actual time=0.111..0.114 rows=1 loops=9,257)

  • Index Cond: ((hash)::text = (cte_rgt.reagent_id)::text)
17. 259.140 259.140 ↑ 1.0 1 9,255

Index Scan using batches_pkey on batches (cost=0.42..8.40 rows=1 width=407) (actual time=0.027..0.028 rows=1 loops=9,255)

  • Index Cond: (id = batches_1.id)
18. 1,416.015 1,416.015 ↑ 1.0 1 9,255

Index Scan using molecules_rec_id_key on molecules (cost=0.42..0.52 rows=1 width=54) (actual time=0.153..0.153 rows=1 loops=9,255)

  • Index Cond: ((rec_id)::text = (batches.rec_id)::text)
19. 1,082.835 1,082.835 ↓ 0.0 0 9,255

Index Scan using ix_cauldron_sirnas_rec_id on sirnas (cost=0.42..0.48 rows=1 width=23) (actual time=0.117..0.117 rows=0 loops=9,255)

  • Index Cond: ((molecules.rec_id)::text = (rec_id)::text)
20. 657.105 657.105 ↓ 0.0 0 9,255

Index Scan using ix_cauldron_guides_rec_id on guides (cost=0.28..0.30 rows=1 width=22) (actual time=0.071..0.071 rows=0 loops=9,255)

  • Index Cond: ((molecules.rec_id)::text = (rec_id)::text)
21. 0.090 112,375.052 ↑ 625.0 2 1

Hash Left Join (cost=4,419.85..15,322.49 rows=1,250 width=763) (actual time=111,167.995..112,375.052 rows=2 loops=1)

  • Hash Cond: ((molecules_1.rec_id)::text = (guides_1.rec_id)::text)
22.          

Initplan (for Hash Left Join)

23. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=851.74..1,293.57 rows=100 width=0) (never executed)

24. 0.000 0.000 ↓ 0.0 0

Unique (cost=851.32..851.82 rows=100 width=4) (never executed)

25. 0.000 0.000 ↓ 0.0 0

Sort (cost=851.32..851.57 rows=100 width=4) (never executed)

  • Sort Key: batches_4.id
26. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=2.67..848.00 rows=100 width=4) (never executed)

27. 0.000 0.000 ↓ 0.0 0

HashAggregate (cost=2.25..3.25 rows=100 width=32) (never executed)

  • Group Key: (cte_rgt_1.reagent_id)::text
28. 0.000 0.000 ↓ 0.0 0

CTE Scan on cte_rgt cte_rgt_1 (cost=0.00..2.00 rows=100 width=32) (never executed)

29. 0.000 0.000 ↓ 0.0 0

Index Scan using batches_hash_key on batches batches_4 (cost=0.42..8.44 rows=1 width=41) (never executed)

  • Index Cond: ((hash)::text = (cte_rgt_1.reagent_id)::text)
30. 0.000 0.000 ↓ 0.0 0

Index Only Scan using batches_pkey on batches batches_3 (cost=0.42..4.40 rows=1 width=4) (never executed)

  • Index Cond: (id = batches_4.id)
  • Heap Fetches: 0
31. 0.013 112,350.296 ↑ 625.0 2 1

Nested Loop Left Join (cost=3,407.47..14,299.01 rows=1,250 width=456) (actual time=111,143.253..112,350.296 rows=2 loops=1)

32. 0.023 112,350.253 ↑ 625.0 2 1

Nested Loop Left Join (cost=3,407.05..13,684.43 rows=1,250 width=449) (actual time=111,143.229..112,350.253 rows=2 loops=1)

  • Filter: ((batches_2.id IS NULL) OR (NOT $3))
33. 347.670 112,350.174 ↑ 1,250.5 2 1

Hash Join (cost=3,406.63..10,187.87 rows=2,501 width=54) (actual time=111,143.185..112,350.174 rows=2 loops=1)

  • Hash Cond: ((molecules_1.rec_id)::text = (molecules_2.rec_id)::text)
34. 939.152 939.152 ↑ 1.0 248,863 1

Seq Scan on molecules molecules_1 (cost=0.00..5,818.53 rows=250,053 width=54) (actual time=0.012..939.152 rows=248,863 loops=1)

35. 0.022 111,063.352 ↑ 1,250.5 2 1

Hash (cost=3,375.37..3,375.37 rows=2,501 width=13) (actual time=111,063.337..111,063.352 rows=2 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 33kB
36. 0.000 111,063.330 ↑ 1,250.5 2 1

HashAggregate (cost=3,325.35..3,350.36 rows=2,501 width=13) (actual time=111,063.319..111,063.330 rows=2 loops=1)

  • Group Key: molecules_2.rec_id
37.          

Initplan (for HashAggregate)

38. 27.963 53.600 ↑ 1.0 1 1

Aggregate (cost=2.25..2.26 rows=1 width=32) (actual time=53.599..53.600 rows=1 loops=1)

39. 25.637 25.637 ↓ 92.6 9,257 1

CTE Scan on cte_rgt cte_rgt_2 (cost=0.00..2.00 rows=100 width=32) (actual time=0.001..25.637 rows=9,257 loops=1)

40. 0.026 111,063.270 ↑ 1,250.5 2 1

Bitmap Heap Scan on molecules molecules_2 (cost=51.38..3,316.83 rows=2,501 width=13) (actual time=111,063.265..111,063.270 rows=2 loops=1)

  • Recheck Cond: (synonyms && $4)
  • Heap Blocks: exact=2
41. 111,063.244 111,063.244 ↑ 1,250.5 2 1

Bitmap Index Scan on ix_gin_cauldron_molecules_synonyms (cost=0.00..50.75 rows=2,501 width=0) (actual time=111,063.244..111,063.244 rows=2 loops=1)

  • Index Cond: (synonyms && $4)
42. 0.056 0.056 ↓ 0.0 0 2

Index Scan using ix_cauldron_batches_rec_id on batches batches_2 (cost=0.42..1.39 rows=1 width=407) (actual time=0.028..0.028 rows=0 loops=2)

  • Index Cond: ((molecules_1.rec_id)::text = (rec_id)::text)
43. 0.030 0.030 ↓ 0.0 0 2

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

  • Index Cond: ((molecules_1.rec_id)::text = (rec_id)::text)
44. 1.302 24.666 ↑ 1.0 3,103 1

Hash (cost=117.21..117.21 rows=3,121 width=22) (actual time=24.666..24.666 rows=3,103 loops=1)

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

Seq Scan on guides guides_1 (cost=0.00..117.21 rows=3,121 width=22) (actual time=0.019..23.364 rows=3,103 loops=1)

Planning time : 83.461 ms