explain.depesz.com

PostgreSQL's explain analyze made readable

Result: XcM3

Settings
# exclusive inclusive rows x rows loops node
1. 0.125 5,514.003 ↑ 3.0 1 1

Subquery Scan on ordered_view (cost=649,483.50..649,483.54 rows=3 width=569) (actual time=5,514.002..5,514.003 rows=1 loops=1)

2. 0.014 5,513.878 ↑ 3.0 1 1

Sort (cost=649,483.50..649,483.50 rows=3 width=545) (actual time=5,513.877..5,513.878 rows=1 loops=1)

  • Sort Key: summary_view.gene
  • Sort Method: quicksort Memory: 26kB
3. 0.118 5,513.864 ↑ 3.0 1 1

Nested Loop (cost=5.13..649,483.47 rows=3 width=545) (actual time=5,513.834..5,513.864 rows=1 loops=1)

  • Join Filter: (summary_view.callset_id = callset.callset_id)
  • Rows Removed by Join Filter: 135
4. 0.034 0.034 ↓ 1.0 136 1

Seq Scan on callset (cost=0.00..4.33 rows=133 width=96) (actual time=0.008..0.034 rows=136 loops=1)

5. 0.000 5,513.712 ↑ 3.0 1 136

Materialize (cost=5.13..649,473.16 rows=3 width=453) (actual time=40.542..40.542 rows=1 loops=136)

6. 0.001 5,513.733 ↑ 3.0 1 1

Subquery Scan on summary_view (cost=5.13..649,473.15 rows=3 width=453) (actual time=5,513.684..5,513.733 rows=1 loops=1)

7. 0.835 5,513.732 ↑ 3.0 1 1

Nested Loop (cost=5.13..649,473.12 rows=3 width=221) (actual time=5,513.683..5,513.732 rows=1 loops=1)

8. 0.058 5,458.982 ↑ 3.0 1 1

Nested Loop (cost=4.70..649,209.68 rows=3 width=193) (actual time=5,458.935..5,458.982 rows=1 loops=1)

  • Join Filter: (csc.callset_id = cs.callset_id)
  • Rows Removed by Join Filter: 135
9. 0.020 0.020 ↓ 1.0 136 1

Seq Scan on callset cs (cost=0.00..4.33 rows=133 width=13) (actual time=0.003..0.020 rows=136 loops=1)

10. 0.026 5,458.904 ↑ 3.0 1 136

Materialize (cost=4.70..649,199.37 rows=3 width=184) (actual time=38.363..40.139 rows=1 loops=136)

11. 0.341 5,458.878 ↑ 3.0 1 1

Nested Loop (cost=4.70..649,199.35 rows=3 width=184) (actual time=5,217.308..5,458.878 rows=1 loops=1)

12. 1.890 3.143 ↓ 133.0 133 1

Nested Loop (cost=4.14..331.69 rows=1 width=14) (actual time=0.169..3.143 rows=133 loops=1)

  • Join Filter: ((csq.participant_id = callset_qc_evaluation.participant_id) AND (csq.evaluation_timestamp = (max(callset_qc_evaluation.evaluation_timestamp))))
  • Rows Removed by Join Filter: 17,556
13. 0.189 0.189 ↑ 1.0 133 1

Index Scan using callset_qc_evaluation_callset_id_idx on callset_qc_evaluation csq (cost=0.14..15.15 rows=133 width=22) (actual time=0.009..0.189 rows=133 loops=1)

  • Filter: approved
14. 0.904 1.064 ↑ 1.0 133 133

Materialize (cost=4.00..7.32 rows=133 width=18) (actual time=0.001..0.008 rows=133 loops=133)

15. 0.126 0.160 ↑ 1.0 133 1

HashAggregate (cost=4.00..5.33 rows=133 width=18) (actual time=0.122..0.160 rows=133 loops=1)

  • Group Key: callset_qc_evaluation.participant_id
16. 0.034 0.034 ↑ 1.0 133 1

Seq Scan on callset_qc_evaluation (cost=0.00..3.33 rows=133 width=18) (actual time=0.006..0.034 rows=133 loops=1)

