explain.depesz.com

PostgreSQL's explain analyze made readable

Result: WTE6

Settings
# exclusive inclusive rows x rows loops node
1. 32.525 1,937.228 ↓ 67.4 26,496 1

Sort (cost=193,058.13..193,059.12 rows=393 width=122) (actual time=1,932.463..1,937.228 rows=26,496 loops=1)

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

CTE scps

3. 2.409 43.319 ↓ 3.9 3,484 1

Nested Loop (cost=54.81..7,278.23 rows=903 width=10) (actual time=0.505..43.319 rows=3,484 loops=1)

4. 22.382 37.426 ↓ 3.9 3,484 1

Hash Join (cost=54.39..6,852.52 rows=903 width=4) (actual time=0.495..37.426 rows=3,484 loops=1)

  • Hash Cond: (lab_cherry_pick.cherry_pick_request_id = cherry_pick_request.cherry_pick_request_id)
5. 14.915 14.915 ↑ 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.002..14.915 rows=294,777 loops=1)

6. 0.006 0.129 ↑ 1.0 4 1

Hash (cost=54.34..54.34 rows=4 width=4) (actual time=0.129..0.129 rows=4 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
7. 0.123 0.123 ↑ 1.0 4 1

Seq Scan on cherry_pick_request (cost=0.00..54.34 rows=4 width=4) (actual time=0.037..0.123 rows=4 loops=1)

  • Filter: (screen_id = 606)
  • Rows Removed by Filter: 1303
8. 3.484 3.484 ↑ 1.0 1 3,484

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=3,484)

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

CTE wqx

10. 11.886 20.156 ↑ 1.0 26,496 1

Sort (cost=2,615.79..2,682.03 rows=26,496 width=14) (actual time=16.318..20.156 rows=26,496 loops=1)

  • Sort Key: well_query_index.id
  • Sort Method: quicksort Memory: 2011kB
11. 8.270 8.270 ↑ 1.0 26,496 1

Seq Scan on well_query_index (cost=0.00..669.20 rows=26,496 width=14) (actual time=0.137..8.270 rows=26,496 loops=1)

  • Filter: (query_id = 322)
12. 114.489 1,904.703 ↓ 67.4 26,496 1

Hash Join (cost=108,304.56..183,080.93 rows=393 width=122) (actual time=384.506..1,904.703 rows=26,496 loops=1)

  • Hash Cond: (well.library_id = library.library_id)
13. 3.473 527.027 ↓ 67.4 26,496 1

Nested Loop (cost=108,277.96..139,145.13 rows=393 width=115) (actual time=339.012..527.027 rows=26,496 loops=1)

14. 0.005 0.016 ↑ 1.0 1 1

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

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

  • Index Cond: (screen_result_id = 169)
16. 0.005 0.005 ↑ 1.0 1 1

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

  • Index Cond: (screen_id = screen_result.screen_id)
17. 202.810 523.538 ↓ 67.4 26,496 1

Hash Right Join (cost=108,277.41..139,124.61 rows=393 width=115) (actual time=338.996..523.538 rows=26,496 loops=1)

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

Seq Scan on reagent (cost=0.00..25,611.33 rows=1,395,533 width=34) (actual time=0.017..83.655 rows=1,395,533 loops=1)

19. 7.678 237.073 ↓ 67.4 26,496 1

Hash (cost=108,272.49..108,272.49 rows=393 width=101) (actual time=237.073..237.073 rows=26,496 loops=1)

  • Buckets: 32768 (originally 1024) Batches: 1 (originally 1) Memory Usage: 3031kB
20. 15.575 229.395 ↓ 67.4 26,496 1

Nested Loop (cost=95,215.30..108,272.49 rows=393 width=101) (actual time=116.256..229.395 rows=26,496 loops=1)

  • Join Filter: (wqx.well_id = well.well_id)
21. 14.679 134.332 ↓ 8.2 26,496 1

Merge Join (cost=95,214.87..95,535.03 rows=3,245 width=68) (actual time=116.233..134.332 rows=26,496 loops=1)

  • Merge Cond: (wqx.well_id = assay_well.well_id)
22. 14.895 42.056 ↑ 1.0 26,496 1

