explain.depesz.com

PostgreSQL's explain analyze made readable

Result: U3N0

Settings
# exclusive inclusive rows x rows loops node
1. 420.247 7,895.469 ↑ 298.6 403,181 1

GroupAggregate (cost=134,704,606.22..820,108,973.55 rows=120,395,600 width=32) (actual time=433.635..7,895.469 rows=403,181 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. 427.987 621.145 ↑ 1,370.9 485,559 1

Sort (cost=134,704,606.22..136,368,781.44 rows=665,670,089 width=24) (actual time=433.554..621.145 rows=485,559 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: 50223kB
3. 51.717 193.158 ↑ 1,370.9 485,559 1

Nested Loop (cost=6.72..23,498,443.66 rows=665,670,089 width=24) (actual time=0.078..193.158 rows=485,559 loops=1)

  • Output: p2.ms1_peak_id, pp.ms1_peak_id, (count(ms2_ion.ms1_peak_id)), pp.mz
4. 0.011 0.066 ↑ 3.8 13 1

Nested Loop (cost=6.13..11.53 rows=50 width=20) (actual time=0.045..0.066 rows=13 loops=1)

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

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

  • Output: count(ms2_ion.ms1_peak_id)
6. 0.034 0.034 ↑ 3.8 13 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.033..0.034 rows=13 loops=1)

  • Output: ms2_ion.ms1_peak_id, ms2_ion.mz
  • Index Cond: (ms2_ion.ms1_peak_id = 2)
  • Heap Fetches: 0
7. 0.015 0.015 ↑ 3.8 13 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.004..0.015 rows=13 loops=1)

  • Output: p2.ms1_peak_id, p2.mz
  • Index Cond: (p2.ms1_peak_id = 2)
  • Heap Fetches: 0
8. 141.375 141.375 ↑ 356.4 37,351 13

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.022..10.875 rows=37,351 loops=13)

  • 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. 2,015.905 6,854.077 ↑ 1.0 1 403,181

Aggregate (cost=5.57..5.58 rows=1 width=8) (actual time=0.017..0.017 rows=1 loops=403,181)

  • Output: count(ms2_ion_1.ms1_peak_id)
11. 4,838.172 4,838.172 ↓ 1.5 73 403,181

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.004..0.012 rows=73 loops=403,181)

  • 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.347 ms
Execution time : 7,925.365 ms