explain.depesz.com

PostgreSQL's explain analyze made readable

Result: OIjC

Settings
# exclusive inclusive rows x rows loops node
1. 0.742 769.432 ↓ 1,000.0 1,000 1

Nested Loop Left Join (cost=42,381.48..105,913.10 rows=1 width=283) (actual time=493.889..769.432 rows=1,000 loops=1)

2.          

CTE searches

3. 0.334 0.334 ↑ 1.0 1,000 1

Values Scan on "*VALUES*" (cost=0.00..12.50 rows=1,000 width=32) (actual time=0.003..0.334 rows=1,000 loops=1)

4. 0.730 757.690 ↓ 1,000.0 1,000 1

Nested Loop Left Join (cost=42,368.70..105,900.29 rows=1 width=72) (actual time=493.873..757.690 rows=1,000 loops=1)

5. 133.219 746.960 ↓ 1,000.0 1,000 1

Hash Right Join (cost=42,368.29..105,899.80 rows=1 width=65) (actual time=493.845..746.960 rows=1,000 loops=1)

  • Hash Cond: ((b.rec_id)::text = (m.rec_id)::text)
  • Filter: ((b.rec_id IS NULL) OR ((b.hash)::text = (NULL::text)))
  • Rows Removed by Filter: 342
6. 120.577 120.577 ↑ 1.2 180,338 1

Seq Scan on batches b (cost=0.00..55,694.39 rows=218,639 width=60) (actual time=0.016..120.577 rows=180,338 loops=1)

7. 0.860 493.164 ↑ 111.8 1,000 1

Hash (cost=39,878.66..39,878.66 rows=111,810 width=49) (actual time=493.164..493.164 rows=1,000 loops=1)

  • Buckets: 65536 Batches: 4 Memory Usage: 538kB
8. 41.054 492.304 ↑ 111.8 1,000 1

Hash Join (cost=31,649.60..39,878.66 rows=111,810 width=49) (actual time=450.958..492.304 rows=1,000 loops=1)

  • Hash Cond: (("*SELECT* 1".rec_id)::text = (m.rec_id)::text)
9. 0.349 223.587 ↑ 111.8 1,000 1

Unique (cost=21,286.14..22,124.72 rows=111,810 width=90) (actual time=223.049..223.587 rows=1,000 loops=1)

10. 6.253 223.238 ↑ 111.8 1,000 1

Sort (cost=21,286.14..21,565.67 rows=111,810 width=90) (actual time=223.047..223.238 rows=1,000 loops=1)

  • Sort Key: "*SELECT* 1".rec_id, (NULL::text)
  • Sort Method: quicksort Memory: 165kB
11. 0.174 216.985 ↑ 111.8 1,000 1

Append (cost=3,324.49..6,177.49 rows=111,810 width=90) (actual time=205.322..216.985 rows=1,000 loops=1)

12. 0.002 204.656 ↓ 0.0 0 1

Subquery Scan on *SELECT* 1 (cost=3,324.49..3,374.29 rows=2,490 width=45) (actual time=204.656..204.656 rows=0 loops=1)

13. 0.000 204.654 ↓ 0.0 0 1

HashAggregate (cost=3,324.49..3,349.39 rows=2,490 width=45) (actual time=204.654..204.654 rows=0 loops=1)

  • Group Key: molecules.rec_id, NULL::text
14.          

Initplan (for HashAggregate)

15. 0.344 1.080 ↑ 1.0 1 1

Aggregate (cost=22.50..22.51 rows=1 width=32) (actual time=1.078..1.080 rows=1 loops=1)

16. 0.736 0.736 ↑ 1.0 1,000 1

CTE Scan on searches searches_1 (cost=0.00..20.00 rows=1,000 width=32) (actual time=0.006..0.736 rows=1,000 loops=1)

17. 0.005 204.639 ↓ 0.0 0 1

Bitmap Heap Scan on molecules (cost=39.30..3,289.53 rows=2,490 width=45) (actual time=204.639..204.639 rows=0 loops=1)

  • Recheck Cond: (synonyms && $1)
18. 204.634 204.634 ↓ 0.0 0 1

Bitmap Index Scan on ix_gin_cauldron_molecules_synonyms (cost=0.00..38.67 rows=2,490 width=0) (actual time=204.634..204.634 rows=0 loops=1)

  • Index Cond: (synonyms && $1)
19. 0.219 12.155 ↑ 109.3 1,000 1

Subquery Scan on *SELECT* 2 (cost=22.92..2,803.20 rows=109,320 width=44) (actual time=0.663..12.155 rows=1,000 loops=1)

20. 0.966 11.936 ↑ 109.3 1,000 1

Nested Loop (cost=22.92..1,710.00 rows=109,320 width=49) (actual time=0.661..11.936 rows=1,000 loops=1)

21. 0.789 0.970 ↓ 5.0 1,000 1

HashAggregate (cost=22.50..24.50 rows=200 width=32) (actual time=0.615..0.970 rows=1,000 loops=1)

  • Group Key: searches.s
22. 0.181 0.181 ↑ 1.0 1,000 1

CTE Scan on searches (cost=0.00..20.00 rows=1,000 width=32) (actual time=0.002..0.181 rows=1,000 loops=1)

23. 10.000 10.000 ↑ 1.0 1 1,000

Index Scan using batches_hash_key on batches (cost=0.42..8.42 rows=1 width=49) (actual time=0.010..0.010 rows=1 loops=1,000)

  • Index Cond: ((hash)::text = searches.s)
24. 95.671 227.663 ↑ 1.0 248,847 1

Hash (cost=5,791.98..5,791.98 rows=248,998 width=17) (actual time=227.663..227.663 rows=248,847 loops=1)

  • Buckets: 65536 Batches: 4 Memory Usage: 3583kB
25. 131.992 131.992 ↑ 1.0 248,847 1

Seq Scan on molecules m (cost=0.00..5,791.98 rows=248,998 width=17) (actual time=0.013..131.992 rows=248,847 loops=1)

26. 10.000 10.000 ↓ 0.0 0 1,000

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

  • Index Cond: ((rec_id)::text = (m.rec_id)::text)
27. 11.000 11.000 ↓ 0.0 0 1,000

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

  • Index Cond: ((rec_id)::text = (m.rec_id)::text)
Planning time : 2.495 ms
Execution time : 769.894 ms