explain.depesz.com

PostgreSQL's explain analyze made readable

Result: CbIG

Settings
# exclusive inclusive rows x rows loops node
1. 32.006 1,949.623 ↓ 67.4 26,496 1

Sort (cost=184,013.95..184,014.93 rows=393 width=122) (actual time=1,945.294..1,949.623 rows=26,496 loops=1)

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

CTE wqx

3. 16.722 32.557 ↑ 1.0 26,496 1

Sort (cost=2,446.79..2,513.03 rows=26,496 width=14) (actual time=28.913..32.557 rows=26,496 loops=1)

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

Seq Scan on well_query_index (cost=0.00..500.20 rows=26,496 width=14) (actual time=0.016..15.835 rows=26,496 loops=1)

  • Filter: (query_id = 321)
5. 120.190 1,917.617 ↓ 67.4 26,496 1

Hash Join (cost=108,304.56..181,483.98 rows=393 width=122) (actual time=366.498..1,917.617 rows=26,496 loops=1)

  • Hash Cond: (well.library_id = library.library_id)
6. 3.545 551.497 ↓ 67.4 26,496 1

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

7. 0.013 0.049 ↑ 1.0 1 1

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

8. 0.023 0.023 ↑ 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.023..0.023 rows=1 loops=1)

  • Index Cond: (screen_result_id = 169)
9. 0.013 0.013 ↑ 1.0 1 1

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

  • Index Cond: (screen_id = screen_result.screen_id)
10. 208.354 547.903 ↓ 67.4 26,496 1

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

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

12. 7.818 251.137 ↓ 67.4 26,496 1

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

  • Buckets: 32768 (originally 1024) Batches: 1 (originally 1) Memory Usage: 3031kB
13. 17.772 243.319 ↓ 67.4 26,496 1

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

  • Join Filter: (wqx.well_id = well.well_id)
14. 14.964 146.059 ↓ 8.2 26,496 1

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

  • Merge Cond: (wqx.well_id = assay_well.well_id)
15. 13.720 53.598 ↑ 1.0 26,496 1

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

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

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

17. 65.132 77.497 ↑ 1.2 26,496 1

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

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

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

  • Index Cond: (screen_result_id = 169)
20. 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)
21. 0.292 0.618 ↑ 1.0 338 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 25kB
22. 0.326 0.326 ↑ 1.0 338 1

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

23.          

SubPlan (forHash Join)

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

25. 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))
26. 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)

27. 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))
28. 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)

29. 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))
30. 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)

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

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

35. 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))
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_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))
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_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))
40. 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)

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

43. 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)
44. 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)
45. 52.992 185.472 ↑ 1.0 1 26,496

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

46. 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
47. 0.000 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)

48. 31.332 79.488 ↑ 1.0 1 26,496

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

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