17. 5,453.969 5,455.394 ↓ 0.0 0 133

Index Scan using callset_call_pkey on callset_call csc (cost=0.56..648,867.63 rows=3 width=170) (actual time=40.793..41.018 rows=0 loops=133)

  • Index Cond: (callset_id = csq.callset_id)
  • Filter: (((verify)::integer = 1) AND (COALESCE((SubPlan 9), 0) = 0))
  • Rows Removed by Filter: 117,960
18.          

SubPlan (for Index Scan)

19. 0.057 1.425 ↑ 1.0 1 57

Limit (cost=5.07..5.07 rows=1 width=12) (actual time=0.025..0.025 rows=1 loops=57)

20. 0.399 1.368 ↑ 3.0 1 57

Sort (cost=5.07..5.07 rows=3 width=12) (actual time=0.024..0.024 rows=1 loops=57)

  • Sort Key: vc_2.datetime DESC
  • Sort Method: top-N heapsort Memory: 25kB
21. 0.969 0.969 ↓ 1.3 4 57

Seq Scan on variant_confirmation vc_2 (cost=0.00..5.05 rows=3 width=12) (actual time=0.008..0.017 rows=4 loops=57)

  • Filter: (var_id = csc.var_id)
  • Rows Removed by Filter: 177
22. 0.013 0.013 ↑ 1.0 1 1

Index Scan using normalized_variant_pkey on normalized_variant nv (cost=0.43..8.17 rows=1 width=28) (actual time=0.012..0.013 rows=1 loops=1)

  • Index Cond: (var_id = csc.var_id)
23.          

SubPlan (for Nested Loop)

24. 0.000 0.030 ↑ 1.0 1 1

Limit (cost=5.07..5.07 rows=1 width=12) (actual time=0.030..0.030 rows=1 loops=1)

25. 0.009 0.030 ↑ 3.0 1 1

Sort (cost=5.07..5.07 rows=3 width=12) (actual time=0.030..0.030 rows=1 loops=1)

  • Sort Key: vc.datetime DESC
  • Sort Method: quicksort Memory: 25kB
26. 0.021 0.021 ↑ 3.0 1 1

Seq Scan on variant_confirmation vc (cost=0.00..5.05 rows=3 width=12) (actual time=0.021..0.021 rows=1 loops=1)

  • Filter: (var_id = csc.var_id)
  • Rows Removed by Filter: 180
27. 0.001 0.153 ↑ 1.0 1 1

Limit (cost=5.07..5.08 rows=1 width=8) (actual time=0.153..0.153 rows=1 loops=1)

28. 0.002 0.152 ↑ 3.0 1 1

Sort (cost=5.07..5.08 rows=3 width=8) (actual time=0.152..0.152 rows=1 loops=1)

  • Sort Key: vc_1.datetime DESC
  • Sort Method: quicksort Memory: 25kB
29. 0.150 0.150 ↑ 3.0 1 1

Seq Scan on variant_confirmation vc_1 (cost=0.00..5.06 rows=3 width=8) (actual time=0.148..0.150 rows=1 loops=1)

  • Filter: (var_id = csc.var_id)
  • Rows Removed by Filter: 180
30. 0.001 0.015 ↓ 0.0 0 1

Limit (cost=1.28..1.28 rows=1 width=12) (actual time=0.015..0.015 rows=0 loops=1)

31. 0.004 0.014 ↓ 0.0 0 1

Sort (cost=1.28..1.28 rows=1 width=12) (actual time=0.014..0.014 rows=0 loops=1)

  • Sort Key: pvft.datetime DESC
  • Sort Method: quicksort Memory: 25kB
32. 0.010 0.010 ↓ 0.0 0 1

Seq Scan on participant_variant_finding_tier pvft (cost=0.00..1.27 rows=1 width=12) (actual time=0.010..0.010 rows=0 loops=1)

  • Filter: ((var_id = csc.var_id) AND (participant_id = csq.participant_id))
  • Rows Removed by Filter: 29
