explain.depesz.com

PostgreSQL's explain analyze made readable

Result: vplK

Settings
# exclusive inclusive rows x rows loops node
1. 52.247 1,146.086 ↓ 13.3 2,299 1

GroupAggregate (cost=187,643.21..187,649.26 rows=173 width=21) (actual time=1,087.128..1,146.086 rows=2,299 loops=1)

  • Group Key: cpgm.gene, e.cell_type
  • Filter: ((count(DISTINCT e.label) >= 2) AND (percentile_cont('0.5'::double precision) WITHIN GROUP (ORDER BY ((cpgm.avg_cvangle_gene_pval)::double precision)) < '0.01'::double precision))
  • Rows Removed by Filter: 6,160
2. 98.232 1,093.839 ↓ 108.4 18,747 1

Sort (cost=187,643.21..187,643.64 rows=173 width=40) (actual time=1,087.039..1,093.839 rows=18,747 loops=1)

  • Sort Key: cpgm.gene, e.cell_type
  • Sort Method: quicksort Memory: 2,239kB
3. 22.032 995.607 ↓ 108.4 18,747 1

Hash Join (cost=157,097.53..187,636.78 rows=173 width=40) (actual time=912.324..995.607 rows=18,747 loops=1)

  • Hash Cond: ((cpgm.experiment_label)::text = (e.label)::text)
4. 22.192 646.828 ↓ 2.9 25,363 1

Nested Loop (cost=16,585.94..47,090.42 rows=8,810 width=34) (actual time=584.712..646.828 rows=25,363 loops=1)

5. 1.222 585.191 ↓ 13.2 805 1

Unique (cost=16,580.45..16,580.81 rows=61 width=353) (actual time=582.936..585.191 rows=805 loops=1)

6. 7.596 583.969 ↓ 28.6 2,086 1

Sort (cost=16,580.45..16,580.63 rows=73 width=353) (actual time=582.934..583.969 rows=2,086 loops=1)

  • Sort Key: all_sets_per_experiment.experiment_label, (COALESCE(all_sets_per_experiment.is_active, false)) DESC, (COALESCE(all_sets_per_experiment.is_active, true)) DESC, all_sets_per_experiment.created_at DESC
  • Sort Method: quicksort Memory: 390kB
7. 36.275 576.373 ↓ 28.6 2,086 1

Subquery Scan on all_sets_per_experiment (cost=15,849.27..16,578.19 rows=73 width=353) (actual time=510.724..576.373 rows=2,086 loops=1)

  • Filter: (((all_sets_per_experiment.is_active IS NULL) OR all_sets_per_experiment.is_active) AND ((all_sets_per_experiment.labels -> 'use_embeddings'::text) = 'true'::jsonb))
  • Rows Removed by Filter: 27,455
8. 15.155 540.098 ↓ 1.0 29,541 1

Unique (cost=15,849.27..16,140.56 rows=29,175 width=351) (actual time=510.652..540.098 rows=29,541 loops=1)

9.          

CTE latest_labels

10. 27.686 158.199 ↑ 1.2 31,910 1

Unique (cost=8,056.18..8,408.12 rows=37,583 width=153) (actual time=99.304..158.199 rows=31,910 loops=1)

11. 103.308 130.513 ↑ 1.2 58,497 1

Sort (cost=8,056.18..8,232.15 rows=70,388 width=153) (actual time=99.301..130.513 rows=58,497 loops=1)

  • Sort Key: result_set_labels.result_set_id, result_set_labels.created_at DESC
  • Sort Method: quicksort Memory: 20,061kB
12. 27.205 27.205 ↑ 1.2 58,497 1

Seq Scan on result_set_labels (cost=0.00..2,388.88 rows=70,388 width=153) (actual time=0.008..27.205 rows=58,497 loops=1)

13. 192.769 524.943 ↑ 1.3 30,485 1

Sort (cost=7,441.15..7,538.24 rows=38,839 width=351) (actual time=510.651..524.943 rows=30,485 loops=1)

  • Sort Key: ds.experiment_label, ds.result_set_id, rs.created_at DESC, ds.created_at DESC
  • Sort Method: quicksort Memory: 11,838kB
14. 37.086 332.174 ↑ 1.3 30,485 1

Hash Join (cost=2,447.95..4,480.60 rows=38,839 width=351) (actual time=212.412..332.174 rows=30,485 loops=1)

  • Hash Cond: (ll.result_set_id = ds.result_set_id)
15. 182.060 182.060 ↑ 1.2 31,910 1

CTE Scan on latest_labels ll (cost=0.00..751.66 rows=37,583 width=48) (actual time=99.308..182.060 rows=31,910 loops=1)

