explain.depesz.com

PostgreSQL's explain analyze made readable

Result: tfaM

Settings
# exclusive inclusive rows x rows loops node
1. 813.982 9,531.281 ↓ 2.5 747,648 1

Sort (cost=3,552,887.42..3,553,620.63 rows=293,286 width=65) (actual time=9,341.006..9,531.281 rows=747,648 loops=1)

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

CTE scps

3. 0.412 39.476 ↓ 1.2 561 1

Nested Loop (cost=54.78..7,061.08 rows=452 width=10) (actual time=4.736..39.476 rows=561 loops=1)

4. 18.665 38.503 ↓ 1.2 561 1

Hash Join (cost=54.36..6,847.99 rows=452 width=4) (actual time=4.716..38.503 rows=561 loops=1)

  • Hash Cond: (lab_cherry_pick.cherry_pick_request_id = cherry_pick_request.cherry_pick_request_id)
5. 19.675 19.675 ↑ 1.0 294,777 1

Seq Scan on lab_cherry_pick (cost=0.00..5,682.62 rows=295,062 width=8) (actual time=0.007..19.675 rows=294,777 loops=1)

6. 0.001 0.163 ↓ 1.5 3 1

Hash (cost=54.34..54.34 rows=2 width=4) (actual time=0.163..0.163 rows=3 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
7. 0.162 0.162 ↓ 1.5 3 1

Seq Scan on cherry_pick_request (cost=0.00..54.34 rows=2 width=4) (actual time=0.068..0.162 rows=3 loops=1)

  • Filter: (screen_id = 3851)
  • Rows Removed by Filter: 1304
8. 0.561 0.561 ↑ 1.0 1 561

Index Scan using screener_cherry_pick_pkey on screener_cherry_pick (cost=0.42..0.46 rows=1 width=14) (actual time=0.001..0.001 rows=1 loops=561)

  • Index Cond: (screener_cherry_pick_id = lab_cherry_pick.screener_cherry_pick_id)
9.          

CTE wqx

10. 401.277 536.926 ↑ 1.0 747,648 1

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

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

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

  • Filter: (query_id = 324)
12. 753.980 8,717.299 ↓ 2.5 747,648 1

Hash Join (cost=215,235.78..3,400,536.86 rows=293,286 width=65) (actual time=2,612.021..8,717.299 rows=747,648 loops=1)

  • Hash Cond: (well.library_id = library.library_id)
13. 71.796 3,437.537 ↓ 2.5 747,648 1

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

14. 0.006 0.036 ↑ 1.0 1 1

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

15. 0.019 0.019 ↑ 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.018..0.019 rows=1 loops=1)

  • Index Cond: (screen_result_id = 941)
16. 0.011 0.011 ↑ 1.0 1 1

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

  • Index Cond: (screen_id = screen_result.screen_id)
17. 641.505 3,365.705 ↓ 2.5 747,648 1

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

  • Hash Cond: (reagent.well_id = well.well_id)
18. 152.550 152.550 ↑ 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.013..152.550 rows=1,395,533 loops=1)

19. 172.178 2,571.650 ↓ 2.5 747,648 1

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

  • Buckets: 65536 (originally 65536) Batches: 32 (originally 16) Memory Usage: 3585kB
20. 345.685 2,399.472 ↓ 2.5 747,648 1

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

  • Hash Cond: (assay_well.well_id = well.well_id)
21. 101.038 101.038 ↑ 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.060..101.038 rows=747,648 loops=1)

  • Index Cond: (screen_result_id = 941)
  • Heap Fetches: 0
22. 149.943 1,952.749 ↑ 1.0 747,648 1

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

  • Buckets: 65536 Batches: 32 Memory Usage: 2061kB
23. 468.654 1,802.806 ↑ 1.0 747,648 1

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

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

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

25. 377.947 667.662 ↑ 1.0 2,094,144 1

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

  • Buckets: 65536 Batches: 32 Memory Usage: 3816kB
26. 289.715 289.715 ↑ 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.012..289.715 rows=2,094,144 loops=1)

27. 0.090 0.280 ↑ 1.0 338 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 25kB
28. 0.190 0.190 ↑ 1.0 338 1

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

29.          

SubPlan (forHash Join)

30. 0.000 0.000 ↓ 0.0 0

CTE Scan on scps (cost=0.00..10.17 rows=2 width=0) (never executed)

  • Filter: (screened_well_id = wqx.well_id)
31. 39.614 39.614 ↓ 1.2 561 1

CTE Scan on scps scps_1 (cost=0.00..9.04 rows=452 width=32) (actual time=4.738..39.614 rows=561 loops=1)

32. 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)

33. 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))