explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Lc6y

Settings
# exclusive inclusive rows x rows loops node
1. 33.115 1,974.324 ↓ 69.0 26,496 1

Sort (cost=189,247.59..189,248.55 rows=384 width=122) (actual time=1,969.719..1,974.324 rows=26,496 loops=1)

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

CTE wqx

3. 17.618 27.733 ↓ 1.0 26,496 1

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

  • Sort Key: well_query_index.id
  • Sort Method: quicksort Memory: 2011kB
4. 6.382 10.115 ↓ 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=3.773..10.115 rows=26,496 loops=1)

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

  • Index Cond: (query_id = 254)
6. 120.739 1,941.209 ↓ 69.0 26,496 1

Hash Join (cost=107,963.38..180,173.66 rows=384 width=122) (actual time=373.765..1,941.209 rows=26,496 loops=1)

  • Hash Cond: (well.library_id = library.library_id)
7. 3.688 574.882 ↓ 69.0 26,496 1

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

8. 0.004 0.022 ↑ 1.0 1 1

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

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

  • Index Cond: (screen_result_id = 169)
10. 0.008 0.008 ↑ 1.0 1 1

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

  • Index Cond: (screen_id = screen_result.screen_id)
11. 228.451 571.172 ↓ 69.0 26,496 1

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

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

13. 8.949 250.988 ↓ 69.0 26,496 1

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

  • Buckets: 32768 (originally 1024) Batches: 1 (originally 1) Memory Usage: 3031kB
14. 6.412 242.039 ↓ 69.0 26,496 1

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

  • Join Filter: (wqx.well_id = well.well_id)
15. 17.652 129.643 ↓ 8.3 26,496 1

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

  • Merge Cond: (wqx.well_id = assay_well.well_id)
16. 13.636 48.284 ↓ 1.0 26,496 1

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

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

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

18. 53.474 63.707 ↑ 1.2 26,496 1

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

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

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

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

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

  • Index Cond: (well_id = assay_well.well_id)
22. 0.122 0.276 ↑ 1.0 338 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 25kB
23. 0.154 0.154 ↑ 1.0 338 1

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

24.          

SubPlan (forHash Join)

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

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

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

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

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

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

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

44. 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)
45. 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.002..0.002 rows=1 loops=21,660)

  • Index Cond: (gene_id = silencing_reagent.vendor_gene_id)
46. 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)

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

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

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