explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 2O1R

Settings
# exclusive inclusive rows x rows loops node
1. 1.213 105,089.725 ↑ 4.9 162 1

Hash Join (cost=1,287.90..68,447,827.55 rows=794 width=16) (actual time=585.450..105,089.725 rows=162 loops=1)

  • Hash Cond: (screen.screen_id = assay_plate.screen_id)
2. 0.772 0.772 ↑ 1.0 1,344 1

Seq Scan on screen (cost=0.00..184.44 rows=1,344 width=16) (actual time=0.006..0.772 rows=1,344 loops=1)

3. 0.045 11.462 ↑ 4.9 162 1

Hash (cost=1,277.97..1,277.97 rows=794 width=4) (actual time=11.462..11.462 rows=162 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 14kB
4. 0.885 11.417 ↑ 4.9 162 1

HashAggregate (cost=1,262.09..1,270.03 rows=794 width=4) (actual time=11.363..11.417 rows=162 loops=1)

  • Group Key: assay_plate.screen_id
5. 4.743 10.532 ↓ 1.3 1,640 1

Hash Join (cost=900.70..1,258.99 rows=1,241 width=4) (actual time=2.712..10.532 rows=1,640 loops=1)

  • Hash Cond: (ls.activity_id = assay_plate.library_screening_id)
6. 3.096 3.096 ↑ 1.0 12,594 1

Seq Scan on library_screening ls (cost=0.00..219.94 rows=12,594 width=4) (actual time=0.007..3.096 rows=12,594 loops=1)

7. 0.596 2.693 ↓ 1.3 1,640 1

Hash (cost=885.19..885.19 rows=1,241 width=8) (actual time=2.693..2.693 rows=1,640 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 81kB
8. 0.539 2.097 ↓ 1.3 1,640 1

Nested Loop (cost=5.08..885.19 rows=1,241 width=8) (actual time=0.043..2.097 rows=1,640 loops=1)

9. 0.021 0.150 ↑ 3.9 44 1

Nested Loop (cost=4.66..653.66 rows=172 width=4) (actual time=0.034..0.150 rows=44 loops=1)

10. 0.025 0.041 ↑ 1.0 11 1

Bitmap Heap Scan on copy (cost=4.37..27.99 rows=11 width=4) (actual time=0.023..0.041 rows=11 loops=1)

  • Recheck Cond: (library_id = 61)
  • Heap Blocks: exact=7
11. 0.016 0.016 ↑ 1.0 11 1

Bitmap Index Scan on copy_library_id (cost=0.00..4.36 rows=11 width=0) (actual time=0.016..0.016 rows=11 loops=1)

  • Index Cond: (library_id = 61)
12. 0.088 0.088 ↑ 4.0 4 11

Index Scan using plate_copy_id on plate (cost=0.29..56.72 rows=16 width=8) (actual time=0.005..0.008 rows=4 loops=11)

  • Index Cond: (copy_id = copy.copy_id)
13. 1.408 1.408 ↓ 1.5 37 44

Index Scan using assay_plate_plate_id on assay_plate (cost=0.42..1.11 rows=24 width=12) (actual time=0.006..0.032 rows=37 loops=44)

  • Index Cond: (plate_id = plate.plate_id)
14.          

SubPlan (forHash Join)

15. 749.898 105,076.278 ↑ 1.0 1 162

Aggregate (cost=86,204.45..86,204.46 rows=1 width=0) (actual time=648.619..648.619 rows=1 loops=162)

16. 2,176.842 104,326.380 ↓ 2.3 89,366 162

Hash Join (cost=73,565.04..86,105.44 rows=39,603 width=0) (actual time=545.134..643.990 rows=89,366 loops=162)

  • Hash Cond: (w.library_id = l.library_id)
17. 10,930.626 102,149.262 ↓ 2.3 89,366 162

Hash Join (cost=73,538.46..85,534.32 rows=39,603 width=4) (actual time=545.124..630.551 rows=89,366 loops=162)

  • Hash Cond: (ap.plate_number = w.plate_number)
18. 4,161.132 4,161.132 ↓ 1.6 263 162

Seq Scan on assay_plate ap (cost=0.00..5,830.16 rows=162 width=4) (actual time=5.000..25.686 rows=263 loops=162)

  • Filter: ((replicate_ordinal = 0) AND (screen_id = screen.screen_id))
  • Rows Removed by Filter: 272548
19. 30,266.946 87,057.504 ↑ 1.0 1,394,753 162

Hash (cost=50,220.62..50,220.62 rows=1,421,267 width=8) (actual time=537.392..537.392 rows=1,394,753 loops=162)

  • Buckets: 131072 Batches: 32 Memory Usage: 2617kB
20. 56,790.558 56,790.558 ↑ 1.0 1,394,753 162

Seq Scan on well w (cost=0.00..50,220.62 rows=1,421,267 width=8) (actual time=0.029..350.559 rows=1,394,753 loops=162)

  • Filter: (library_well_type = 'experimental'::text)
  • Rows Removed by Filter: 699007
21. 0.107 0.276 ↑ 1.0 337 1

Hash (cost=22.37..22.37 rows=337 width=4) (actual time=0.276..0.276 rows=337 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 20kB
22. 0.169 0.169 ↑ 1.0 337 1

Seq Scan on library l (cost=0.00..22.37 rows=337 width=4) (actual time=0.007..0.169 rows=337 loops=1)