33. 0.001 0.041 ↑ 1.0 1 1

Limit (cost=9.43..9.43 rows=1 width=10) (actual time=0.041..0.041 rows=1 loops=1)

34. 0.003 0.040 ↑ 1.0 1 1

Sort (cost=9.43..9.43 rows=1 width=10) (actual time=0.040..0.040 rows=1 loops=1)

  • Sort Key: vi.created_at DESC
  • Sort Method: quicksort Memory: 25kB
35. 0.007 0.037 ↑ 1.0 1 1

Nested Loop (cost=0.29..9.42 rows=1 width=10) (actual time=0.036..0.037 rows=1 loops=1)

  • Join Filter: (vi.interpretation = ic.id)
  • Rows Removed by Join Filter: 4
36. 0.027 0.027 ↑ 1.0 1 1

Index Scan using variant_interpretation_var_id_idx on variant_interpretation vi (cost=0.29..8.30 rows=1 width=12) (actual time=0.027..0.027 rows=1 loops=1)

  • Index Cond: (var_id = nv.var_id)
37. 0.003 0.003 ↑ 1.0 5 1

Seq Scan on acmg_interpretation_code ic (cost=0.00..1.05 rows=5 width=6) (actual time=0.002..0.003 rows=5 loops=1)

38. 0.002 0.008 ↑ 1.0 1 1

Aggregate (cost=9.42..9.43 rows=1 width=0) (actual time=0.008..0.008 rows=1 loops=1)

39. 0.002 0.006 ↑ 1.0 1 1

Nested Loop (cost=0.29..9.42 rows=1 width=0) (actual time=0.005..0.006 rows=1 loops=1)

  • Join Filter: (vi_1.interpretation = aic.id)
  • Rows Removed by Join Filter: 4
40. 0.002 0.002 ↑ 1.0 1 1

Index Scan using variant_interpretation_var_id_idx on variant_interpretation vi_1 (cost=0.29..8.30 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=1)

  • Index Cond: (var_id = csc.var_id)
41. 0.002 0.002 ↑ 1.0 5 1

Seq Scan on acmg_interpretation_code aic (cost=0.00..1.05 rows=5 width=4) (actual time=0.001..0.002 rows=5 loops=1)

42. 0.005 0.017 ↑ 1.0 1 1

Aggregate (cost=9.93..9.94 rows=1 width=5) (actual time=0.017..0.017 rows=1 loops=1)

43. 0.012 0.012 ↑ 2.0 1 1

Index Scan using analysis_note_var_id_idx on analysis_note (cost=0.28..9.92 rows=2 width=5) (actual time=0.012..0.012 rows=1 loops=1)

  • Index Cond: (var_id = csc.var_id)
44. 0.005 0.155 ↑ 1.0 1 1

Aggregate (cost=25.26..25.27 rows=1 width=5) (actual time=0.155..0.155 rows=1 loops=1)

45. 0.126 0.150 ↓ 2.0 2 1

Bitmap Heap Scan on analysis_note analysis_note_1 (cost=4.33..25.26 rows=1 width=5) (actual time=0.036..0.150 rows=2 loops=1)

  • Recheck Cond: (gene = csc.gene)
  • Filter: (var_id IS NULL)
  • Rows Removed by Filter: 63
  • Heap Blocks: exact=48
46. 0.024 0.024 ↓ 10.8 65 1

Bitmap Index Scan on analysis_note_gene_idx (cost=0.00..4.33 rows=6 width=0) (actual time=0.024..0.024 rows=65 loops=1)

  • Index Cond: (gene = csc.gene)
47. 53.483 53.483 ↑ 3.0 1 1

Index Only Scan Backward using transcript_depth_of_coverage_callset_id_gene_mean_idx on transcript_depth_of_coverage doc (cost=0.56..13.85 rows=3 width=8) (actual time=53.481..53.483 rows=1 loops=1)

  • Index Cond: ((callset_id = csc.callset_id) AND (gene = csc.gene))
  • Heap Fetches: 1
Planning time : 3.532 ms
Execution time : 5,514.372 ms