explain.depesz.com

PostgreSQL's explain analyze made readable

Result: yDMf

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 98.594 10,368.314 ↓ 1.2 1,627 1

Sort (cost=17,366.97..17,370.36 rows=1,356 width=1,964) (actual time=10,367.406..10,368.314 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.275 0.275 ↓ 16.3 1,627 1

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

4. 27.687 10,269.720 ↓ 1.2 1,627 1

Subquery Scan on reagents (cost=17,265.41..17,295.92 rows=1,356 width=1,964) (actual time=10,240.693..10,269.720 rows=1,627 loops=1)

5. 75.555 10,242.033 ↓ 1.2 1,627 1

HashAggregate (cost=17,265.41..17,278.97 rows=1,356 width=1,960) (actual time=10,240.678..10,242.033 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.222 10,166.478 ↓ 1.2 1,627 1

Append (cost=852.86..17,228.12 rows=1,356 width=1,960) (actual time=249.804..10,166.478 rows=1,627 loops=1)

7. 2.539 810.399 ↓ 16.2 1,622 1

Nested Loop Left Join (cost=852.86..1,826.96 rows=100 width=763) (actual time=249.804..810.399 rows=1,622 loops=1)

8. 0.801 749.468 ↓ 16.2 1,622 1

Nested Loop Left Join (cost=852.58..1,795.49 rows=100 width=456) (actual time=249.772..749.468 rows=1,622 loops=1)

9. 1.939 542.673 ↓ 16.2 1,622 1

Nested Loop (cost=852.16..1,746.34 rows=100 width=449) (actual time=249.755..542.673 rows=1,622 loops=1)

10. 1.287 284.458 ↓ 16.2 1,622 1

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

11. 0.510 250.731 ↓ 16.2 1,622 1

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

12. 1.160 250.221 ↓ 16.2 1,622 1

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

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

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

14. 1.505 24.647 ↓ 16.3 1,627 1

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

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

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

16. 196.867 196.867 ↑ 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.104..0.121 rows=1 loops=1,627)

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

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

  • Index Cond: (id = batches_1.id)
18. 256.276 256.276 ↑ 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.158..0.158 rows=1 loops=1,622)

  • Index Cond: ((rec_id)::text = (batches.rec_id)::text)
19. 205.994 205.994 ↓ 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.127..0.127 rows=0 loops=1,622)

  • Index Cond: ((molecules.rec_id)::text = (rec_id)::text)
20. 58.392 58.392 ↓ 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.036..0.036 rows=0 loops=1,622)

  • Index Cond: ((molecules.rec_id)::text = (rec_id)::text)
21. 0.064 9,355.857 ↑ 251.2 5 1

Hash Left Join (cost=4,446.94..15,387.60 rows=1,256 width=763) (actual time=9,325.540..9,355.857 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.014 9,326.466 ↑ 251.2 5 1

Nested Loop Left Join (cost=3,434.55..14,364.07 rows=1,256 width=456) (actual time=9,296.166..9,326.466 rows=5 loops=1)

32. 0.019 9,326.392 ↑ 251.2 5 1

Nested Loop Left Join (cost=3,434.14..13,746.80 rows=1,256 width=449) (actual time=9,296.147..9,326.392 rows=5 loops=1)

  • Filter: ((batches_2.id IS NULL) OR (NOT $3))
33. 408.706 9,326.288 ↑ 502.4 5 1

Hash Join (cost=3,433.72..10,245.60 rows=2,512 width=54) (actual time=9,296.115..9,326.288 rows=5 loops=1)

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

Seq Scan on molecules molecules_1 (cost=0.00..5,844.83 rows=251,183 width=54) (actual time=0.013..1,028.321 rows=248,863 loops=1)

35. 0.005 7,889.261 ↑ 502.4 5 1

Hash (cost=3,402.32..3,402.32 rows=2,512 width=13) (actual time=7,889.261..7,889.261 rows=5 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 33kB
36. 0.000 7,889.256 ↑ 502.4 5 1

HashAggregate (cost=3,352.08..3,377.20 rows=2,512 width=13) (actual time=7,889.245..7,889.256 rows=5 loops=1)

  • Group Key: molecules_2.rec_id
37.          

Initplan (for HashAggregate)

38. 24.506 24.817 ↑ 1.0 1 1

Aggregate (cost=2.25..2.26 rows=1 width=32) (actual time=24.816..24.817 rows=1 loops=1)

39. 0.311 0.311 ↓ 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.001..0.311 rows=1,627 loops=1)

40. 0.022 7,889.233 ↑ 502.4 5 1

Bitmap Heap Scan on molecules molecules_2 (cost=63.47..3,343.53 rows=2,512 width=13) (actual time=7,889.226..7,889.233 rows=5 loops=1)

  • Recheck Cond: (synonyms && $4)
  • Heap Blocks: exact=3
41. 7,889.211 7,889.211 ↑ 502.4 5 1

Bitmap Index Scan on ix_gin_cauldron_molecules_synonyms (cost=0.00..62.84 rows=2,512 width=0) (actual time=7,889.211..7,889.211 rows=5 loops=1)

  • Index Cond: (synonyms && $4)
42. 0.085 0.085 ↓ 0.0 0 5

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

  • Index Cond: ((molecules_1.rec_id)::text = (rec_id)::text)
43. 0.060 0.060 ↓ 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.012..0.012 rows=0 loops=5)

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

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

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

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

Planning time : 28.033 ms