explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 9Gfl

Settings
# exclusive inclusive rows x rows loops node
1. 0.039 274.159 ↓ 25.0 25 1

Sort (cost=46,338.66..46,338.67 rows=1 width=122) (actual time=274.157..274.159 rows=25 loops=1)

  • Sort Key: wqx.id
  • Sort Method: quicksort Memory: 31kB
2.          

CTE scps

3. 1.592 41.594 ↓ 3.9 3,484 1

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

4. 22.064 36.518 ↓ 3.9 3,484 1

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

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

6. 0.002 0.121 ↑ 1.0 4 1

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

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

Seq Scan on cherry_pick_request (cost=0.00..54.34 rows=4 width=4) (actual time=0.038..0.119 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. 0.005 17.374 ↑ 1.0 25 1

Limit (cost=7,824.36..7,824.42 rows=25 width=14) (actual time=17.367..17.374 rows=25 loops=1)

11. 6.936 17.369 ↑ 1,036.5 25 1

Sort (cost=7,824.36..7,889.14 rows=25,912 width=14) (actual time=17.366..17.369 rows=25 loops=1)

  • Sort Key: well_query_index.id
  • Sort Method: top-N heapsort Memory: 26kB
12. 7.486 10.433 ↓ 1.0 26,496 1

Bitmap Heap Scan on well_query_index (cost=1,249.24..7,093.14 rows=25,912 width=14) (actual time=2.983..10.433 rows=26,496 loops=1)

  • Recheck Cond: (query_id = 254)
  • Heap Blocks: exact=170
13. 2.947 2.947 ↓ 1.0 26,496 1

Bitmap Index Scan on well_query_unique (cost=0.00..1,242.76 rows=25,912 width=0) (actual time=2.947..2.947 rows=26,496 loops=1)

  • Index Cond: (query_id = 254)
14. 0.161 274.120 ↓ 25.0 25 1

Nested Loop (cost=213.45..31,236.00 rows=1 width=122) (actual time=146.627..274.120 rows=25 loops=1)

15. 0.011 223.959 ↓ 25.0 25 1

Nested Loop (cost=213.31..31,124.11 rows=1 width=115) (actual time=104.029..223.959 rows=25 loops=1)

16. 0.006 0.023 ↑ 1.0 1 1

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

17. 0.011 0.011 ↑ 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.010..0.011 rows=1 loops=1)

  • Index Cond: (screen_result_id = 169)
18. 0.006 0.006 ↑ 1.0 1 1

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

  • Index Cond: (screen_id = screen_result.screen_id)
19. 0.031 223.925 ↓ 25.0 25 1

Nested Loop (cost=212.75..31,107.50 rows=1 width=115) (actual time=104.006..223.925 rows=25 loops=1)

20. 116.400 223.769 ↑ 1.0 25 1

Hash Right Join (cost=212.19..31,056.94 rows=25 width=93) (actual time=103.989..223.769 rows=25 loops=1)

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

22. 0.014 17.580 ↑ 1.0 25 1

Hash (cost=211.88..211.88 rows=25 width=69) (actual time=17.580..17.580 rows=25 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
23. 0.012 17.566 ↑ 1.0 25 1

Nested Loop (cost=0.43..211.88 rows=25 width=69) (actual time=17.396..17.566 rows=25 loops=1)

24. 17.379 17.379 ↑ 1.0 25 1

CTE Scan on wqx (cost=0.00..0.50 rows=25 width=36) (actual time=17.369..17.379 rows=25 loops=1)

25. 0.175 0.175 ↑ 1.0 1 25

Index Scan using well_pkey on well (cost=0.43..8.45 rows=1 width=33) (actual time=0.006..0.007 rows=1 loops=25)

  • Index Cond: (well_id = wqx.well_id)
26. 0.125 0.125 ↑ 1.0 1 25

Index Scan using assay_well_screen_result_id_key on assay_well (cost=0.56..2.01 rows=1 width=32) (actual time=0.005..0.005 rows=1 loops=25)

  • Index Cond: ((screen_result_id = 169) AND (well_id = well.well_id))
27. 0.025 0.025 ↑ 1.0 1 25

Index Scan using library_pkey on library (cost=0.15..0.17 rows=1 width=15) (actual time=0.001..0.001 rows=1 loops=25)

  • Index Cond: (library_id = well.library_id)
28.          

SubPlan (forNested Loop)

29. 48.700 48.700 ↓ 0.0 0 25

CTE Scan on scps (cost=0.00..20.32 rows=5 width=0) (actual time=1.948..1.948 rows=0 loops=25)

  • Filter: (screened_well_id = wqx.well_id)
  • Rows Removed by Filter: 3484
30. 0.000 0.000 ↓ 0.0 0

CTE Scan on scps scps_1 (cost=0.00..18.06 rows=903 width=32) (never executed)

31. 0.000 0.125 ↑ 1.0 1 25

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

32. 0.125 0.125 ↑ 1.0 1 25

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.005..0.005 rows=1 loops=25)

  • Index Cond: ((data_column_id = 1371) AND (well_id = assay_well.well_id))
