explain.depesz.com

PostgreSQL's explain analyze made readable

Result: xNTk : Optimization for: plan #yDMf

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 8.754 179.972 ↓ 1.2 1,627 1

Sort (cost=18,437.54..18,441.06 rows=1,408 width=1,964) (actual time=179.567..179.972 rows=1,627 loops=1)

  • Sort Key: (length((reagents.label)::text)), reagents.rec_id, reagents.batch_hash
  • Sort Method: quicksort Memory: 1698kB
2.          

CTE cte_rgt

3. 0.162 0.162 ↓ 16.3 1,627 1

Result (cost=0.00..0.51 rows=100 width=32) (actual time=0.008..0.162 rows=1,627 loops=1)

4. 0.396 171.218 ↓ 1.2 1,627 1

Subquery Scan on reagents (cost=18,331.72..18,363.40 rows=1,408 width=1,964) (actual time=169.832..171.218 rows=1,627 loops=1)

5. 5.614 170.822 ↓ 1.2 1,627 1

HashAggregate (cost=18,331.72..18,345.80 rows=1,408 width=1,960) (actual time=169.818..170.822 rows=1,627 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. 0.145 165.208 ↓ 1.2 1,627 1

Append (cost=852.86..18,293.00 rows=1,408 width=1,960) (actual time=23.486..165.208 rows=1,627 loops=1)

7. 2.262 62.181 ↓ 16.2 1,622 1

Nested Loop Left Join (cost=852.86..1,827.13 rows=100 width=763) (actual time=23.485..62.181 rows=1,622 loops=1)

8. 0.004 55.053 ↓ 16.2 1,622 1

Nested Loop Left Join (cost=852.58..1,795.67 rows=100 width=456) (actual time=23.440..55.053 rows=1,622 loops=1)

9. 0.137 46.939 ↓ 16.2 1,622 1

Nested Loop (cost=852.16..1,746.71 rows=100 width=449) (actual time=23.428..46.939 rows=1,622 loops=1)

10. 0.065 28.960 ↓ 16.2 1,622 1

Nested Loop (cost=851.74..1,693.57 rows=100 width=407) (actual time=23.390..28.960 rows=1,622 loops=1)

11. 0.303 24.029 ↓ 16.2 1,622 1

Unique (cost=851.32..851.82 rows=100 width=4) (actual time=23.354..24.029 rows=1,622 loops=1)

12. 0.976 23.726 ↓ 16.2 1,622 1

Sort (cost=851.32..851.57 rows=100 width=4) (actual time=23.354..23.726 rows=1,622 loops=1)

  • Sort Key: batches_1.id
  • Sort Method: quicksort Memory: 125kB
13. 1.380 22.750 ↓ 16.2 1,622 1

Nested Loop (cost=2.67..848.00 rows=100 width=4) (actual time=1.419..22.750 rows=1,622 loops=1)

14. 1.148 1.846 ↓ 16.3 1,627 1

HashAggregate (cost=2.25..3.25 rows=100 width=32) (actual time=1.341..1.846 rows=1,627 loops=1)

  • Group Key: (cte_rgt.reagent_id)::text
15. 0.698 0.698 ↓ 16.3 1,627 1

CTE Scan on cte_rgt (cost=0.00..2.00 rows=100 width=32) (actual time=0.010..0.698 rows=1,627 loops=1)

16. 19.524 19.524 ↑ 1.0 1 1,627

Index Scan using batches_hash_key on batches batches_1 (cost=0.42..8.44 rows=1 width=41) (actual time=0.012..0.012 rows=1 loops=1,627)

  • Index Cond: ((hash)::text = (cte_rgt.reagent_id)::text)
17. 4.866 4.866 ↑ 1.0 1 1,622

Index Scan using batches_pkey on batches (cost=0.42..8.40 rows=1 width=407) (actual time=0.003..0.003 rows=1 loops=1,622)

  • Index Cond: (id = batches_1.id)
18. 17.842 17.842 ↑ 1.0 1 1,622

Index Scan using molecules_rec_id_key on molecules (cost=0.42..0.52 rows=1 width=54) (actual time=0.010..0.011 rows=1 loops=1,622)

  • Index Cond: ((rec_id)::text = (batches.rec_id)::text)
19. 8.110 8.110 ↓ 0.0 0 1,622

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

  • Index Cond: ((molecules.rec_id)::text = (rec_id)::text)
20. 4.866 4.866 ↓ 0.0 0 1,622

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

  • Index Cond: ((molecules.rec_id)::text = (rec_id)::text)
21. 0.191 102.882 ↑ 261.6 5 1

Hash Left Join (cost=5,161.65..16,451.79 rows=1,308 width=763) (actual time=101.543..102.882 rows=5 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.010 101.201 ↑ 261.6 5 1

Nested Loop Left Join (cost=4,149.27..15,427.80 rows=1,308 width=456) (actual time=99.870..101.201 rows=5 loops=1)

32. 0.022 101.156 ↑ 261.6 5 1

Nested Loop Left Join (cost=4,148.85..14,787.38 rows=1,308 width=449) (actual time=99.852..101.156 rows=5 loops=1)

  • Filter: ((batches_2.id IS NULL) OR (NOT $3))
33. 22.841 101.069 ↑ 523.0 5 1

Hash Join (cost=4,148.43..11,240.32 rows=2,615 width=54) (actual time=99.809..101.069 rows=5 loops=1)

  • Hash Cond: ((molecules_1.rec_id)::text = (molecules_2.rec_id)::text)
34. 55.013 55.013 ↑ 1.1 248,873 1

Seq Scan on molecules molecules_1 (cost=0.00..6,085.08 rows=261,508 width=54) (actual time=0.020..55.013 rows=248,873 loops=1)

35. 0.008 23.215 ↑ 523.0 5 1

Hash (cost=4,115.75..4,115.75 rows=2,615 width=13) (actual time=23.215..23.215 rows=5 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 33kB
36. 0.000 23.207 ↑ 523.0 5 1

HashAggregate (cost=4,063.45..4,089.60 rows=2,615 width=13) (actual time=23.198..23.207 rows=5 loops=1)

  • Group Key: molecules_2.rec_id
37.          

Initplan (for HashAggregate)

38. 0.029 15.436 ↑ 1.0 1 1

Aggregate (cost=453.75..453.76 rows=1 width=32) (actual time=15.436..15.436 rows=1 loops=1)

39. 0.001 15.407 ↑ 20.0 5 1

Subquery Scan on only_synonyms (cost=0.00..453.50 rows=100 width=32) (actual time=15.372..15.407 rows=5 loops=1)

40. 1.074 15.406 ↑ 20.0 5 1

HashSetOp Except (cost=0.00..452.50 rows=100 width=36) (actual time=15.371..15.406 rows=5 loops=1)

41. 0.274 14.332 ↓ 16.2 3,249 1

Append (cost=0.00..452.00 rows=200 width=36) (actual time=0.003..14.332 rows=3,249 loops=1)

42. 0.198 0.354 ↓ 16.3 1,627 1

Subquery Scan on *SELECT* 1 (cost=0.00..3.00 rows=100 width=36) (actual time=0.003..0.354 rows=1,627 loops=1)

43. 0.156 0.156 ↓ 16.3 1,627 1

CTE Scan on cte_rgt cte_rgt_2 (cost=0.00..2.00 rows=100 width=32) (actual time=0.002..0.156 rows=1,627 loops=1)

44. 0.166 13.704 ↓ 16.2 1,622 1

Subquery Scan on *SELECT* 2 (cost=2.67..449.00 rows=100 width=41) (actual time=1.040..13.704 rows=1,622 loops=1)

45. 0.809 13.538 ↓ 16.2 1,622 1

Nested Loop (cost=2.67..448.00 rows=100 width=37) (actual time=1.040..13.538 rows=1,622 loops=1)

46. 1.129 1.340 ↓ 16.3 1,627 1

HashAggregate (cost=2.25..3.25 rows=100 width=32) (actual time=0.964..1.340 rows=1,627 loops=1)

  • Group Key: (cte_rgt_3.reagent_id)::text
47. 0.211 0.211 ↓ 16.3 1,627 1

CTE Scan on cte_rgt cte_rgt_3 (cost=0.00..2.00 rows=100 width=32) (actual time=0.001..0.211 rows=1,627 loops=1)

48. 11.389 11.389 ↑ 1.0 1 1,627

Index Only Scan using batches_hash_key on batches batches_5 (cost=0.42..4.44 rows=1 width=37) (actual time=0.007..0.007 rows=1 loops=1,627)

  • Index Cond: (hash = (cte_rgt_3.reagent_id)::text)
  • Heap Fetches: 1137
49. 0.030 23.189 ↑ 523.0 5 1

Bitmap Heap Scan on molecules molecules_2 (cost=188.27..3,603.15 rows=2,615 width=13) (actual time=23.176..23.189 rows=5 loops=1)

  • Recheck Cond: (synonyms && $5)
  • Heap Blocks: exact=3
50. 23.159 23.159 ↑ 523.0 5 1

Bitmap Index Scan on ix_gin_cauldron_molecules_synonyms (cost=0.00..187.61 rows=2,615 width=0) (actual time=23.159..23.159 rows=5 loops=1)

  • Index Cond: (synonyms && $5)
51. 0.065 0.065 ↓ 0.0 0 5

Index Scan using ix_cauldron_batches_rec_id on batches batches_2 (cost=0.42..1.35 rows=1 width=407) (actual time=0.013..0.013 rows=0 loops=5)

  • Index Cond: ((molecules_1.rec_id)::text = (rec_id)::text)
52. 0.035 0.035 ↓ 0.0 0 5

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

  • Index Cond: ((molecules_1.rec_id)::text = (rec_id)::text)
53. 0.575 1.490 ↑ 1.0 3,103 1

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

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

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

Planning time : 4.548 ms