16. 23.844 113.028 ↓ 1.0 30,485 1

Hash (cost=2,071.08..2,071.08 rows=30,150 width=71) (actual time=113.028..113.028 rows=30,485 loops=1)

  • Buckets: 32,768 Batches: 1 Memory Usage: 3,433kB
17. 41.345 89.184 ↓ 1.0 30,485 1

Hash Join (cost=944.19..2,071.08 rows=30,150 width=71) (actual time=33.983..89.184 rows=30,485 loops=1)

  • Hash Cond: (ds.result_set_id = rs.id)
18. 13.931 13.931 ↓ 1.0 30,485 1

Seq Scan on dart_statuses ds (cost=0.00..717.50 rows=30,150 width=47) (actual time=0.013..13.931 rows=30,485 loops=1)

19. 18.682 33.908 ↓ 1.1 31,910 1

Hash (cost=585.75..585.75 rows=28,675 width=24) (actual time=33.908..33.908 rows=31,910 loops=1)

  • Buckets: 32,768 Batches: 1 Memory Usage: 2,002kB
20. 15.226 15.226 ↓ 1.1 31,910 1

Seq Scan on result_sets rs (cost=0.00..585.75 rows=28,675 width=24) (actual time=0.011..15.226 rows=31,910 loops=1)

21. 32.200 39.445 ↑ 4.5 32 805

Bitmap Heap Scan on crispr_phenoscreen_gene_metrics cpgm (cost=5.49..498.71 rows=144 width=50) (actual time=0.013..0.049 rows=32 loops=805)

  • Recheck Cond: (result_set_id = all_sets_per_experiment.result_set_id)
  • Filter: ((gene)::text !~~ '%%_control'::text)
  • Rows Removed by Filter: 2
  • Heap Blocks: exact=7,654
22. 7.245 7.245 ↑ 4.3 34 805

Bitmap Index Scan on ix_exploration_crispr_phenoscreen_gene_metrics_result_set_id (cost=0.00..5.46 rows=147 width=0) (actual time=0.009..0.009 rows=34 loops=805)

  • Index Cond: (result_set_id = all_sets_per_experiment.result_set_id)
23. 0.373 326.747 ↓ 1.9 494 1