33. 0.025 0.100 ↑ 1.0 1 25

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

34. 0.075 0.075 ↑ 1.0 1 25

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.003..0.003 rows=1 loops=25)

  • Index Cond: ((data_column_id = 1372) AND (well_id = assay_well.well_id))
35. 0.000 0.100 ↑ 1.0 1 25

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

36. 0.100 0.100 ↑ 1.0 1 25

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=25)

  • Index Cond: ((data_column_id = 1379) AND (well_id = assay_well.well_id))
37. 0.025 0.100 ↑ 1.0 1 25

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

38. 0.075 0.075 ↑ 1.0 1 25

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.003..0.003 rows=1 loops=25)

  • Index Cond: ((data_column_id = 1375) AND (well_id = assay_well.well_id))
39. 0.000 0.100 ↑ 1.0 1 25

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

40. 0.100 0.100 ↑ 1.0 1 25

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=25)

  • Index Cond: ((data_column_id = 1378) AND (well_id = assay_well.well_id))
41. 0.000 0.100 ↑ 1.0 1 25

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

42. 0.100 0.100 ↑ 1.0 1 25

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=25)

  • Index Cond: ((data_column_id = 1377) AND (well_id = assay_well.well_id))
43. 0.000 0.100 ↑ 1.0 1 25

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

44. 0.100 0.100 ↑ 1.0 1 25

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=25)

  • Index Cond: ((data_column_id = 1373) AND (well_id = assay_well.well_id))
45. 0.000 0.100 ↑ 1.0 1 25

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

46. 0.100 0.100 ↑ 1.0 1 25

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=25)

  • Index Cond: ((data_column_id = 1376) AND (well_id = assay_well.well_id))
47. 0.025 0.100 ↑ 1.0 1 25

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

48. 0.075 0.075 ↑ 1.0 1 25

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.003..0.003 rows=1 loops=25)

  • Index Cond: ((data_column_id = 1374) AND (well_id = assay_well.well_id))
49. 0.010 0.100 ↑ 1.0 1 25

Nested Loop (cost=0.85..16.90 rows=1 width=4) (actual time=0.003..0.004 rows=1 loops=25)

50. 0.050 0.050 ↑ 1.0 1 25

Index Scan using silencing_reagent_pkey1 on silencing_reagent (cost=0.42..8.44 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=25)

  • Index Cond: (reagent_id = reagent.reagent_id)
51. 0.040 0.040 ↑ 1.0 1 20

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

  • Index Cond: (gene_id = silencing_reagent.vendor_gene_id)
52. 0.050 0.250 ↑ 1.0 1 25

Aggregate (cost=13.41..13.42 rows=1 width=7) (actual time=0.010..0.010 rows=1 loops=25)

53. 0.050 0.200 ↑ 1.0 1 25

Sort (cost=13.39..13.40 rows=1 width=11) (actual time=0.008..0.008 rows=1 loops=25)

  • Sort Key: gene_symbol.ordinal
  • Sort Method: quicksort Memory: 25kB
54. 0.010 0.150 ↑ 1.0 1 25

Nested Loop (cost=1.28..13.38 rows=1 width=11) (actual time=0.005..0.006 rows=1 loops=25)

55. 0.035 0.100 ↑ 1.0 1 25

Nested Loop (cost=0.85..12.89 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=25)

56. 0.025 0.025 ↑ 1.0 1 25

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=25)

  • Index Cond: (reagent_id = reagent.reagent_id)
57. 0.040 0.040 ↑ 1.0 1 20

Index Only Scan using gene_pkey1 on gene gene_1 (cost=0.43..4.45 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=20)

  • Index Cond: (gene_id = silencing_reagent_1.vendor_gene_id)
  • Heap Fetches: 0
58. 0.040 0.040 ↑ 1.0 1 20

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

  • Index Cond: (gene_id = gene_1.gene_id)