explain.depesz.com

PostgreSQL's explain analyze made readable

Result: x71s

Settings
# exclusive inclusive rows x rows loops node
1. 25.691 44.629 ↓ 1.5 2,122 1

Gather (cost=36,058.18..48,257.84 rows=1,405 width=12) (actual time=38.558..44.629 rows=2,122 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
2. 0.193 18.938 ↓ 1.2 707 3 / 3

Nested Loop Left Join (cost=35,058.18..47,117.34 rows=585 width=12) (actual time=16.984..18.938 rows=707 loops=3)

3. 0.268 18.745 ↓ 1.2 707 3 / 3

Hash Left Join (cost=35,057.76..43,060.25 rows=585 width=12) (actual time=16.983..18.745 rows=707 loops=3)

  • Hash Cond: (bioactivities_observation.literature_source_id = bioactivities_literaturesource.id)
4. 0.094 8.952 ↓ 1.2 707 3 / 3

Nested Loop (cost=31,505.38..39,506.34 rows=585 width=16) (actual time=7.304..8.952 rows=707 loops=3)

5. 0.631 7.443 ↓ 1.2 707 3 / 3

Merge Join (cost=31,504.95..35,435.66 rows=585 width=24) (actual time=7.300..7.443 rows=707 loops=3)

  • Merge Cond: (bioactivities_target.id = bioactivities_observation.target_id)
6. 1.389 1.389 ↑ 8.0 7,208 3 / 3

Parallel Index Only Scan using bioactivities_accessiongroup_pkey on bioactivities_target (cost=0.42..3,772.61 rows=57,475 width=8) (actual time=0.026..1.389 rows=7,208 loops=3)

  • Heap Fetches: 20,890
7. 0.529 5.423 ↓ 1.3 2,122 3 / 3

Sort (cost=31,503.85..31,508.08 rows=1,693 width=40) (actual time=5.308..5.423 rows=2,122 loops=3)

  • Sort Key: bioactivities_observation.target_id
  • Sort Method: quicksort Memory: 262kB
8. 0.460 4.894 ↓ 1.3 2,122 3 / 3

Nested Loop (cost=171.56..31,413.06 rows=1,693 width=40) (actual time=0.827..4.894 rows=2,122 loops=3)

9. 0.001 0.089 ↑ 1.0 1 3 / 3

Nested Loop (cost=5.28..59.93 rows=1 width=8) (actual time=0.076..0.089 rows=1 loops=3)

10. 0.008 0.075 ↑ 1.0 1 3 / 3

Nested Loop (cost=4.86..53.00 rows=1 width=24) (actual time=0.062..0.075 rows=1 loops=3)

11. 0.035 0.035 ↑ 1.5 2 3 / 3

Index Only Scan using probex_protein_accessions_protein_id_accession_id_ef10ab2a_uniq on probex_protein_accessions (cost=0.42..16.44 rows=3 width=8) (actual time=0.032..0.035 rows=2 loops=3)

  • Index Cond: (protein_id = 212,333)
  • Heap Fetches: 2
12. 0.006 0.032 ↓ 0.0 0 6 / 3

Bitmap Heap Scan on bioactivities_target_accessions (cost=4.44..12.17 rows=2 width=16) (actual time=0.016..0.016 rows=0 loops=6)

  • Recheck Cond: (accession_id = probex_protein_accessions.accession_id)
  • Heap Blocks: exact=1
13. 0.026 0.026 ↓ 0.0 0 6 / 3

Bitmap Index Scan on bioactivities_accessiongroup_accessions_accession_id_a9f71c10 (cost=0.00..4.43 rows=2 width=0) (actual time=0.013..0.013 rows=0 loops=6)

  • Index Cond: (accession_id = probex_protein_accessions.accession_id)
14. 0.013 0.013 ↑ 1.0 1 3 / 3

Index Only Scan using probex_accession_pkey on probex_accession (cost=0.42..6.93 rows=1 width=8) (actual time=0.013..0.013 rows=1 loops=3)

  • Index Cond: (id = bioactivities_target_accessions.accession_id)
  • Heap Fetches: 1
15. 3.931 4.345 ↑ 4.6 2,122 3 / 3

Bitmap Heap Scan on bioactivities_observation (cost=166.28..31,256.30 rows=9,683 width=32) (actual time=0.748..4.345 rows=2,122 loops=3)

  • Recheck Cond: (target_id = bioactivities_target_accessions.target_id)
  • Heap Blocks: exact=2,033
16. 0.414 0.414 ↑ 4.6 2,122 3 / 3

Bitmap Index Scan on bioactivities_observation_accession_group_id_2dd6f69c (cost=0.00..163.86 rows=9,683 width=0) (actual time=0.414..0.414 rows=2,122 loops=3)

  • Index Cond: (target_id = bioactivities_target_accessions.target_id)
17. 1.415 1.415 ↑ 1.0 1 2,122 / 3

Index Scan using bioactivities_rawsmiles_pkey on bioactivities_rawsmiles (cost=0.43..6.96 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=2,122)

  • Index Cond: (id = bioactivities_observation.raw_smiles_id)
  • Filter: (rdkit_mol IS NOT NULL)
18. 4.924 9.525 ↑ 1.0 112,861 1 / 3

Hash (cost=2,141.61..2,141.61 rows=112,861 width=12) (actual time=28.575..28.575 rows=112,861 loops=1)

  • Buckets: 131,072 Batches: 1 Memory Usage: 5,809kB
19. 4.601 4.601 ↑ 1.0 112,861 1 / 3

Seq Scan on bioactivities_literaturesource (cost=0.00..2,141.61 rows=112,861 width=12) (actual time=0.007..13.802 rows=112,861 loops=1)

20. 0.000 0.000 ↓ 0.0 0 2,122 / 3

Index Scan using probex_pubmed_pkey on probex_pubmed (cost=0.42..6.94 rows=1 width=8) (actual time=0.000..0.000 rows=0 loops=2,122)

  • Index Cond: (bioactivities_literaturesource.pubmed_id = id)
Planning time : 8.874 ms
Execution time : 44.765 ms