Sort (cost=2,476.51..2,542.75 rows=26,496 width=36) (actual time=40.464..42.056 rows=26,496 loops=1)

  • Sort Key: wqx.well_id
  • Sort Method: quicksort Memory: 2011kB
23. 27.161 27.161 ↑ 1.0 26,496 1

CTE Scan on wqx (cost=0.00..529.92 rows=26,496 width=36) (actual time=16.321..27.161 rows=26,496 loops=1)

24. 64.275 77.597 ↑ 1.2 26,496 1

Sort (cost=92,738.36..92,815.98 rows=31,046 width=32) (actual time=75.762..77.597 rows=26,496 loops=1)

  • Sort Key: assay_well.well_id
  • Sort Method: quicksort Memory: 2036kB
25. 9.827 13.322 ↑ 1.2 26,496 1

Bitmap Heap Scan on assay_well (cost=1,089.17..90,422.00 rows=31,046 width=32) (actual time=4.109..13.322 rows=26,496 loops=1)

  • Recheck Cond: (screen_result_id = 169)
  • Heap Blocks: exact=3605
26. 3.495 3.495 ↑ 1.2 26,496 1

Bitmap Index Scan on assay_well_sr_is_positive_idx2 (cost=0.00..1,081.41 rows=31,046 width=0) (actual time=3.495..3.495 rows=26,496 loops=1)

  • Index Cond: (screen_result_id = 169)
27. 79.488 79.488 ↑ 1.0 1 26,496

Index Scan using well_pkey on well (cost=0.43..3.91 rows=1 width=33) (actual time=0.003..0.003 rows=1 loops=26,496)

  • Index Cond: (well_id = assay_well.well_id)
28. 0.078 0.179 ↑ 1.0 338 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 25kB
29. 0.101 0.101 ↑ 1.0 338 1

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

30.          

SubPlan (forHash Join)

31. 0.000 0.000 ↓ 0.0 0

CTE Scan on scps (cost=0.00..20.32 rows=5 width=0) (never executed)

  • Filter: (screened_well_id = wqx.well_id)
32. 44.192 44.192 ↓ 3.9 3,484 1

CTE Scan on scps scps_1 (cost=0.00..18.06 rows=903 width=32) (actual time=0.506..44.192 rows=3,484 loops=1)

33. 26.496 132.480 ↑ 1.0 1 26,496

Limit (cost=0.57..8.59 rows=1 width=8) (actual time=0.005..0.005 rows=1 loops=26,496)

34. 105.984 105.984 ↑ 1.0 1 26,496

Index Scan using result_value_data_column_and_well_index on result_value (cost=0.57..8.59 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=26,496)

  • Index Cond: ((data_column_id = 1371) AND (well_id = assay_well.well_id))
35. 0.000 105.984 ↑ 1.0 1 26,496

Limit (cost=0.57..8.59 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=26,496)

36. 105.984 105.984 ↑ 1.0 1 26,496

Index Scan using result_value_data_column_and_well_index on result_value result_value_1 (cost=0.57..8.59 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=26,496)

  • Index Cond: ((data_column_id = 1372) AND (well_id = assay_well.well_id))
37. 0.000 105.984 ↑ 1.0 1 26,496

Limit (cost=0.57..8.59 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=26,496)

38. 105.984 105.984 ↑ 1.0 1 26,496

Index Scan using result_value_data_column_and_well_index on result_value result_value_2 (cost=0.57..8.59 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=26,496)

  • Index Cond: ((data_column_id = 1379) AND (well_id = assay_well.well_id))
39. 0.000 105.984 ↑ 1.0 1 26,496

Limit (cost=0.57..8.59 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=26,496)

40. 105.984 105.984 ↑ 1.0 1 26,496

Index Scan using result_value_data_column_and_well_index on result_value result_value_3 (cost=0.57..8.59 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=26,496)

  • Index Cond: ((data_column_id = 1375) AND (well_id = assay_well.well_id))
41. 0.000 105.984 ↑ 1.0 1 26,496

Limit (cost=0.57..8.59 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=26,496)

42. 105.984 105.984 ↑ 1.0 1 26,496

Index Scan using result_value_data_column_and_well_index on result_value result_value_4 (cost=0.57..8.59 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=26,496)

  • Index Cond: ((data_column_id = 1378) AND (well_id = assay_well.well_id))
