explain.depesz.com

PostgreSQL's explain analyze made readable

Result: KVdi

Settings
# exclusive inclusive rows x rows loops node
1. 32.428 1,952.570 ↓ 69.0 26,496 1

Sort (cost=198,086.20..198,087.16 rows=384 width=122) (actual time=1,947.926..1,952.570 rows=26,496 loops=1)

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

CTE scps

3. 1.113 39.814 ↓ 3.9 3,484 1

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

4. 21.706 35.217 ↓ 3.9 3,484 1

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

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

6. 0.007 0.154 ↑ 1.0 4 1

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

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

Seq Scan on cherry_pick_request (cost=0.00..54.34 rows=4 width=4) (actual time=0.037..0.147 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. 8.092 19.996 ↓ 1.0 26,496 1

Sort (cost=8,992.67..9,057.45 rows=25,912 width=14) (actual time=18.026..19.996 rows=26,496 loops=1)

  • Sort Key: well_query_index.id
  • Sort Method: quicksort Memory: 2011kB
11. 6.375 11.904 ↓ 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=5.574..11.904 rows=26,496 loops=1)

  • Recheck Cond: (query_id = 254)
  • Heap Blocks: exact=170
12. 5.529 5.529 ↓ 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=5.529..5.529 rows=26,496 loops=1)

  • Index Cond: (query_id = 254)
13. 123.639 1,920.142 ↓ 69.0 26,496 1

Hash Join (cost=107,963.38..181,734.04 rows=384 width=122) (actual time=382.253..1,920.142 rows=26,496 loops=1)

  • Hash Cond: (well.library_id = library.library_id)
14. 3.520 536.316 ↓ 69.0 26,496 1

Nested Loop (cost=107,936.77..138,803.80 rows=384 width=115) (actual time=340.010..536.316 rows=26,496 loops=1)

15. 0.013 0.059 ↑ 1.0 1 1

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

16. 0.029 0.029 ↑ 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.028..0.029 rows=1 loops=1)

  • Index Cond: (screen_result_id = 169)
17. 0.017 0.017 ↑ 1.0 1 1

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

  • Index Cond: (screen_id = screen_result.screen_id)
18. 212.326 532.737 ↓ 69.0 26,496 1

Hash Right Join (cost=107,936.22..138,783.36 rows=384 width=115) (actual time=339.935..532.737 rows=26,496 loops=1)

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

20. 7.891 230.502 ↓ 69.0 26,496 1

Hash (cost=107,931.42..107,931.42 rows=384 width=101) (actual time=230.502..230.502 rows=26,496 loops=1)

  • Buckets: 32768 (originally 1024) Batches: 1 (originally 1) Memory Usage: 3031kB
21. 18.044 222.611 ↓ 69.0 26,496 1

Nested Loop (cost=95,156.55..107,931.42 rows=384 width=101) (actual time=106.734..222.611 rows=26,496 loops=1)

  • Join Filter: (wqx.well_id = well.well_id)
22. 15.055 125.079 ↓ 8.3 26,496 1

Merge Join (cost=95,156.12..95,472.65 rows=3,174 width=68) (actual time=106.710..125.079 rows=26,496 loops=1)

  • Merge Cond: (wqx.well_id = assay_well.well_id)
23. 10.105 35.005 ↓ 1.0 26,496 1

Sort (cost=2,417.76..2,482.54 rows=25,912 width=36) (actual time=33.428..35.005 rows=26,496 loops=1)

  • Sort Key: wqx.well_id
  • Sort Method: quicksort Memory: 2011kB
24. 24.900 24.900 ↓ 1.0 26,496 1

CTE Scan on wqx (cost=0.00..518.24 rows=25,912 width=36) (actual time=18.028..24.900 rows=26,496 loops=1)

25. 52.935 75.019 ↑ 1.2 26,496 1

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

  • Sort Key: assay_well.well_id
  • Sort Method: quicksort Memory: 2036kB
26. 16.298 22.084 ↑ 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=6.363..22.084 rows=26,496 loops=1)

  • Recheck Cond: (screen_result_id = 169)
  • Heap Blocks: exact=3605
27. 5.786 5.786 ↑ 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=5.786..5.786 rows=26,496 loops=1)

  • Index Cond: (screen_result_id = 169)
28. 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)
29. 0.582 0.860 ↑ 1.0 338 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 25kB
30. 0.278 0.278 ↑ 1.0 338 1

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

31.          

SubPlan (forHash Join)

32. 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)
33. 40.511 40.511 ↓ 3.9 3,484 1

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

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

35. 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))
36. 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)

37. 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))
38. 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)

39. 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))
40. 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)

41. 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))
42. 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)

43. 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))
44. 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)

45. 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))
46. 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)

47. 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))
48. 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)

49. 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))
50. 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)

51. 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))
52. 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)

53. 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)
54. 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)
55. 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)

56. 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
57. 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)

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

59. 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)
60. 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
61. 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)