explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ELtm

Settings
# exclusive inclusive rows x rows loops node
1. 32.926 1,909.465 ↓ 67.4 26,496 1

Sort (cost=184,182.95..184,183.93 rows=393 width=122) (actual time=1,903.348..1,909.465 rows=26,496 loops=1)

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

CTE wqx

3. 29.686 45.734 ↑ 1.0 26,496 1

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

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

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

  • Filter: (query_id = 322)
5. 97.736 1,876.539 ↓ 67.4 26,496 1

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

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

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

7. 0.015 0.062 ↑ 1.0 1 1

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

8. 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)
9. 0.018 0.018 ↑ 1.0 1 1

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

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

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

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

12. 7.760 249.311 ↓ 67.4 26,496 1

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

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

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

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

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

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

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

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

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

17. 52.335 61.617 ↑ 1.2 26,496 1

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

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

  • Recheck Cond: (screen_result_id = 169)
  • Heap Blocks: exact=3605
19. 2.170 2.170 ↑ 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.170..2.170 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.369 0.793 ↑ 1.0 338 1

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

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

Seq Scan on library (cost=0.00..22.38 rows=338 width=15) (actual time=0.019..0.424 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. 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)

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

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

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)