explain.depesz.com

PostgreSQL's explain analyze made readable

Result: EuHY

Settings
# exclusive inclusive rows x rows loops node
1. 784.519 8,992.646 ↓ 2.5 747,648 1

Sort (cost=2,054,467.03..2,055,200.24 rows=293,286 width=65) (actual time=8,806.574..8,992.646 rows=747,648 loops=1)

  • Sort Key: wqx.id
  • Sort Method: external merge Disk: 37984kB
2.          

CTE wqx

3. 367.232 484.693 ↑ 1.0 747,648 1

Sort (cost=104,757.64..106,626.76 rows=747,648 width=14) (actual time=417.005..484.693 rows=747,648 loops=1)

  • Sort Key: well_query_index.id
  • Sort Method: external sort Disk: 17552kB
4. 117.461 117.461 ↑ 1.0 747,648 1

Seq Scan on well_query_index (cost=0.00..19,038.60 rows=747,648 width=14) (actual time=20.850..117.461 rows=747,648 loops=1)

  • Filter: (query_id = 324)
5. 453.763 8,208.127 ↓ 2.5 747,648 1

Hash Join (cost=215,235.78..1,909,177.55 rows=293,286 width=65) (actual time=2,422.181..8,208.127 rows=747,648 loops=1)

  • Hash Cond: (well.library_id = library.library_id)
6. 73.312 3,268.073 ↓ 2.5 747,648 1

Nested Loop (cost=215,209.17..268,024.10 rows=293,286 width=58) (actual time=2,421.738..3,268.073 rows=747,648 loops=1)

7. 0.007 0.050 ↑ 1.0 1 1

Nested Loop (cost=0.55..16.60 rows=1 width=8) (actual time=0.046..0.050 rows=1 loops=1)

8. 0.027 0.027 ↑ 1.0 1 1

Index Scan using screen_result_pkey on screen_result (cost=0.28..8.29 rows=1 width=8) (actual time=0.026..0.027 rows=1 loops=1)

  • Index Cond: (screen_result_id = 941)
9. 0.016 0.016 ↑ 1.0 1 1

Index Scan using screen_pkey on screen (cost=0.28..8.29 rows=1 width=8) (actual time=0.014..0.016 rows=1 loops=1)

  • Index Cond: (screen_id = screen_result.screen_id)
10. 624.065 3,194.711 ↓ 2.5 747,648 1

Hash Right Join (cost=215,208.62..265,074.65 rows=293,286 width=58) (actual time=2,421.687..3,194.711 rows=747,648 loops=1)

  • Hash Cond: (reagent.well_id = well.well_id)
11. 149.284 149.284 ↑ 1.0 1,395,533 1

Seq Scan on reagent (cost=0.00..25,611.33 rows=1,395,533 width=10) (actual time=0.014..149.284 rows=1,395,533 loops=1)

12. 168.511 2,421.362 ↓ 2.5 747,648 1

Hash (cost=208,105.54..208,105.54 rows=293,286 width=68) (actual time=2,421.362..2,421.362 rows=747,648 loops=1)

  • Buckets: 65536 (originally 65536) Batches: 32 (originally 16) Memory Usage: 3585kB
13. 344.314 2,252.851 ↓ 2.5 747,648 1

Hash Join (cost=150,002.09..208,105.54 rows=293,286 width=68) (actual time=1,808.357..2,252.851 rows=747,648 loops=1)

  • Hash Cond: (assay_well.well_id = well.well_id)
14. 100.362 100.362 ↑ 1.1 747,648 1

Index Only Scan using assay_well_screen_result_id_key on assay_well (cost=0.56..36,764.59 rows=821,487 width=14) (actual time=0.046..100.362 rows=747,648 loops=1)

  • Index Cond: (screen_result_id = 941)
  • Heap Fetches: 0
15. 158.883 1,808.175 ↑ 1.0 747,648 1

Hash (cost=133,353.92..133,353.92 rows=747,648 width=54) (actual time=1,808.175..1,808.175 rows=747,648 loops=1)

  • Buckets: 65536 Batches: 32 Memory Usage: 2061kB
16. 459.009 1,649.292 ↑ 1.0 747,648 1

Hash Join (cost=83,233.24..133,353.92 rows=747,648 width=54) (actual time=1,002.470..1,649.292 rows=747,648 loops=1)

  • Hash Cond: (wqx.well_id = well.well_id)
17. 604.897 604.897 ↑ 1.0 747,648 1

CTE Scan on wqx (cost=0.00..14,952.96 rows=747,648 width=36) (actual time=417.007..604.897 rows=747,648 loops=1)

18. 328.619 585.386 ↑ 1.0 2,094,144 1

Hash (cost=44,785.44..44,785.44 rows=2,094,144 width=18) (actual time=585.386..585.386 rows=2,094,144 loops=1)

  • Buckets: 65536 Batches: 32 Memory Usage: 3816kB
19. 256.767 256.767 ↑ 1.0 2,094,144 1

Seq Scan on well (cost=0.00..44,785.44 rows=2,094,144 width=18) (actual time=0.062..256.767 rows=2,094,144 loops=1)

20. 0.155 0.403 ↑ 1.0 338 1

Hash (cost=22.38..22.38 rows=338 width=15) (actual time=0.403..0.403 rows=338 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 25kB
21. 0.248 0.248 ↑ 1.0 338 1

Seq Scan on library (cost=0.00..22.38 rows=338 width=15) (actual time=0.016..0.248 rows=338 loops=1)

22.          

SubPlan (forHash Join)

23. 0.000 4,485.888 ↑ 1.0 1 747,648

Limit (cost=0.57..5.58 rows=1 width=8) (actual time=0.006..0.006 rows=1 loops=747,648)

24. 4,485.888 4,485.888 ↑ 2.0 1 747,648

Index Scan using data_column_well_id on result_value (cost=0.57..10.59 rows=2 width=8) (actual time=0.006..0.006 rows=1 loops=747,648)

  • Index Cond: ((data_column_id = 8827) AND (well_id = assay_well.well_id))