43. 0.000 105.984 ↑ 1.0 1 26,496

Limit (cost=0.57..8.59 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=26,496)

44. 105.984 105.984 ↑ 1.0 1 26,496

Index Scan using result_value_data_column_and_well_index on result_value result_value_5 (cost=0.57..8.59 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=26,496)

  • Index Cond: ((data_column_id = 1377) AND (well_id = assay_well.well_id))
45. 0.000 105.984 ↑ 1.0 1 26,496

Limit (cost=0.57..8.59 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=26,496)

46. 105.984 105.984 ↑ 1.0 1 26,496

Index Scan using result_value_data_column_and_well_index on result_value result_value_6 (cost=0.57..8.59 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=26,496)

  • Index Cond: ((data_column_id = 1373) AND (well_id = assay_well.well_id))
47. 0.000 105.984 ↑ 1.0 1 26,496

Limit (cost=0.57..8.59 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=26,496)

48. 105.984 105.984 ↑ 1.0 1 26,496

Index Scan using result_value_data_column_and_well_index on result_value result_value_7 (cost=0.57..8.59 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=26,496)

  • Index Cond: ((data_column_id = 1376) AND (well_id = assay_well.well_id))
49. 0.000 105.984 ↑ 1.0 1 26,496

Limit (cost=0.57..8.59 rows=1 width=3) (actual time=0.004..0.004 rows=1 loops=26,496)

50. 105.984 105.984 ↑ 1.0 1 26,496

Index Scan using result_value_data_column_and_well_index on result_value result_value_8 (cost=0.57..8.59 rows=1 width=3) (actual time=0.004..0.004 rows=1 loops=26,496)

  • Index Cond: ((data_column_id = 1374) AND (well_id = assay_well.well_id))
51. 9.672 79.488 ↑ 1.0 1 26,496

Nested Loop (cost=0.85..16.90 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=26,496)

52. 26.496 26.496 ↑ 1.0 1 26,496

Index Scan using silencing_reagent_pkey1 on silencing_reagent (cost=0.42..8.44 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=26,496)

  • Index Cond: (reagent_id = reagent.reagent_id)
53. 43.320 43.320 ↑ 1.0 1 21,660

Index Scan using gene_pkey1 on gene (cost=0.43..8.45 rows=1 width=8) (actual time=0.001..0.002 rows=1 loops=21,660)

  • Index Cond: (gene_id = silencing_reagent.vendor_gene_id)
54. 26.496 158.976 ↑ 1.0 1 26,496

Aggregate (cost=13.41..13.42 rows=1 width=7) (actual time=0.006..0.006 rows=1 loops=26,496)

55. 26.496 132.480 ↑ 1.0 1 26,496

Sort (cost=13.39..13.40 rows=1 width=11) (actual time=0.005..0.005 rows=1 loops=26,496)

  • Sort Key: gene_symbol.ordinal
  • Sort Method: quicksort Memory: 25kB
56. 9.672 105.984 ↑ 1.0 1 26,496

Nested Loop (cost=1.28..13.38 rows=1 width=11) (actual time=0.004..0.004 rows=1 loops=26,496)

57. 4.836 52.992 ↑ 1.0 1 26,496

Nested Loop (cost=0.85..12.89 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=26,496)

58. 26.496 26.496 ↑ 1.0 1 26,496

Index Scan using silencing_reagent_pkey1 on silencing_reagent silencing_reagent_1 (cost=0.42..8.44 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=26,496)

  • Index Cond: (reagent_id = reagent.reagent_id)
59. 21.660 21.660 ↑ 1.0 1 21,660

Index Only Scan using gene_pkey1 on gene gene_1 (cost=0.43..4.45 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=21,660)

  • Index Cond: (gene_id = silencing_reagent_1.vendor_gene_id)
  • Heap Fetches: 0
60. 43.320 43.320 ↑ 1.0 1 21,660

Index Scan using gene_symbol_natural_key on gene_symbol (cost=0.43..0.48 rows=1 width=15) (actual time=0.001..0.002 rows=1 loops=21,660)

  • Index Cond: (gene_id = gene_1.gene_id)