explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ND16

Settings
# exclusive inclusive rows x rows loops node
1. 172.395 5,143.487 ↑ 578.3 208,178 1

GroupAggregate (cost=134,704,606.22..820,108,973.55 rows=120,395,600 width=32) (actual time=279.375..5,143.487 rows=208,178 loops=1)

  • Output: p2.ms1_peak_id, pp.ms1_peak_id, count(pp.mz), ((count(pp.mz))::double precision / sqrt((((((count(ms2_ion.ms1_peak_id)))::double precision ^ '2'::double precision) + (((SubPlan 1))::double precision ^ '2'::double precision)) / '2'::double precision))), (count(ms2_ion.ms1_peak_id))
  • Group Key: p2.ms1_peak_id, pp.ms1_peak_id, (count(ms2_ion.ms1_peak_id))
2. 263.269 391.176 ↑ 2,068.3 321,842 1

Sort (cost=134,704,606.22..136,368,781.44 rows=665,670,089 width=24) (actual time=279.313..391.176 rows=321,842 loops=1)

  • Output: p2.ms1_peak_id, pp.ms1_peak_id, (count(ms2_ion.ms1_peak_id)), pp.mz
  • Sort Key: pp.ms1_peak_id, (count(ms2_ion.ms1_peak_id))
  • Sort Method: quicksort Memory: 37432kB
3. 34.652 127.907 ↑ 2,068.3 321,842 1

Nested Loop (cost=6.72..23,498,443.66 rows=665,670,089 width=24) (actual time=0.114..127.907 rows=321,842 loops=1)

  • Output: p2.ms1_peak_id, pp.ms1_peak_id, (count(ms2_ion.ms1_peak_id)), pp.mz
4. 0.022 0.133 ↓ 2.0 101 1

Nested Loop (cost=6.13..11.53 rows=50 width=20) (actual time=0.082..0.133 rows=101 loops=1)

  • Output: (count(ms2_ion.ms1_peak_id)), p2.ms1_peak_id, p2.mz
5. 0.021 0.075 ↑ 1.0 1 1

Aggregate (cost=5.57..5.58 rows=1 width=8) (actual time=0.075..0.075 rows=1 loops=1)

  • Output: count(ms2_ion.ms1_peak_id)
6. 0.054 0.054 ↓ 2.0 101 1

Index Only Scan using ms2_ion_ms1_peak_id_mz_idx on api.ms2_ion (cost=0.57..5.44 rows=50 width=4) (actual time=0.032..0.054 rows=101 loops=1)

  • Output: ms2_ion.ms1_peak_id, ms2_ion.mz
  • Index Cond: (ms2_ion.ms1_peak_id = 1383)
  • Heap Fetches: 0
7. 0.036 0.036 ↓ 2.0 101 1

Index Only Scan using ms2_ion_ms1_peak_id_mz_idx on api.ms2_ion p2 (cost=0.57..5.44 rows=50 width=12) (actual time=0.006..0.036 rows=101 loops=1)

  • Output: p2.ms1_peak_id, p2.mz
  • Index Cond: (p2.ms1_peak_id = 1383)
  • Heap Fetches: 0
8. 93.122 93.122 ↑ 4,177.4 3,187 101

Index Only Scan using ms2_ion_mz_idx on api.ms2_ion pp (cost=0.58..336,834.62 rows=13,313,402 width=12) (actual time=0.012..0.922 rows=3,187 loops=101)

  • Output: pp.mz, pp.ms1_peak_id
  • Index Cond: ((pp.mz >= (p2.mz - (('15'::double precision * p2.mz) / '1000000'::double precision))) AND (pp.mz <= (p2.mz + (('15'::double precision * p2.mz) / '1000000'::double precision))))
  • Heap Fetches: 0
9.          

SubPlan (forGroupAggregate)

10. 1,249.068 4,579.916 ↑ 1.0 1 208,178

Aggregate (cost=5.57..5.58 rows=1 width=8) (actual time=0.022..0.022 rows=1 loops=208,178)

  • Output: count(ms2_ion_1.ms1_peak_id)
11. 3,330.848 3,330.848 ↓ 2.0 98 208,178

Index Only Scan using ms2_ion_ms1_peak_id_mz_idx on api.ms2_ion ms2_ion_1 (cost=0.57..5.44 rows=50 width=4) (actual time=0.005..0.016 rows=98 loops=208,178)

  • Output: ms2_ion_1.ms1_peak_id, ms2_ion_1.mz
  • Index Cond: (ms2_ion_1.ms1_peak_id = pp.ms1_peak_id)
  • Heap Fetches: 0
Planning time : 0.342 ms
Execution time : 5,161.786 ms