Hash (cost=140,508.27..140,508.27 rows=266 width=29) (actual time=326.747..326.747 rows=494 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 39kB
24. 5.786 326.374 ↓ 1.9 494 1

Merge Join (cost=139,994.16..140,508.27 rows=266 width=29) (actual time=303.107..326.374 rows=494 loops=1)

  • Merge Cond: (e_1.id = e.id)
25. 25.451 316.955 ↑ 1.2 13,190 1

GroupAggregate (cost=139,181.97..139,495.89 rows=15,696 width=340) (actual time=276.386..316.955 rows=13,190 loops=1)

  • Group Key: e_1.id, et.label, b.seed_at
26. 37.227 291.504 ↓ 1.9 30,244 1

Sort (cost=139,181.97..139,221.21 rows=15,696 width=230) (actual time=276.376..291.504 rows=30,244 loops=1)

  • Sort Key: e_1.id, et.label, b.seed_at
  • Sort Method: quicksort Memory: 2,908kB
27. 20.318 254.277 ↓ 1.9 30,585 1

Hash Left Join (cost=4,480.54..138,088.11 rows=15,696 width=230) (actual time=29.744..254.277 rows=30,585 loops=1)

  • Hash Cond: (e_1.batch_id = b.id)
28. 19.121 233.001 ↓ 1.9 30,585 1

Hash Left Join (cost=4,436.52..137,917.88 rows=15,696 width=226) (actual time=28.779..233.001 rows=30,585 loops=1)

  • Hash Cond: (e_1.experiment_type_id = et.id)
29. 22.295 213.867 ↓ 1.9 30,585 1

Hash Right Join (cost=4,419.32..137,684.86 rows=15,696 width=12) (actual time=28.753..213.867 rows=30,585 loops=1)

  • Hash Cond: ((j.experiment_label)::text = (e_1.label)::text)
30. 21.825 177.593 ↓ 1.5 22,791 1

Hash Right Join (cost=3,217.96..136,267.68 rows=15,696 width=28) (actual time=14.747..177.593 rows=22,791 loops=1)

  • Hash Cond: (jc.job_id = j.id)
  • Join Filter: (NOT (SubPlan 2))
  • Rows Removed by Join Filter: 91
31. 8.928 8.928 ↓ 1.1 12,211 1

Seq Scan on job_events jc (cost=0.00..1,373.69 rows=11,393 width=16) (actual time=0.017..8.928 rows=12,211 loops=1)

  • Filter: ((event)::text = 'experiment_evaluation_pipeline_completed'::text)
  • Rows Removed by Filter: 40,072
32. 5.738 14.664 ↑ 1.0 15,448 1

Hash (cost=3,021.76..3,021.76 rows=15,696 width=36) (actual time=14.664..14.664 rows=15,448 loops=1)

  • Buckets: 16,384 Batches: 1 Memory Usage: 1,227kB
33. 8.926 8.926 ↑ 1.0 15,448 1

Seq Scan on jobs j (cost=0.00..3,021.76 rows=15,696 width=36) (actual time=0.006..8.926 rows=15,448 loops=1)

  • Filter: (deleted_at IS NULL)
  • Rows Removed by Filter: 674
34.          

SubPlan (for Hash Right Join)

35. 132.176 132.176 ↓ 0.0 0 12,016

Index Scan using ix_ccc_job_events_job_id_event_time on job_events (cost=0.41..12.04 rows=1 width=0) (actual time=0.011..0.011 rows=0 loops=12,016)

  • Index Cond: ((job_id = j.id) AND (event_time >= jc.event_time))
  • Filter: ((event)::text = 'experiment_evaluation_pipeline_invalidated'::text)
  • Rows Removed by Filter: 31
36. 5.679 13.979 ↑ 1.0 13,527 1

Hash (cost=1,032.27..1,032.27 rows=13,527 width=35) (actual time=13.979..13.979 rows=13,527 loops=1)

  • Buckets: 16,384 Batches: 1 Memory Usage: 989kB
37. 8.300 8.300 ↑ 1.0 13,527 1

Seq Scan on experiments e_1 (cost=0.00..1,032.27 rows=13,527 width=35) (actual time=0.008..8.300 rows=13,527 loops=1)

38. 0.004 0.013 ↑ 32.0 10 1

Hash (cost=13.20..13.20 rows=320 width=222) (actual time=0.013..0.013 rows=10 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
39. 0.009 0.009 ↑ 32.0 10 1

Seq Scan on experiment_types et (cost=0.00..13.20 rows=320 width=222) (actual time=0.004..0.009 rows=10 loops=1)

40. 0.503 0.958 ↓ 1.0 1,675 1

Hash (cost=24.01..24.01 rows=1,601 width=8) (actual time=0.958..0.958 rows=1,675 loops=1)

  • Buckets: 2,048 Batches: 1 Memory Usage: 82kB
41. 0.455 0.455 ↓ 1.0 1,675 1

Seq Scan on batches b (cost=0.00..24.01 rows=1,601 width=8) (actual time=0.004..0.455 rows=1,675 loops=1)

42. 0.665 3.633 ↓ 2.2 494 1

Sort (cost=812.18..812.76 rows=229 width=33) (actual time=3.368..3.633 rows=494 loops=1)

  • Sort Key: e.id
  • Sort Method: quicksort Memory: 63kB
43. 1.071 2.968 ↓ 2.2 494 1

Bitmap Heap Scan on experiments e (cost=275.04..803.21 rows=229 width=33) (actual time=1.935..2.968 rows=494 loops=1)

  • Recheck Cond: (((assay_type)::text = '1536-crispr_lipofection'::text) AND ((experiment_team)::text = 'HTS'::text))
  • Filter: (pass_data_qc AND ((tags IS NULL) OR (NOT ('{DEVELOPMENT}'::character varying[] && tags))))
  • Rows Removed by Filter: 51
  • Heap Blocks: exact=239
44. 0.060 1.897 ↓ 0.0 0 1

BitmapAnd (cost=275.04..275.04 rows=230 width=0) (actual time=1.897..1.897 rows=0 loops=1)

45. 0.216 0.216 ↓ 1.0 1,337 1

Bitmap Index Scan on idx_experiment_assay_type (cost=0.00..34.01 rows=1,297 width=0) (actual time=0.216..0.216 rows=1,337 loops=1)

  • Index Cond: ((assay_type)::text = '1536-crispr_lipofection'::text)
46. 0.482 0.482 ↓ 1.0 3,896 1

Bitmap Index Scan on idx_experiment_pass_data_qc (cost=0.00..77.32 rows=3,871 width=0) (actual time=0.482..0.482 rows=3,896 loops=1)

  • Index Cond: (pass_data_qc = true)
47. 1.139 1.139 ↓ 1.0 8,392 1

Bitmap Index Scan on idx_experiment_experiment_team (cost=0.00..163.04 rows=8,367 width=0) (actual time=1.139..1.139 rows=8,392 loops=1)

  • Index Cond: ((experiment_team)::text = 'HTS'::text)