explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 6LqQ

Settings
# exclusive inclusive rows x rows loops node
1. 1,415.459 24,254.098 ↑ 72.6 1,659,305 1

GroupAggregate (cost=134,704,606.22..820,108,973.55 rows=120,395,600 width=32) (actual time=3,330.444..24,254.098 rows=1,659,305 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. 3,167.045 4,586.284 ↑ 193.3 3,443,949 1

Sort (cost=134,704,606.22..136,368,781.44 rows=665,670,089 width=24) (actual time=3,330.367..4,586.284 rows=3,443,949 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: 367363kB
3. 367.907 1,419.239 ↑ 193.3 3,443,949 1

Nested Loop (cost=6.72..23,498,443.66 rows=665,670,089 width=24) (actual time=0.142..1,419.239 rows=3,443,949 loops=1)

  • Output: p2.ms1_peak_id, pp.ms1_peak_id, (count(ms2_ion.ms1_peak_id)), pp.mz
4. 0.081 0.236 ↓ 3.0 148 1

Nested Loop (cost=6.13..11.53 rows=50 width=20) (actual time=0.064..0.236 rows=148 loops=1)

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

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

  • Output: count(ms2_ion.ms1_peak_id)
6. 0.042 0.042 ↓ 3.0 148 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.029..0.042 rows=148 loops=1)

  • Output: ms2_ion.ms1_peak_id, ms2_ion.mz
  • Index Cond: (ms2_ion.ms1_peak_id = 1382)
  • Heap Fetches: 0
7. 0.100 0.100 ↓ 3.0 148 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.009..0.100 rows=148 loops=1)

  • Output: p2.ms1_peak_id, p2.mz
  • Index Cond: (p2.ms1_peak_id = 1382)
  • Heap Fetches: 0
8. 1,051.096 1,051.096 ↑ 572.1 23,270 148

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.032..7.102 rows=23,270 loops=148)

  • 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. 4,977.915 18,252.355 ↑ 1.0 1 1,659,305

Aggregate (cost=5.57..5.58 rows=1 width=8) (actual time=0.011..0.011 rows=1 loops=1,659,305)

  • Output: count(ms2_ion_1.ms1_peak_id)
11. 13,274.440 13,274.440 ↑ 1.1 45 1,659,305

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.003..0.008 rows=45 loops=1,659,305)

  • 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.337 ms
Execution time : 24,385.579 ms