explain.depesz.com

PostgreSQL's explain analyze made readable

Result: upsD

Settings
# exclusive inclusive rows x rows loops node
1. 2,184.426 68,112.698 ↓ 0.0 0 1

Update on precursorchrominfo (cost=1,887.18..128,225,713.02 rows=44,366 width=575) (actual time=68,112.698..68,112.698 rows=0 loops=1)

2. 116.802 65,928.272 ↑ 1.0 43,377 1

Bitmap Heap Scan on precursorchrominfo (cost=1,887.18..128,225,713.02 rows=44,366 width=575) (actual time=12.932..65,928.272 rows=43,377 loops=1)

  • Recheck Cond: ((id >= 150000) AND (id <= 200000))
  • Heap Blocks: exact=6466
3. 8.561 8.561 ↓ 1.4 61,431 1

Bitmap Index Scan on pk_precursorchrominfo (cost=0.00..1,876.09 rows=44,366 width=0) (actual time=8.561..8.561 rows=61,431 loops=1)

  • Index Cond: ((id >= 150000) AND (id <= 200000))
4.          

SubPlan (for Bitmap Heap Scan)

5. 86.754 65,802.909 ↑ 1.0 1 43,377

Subquery Scan on x (cost=2,888.46..2,888.49 rows=1 width=4) (actual time=1.517..1.517 rows=1 loops=43,377)

6. 0.000 65,716.155 ↑ 1.0 1 43,377

Aggregate (cost=2,888.46..2,888.47 rows=1 width=4) (actual time=1.515..1.515 rows=1 loops=43,377)

7.          

Initplan (for Aggregate)

8. 43.377 390.393 ↑ 1.0 1 43,377

Nested Loop Left Join (cost=1.15..17.31 rows=1 width=250) (actual time=0.008..0.009 rows=1 loops=43,377)

9. 43.377 303.639 ↑ 1.0 1 43,377

Nested Loop Left Join (cost=0.87..17.00 rows=1 width=235) (actual time=0.006..0.007 rows=1 loops=43,377)

10. 0.000 173.508 ↑ 1.0 1 43,377

Nested Loop (cost=0.58..16.63 rows=1 width=222) (actual time=0.004..0.004 rows=1 loops=43,377)

11. 86.754 86.754 ↑ 1.0 1 43,377

Index Scan using pk_precursor on generalprecursor gp2 (cost=0.29..8.31 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=43,377)

  • Index Cond: (id = precursorchrominfo.precursorid)
12. 86.754 86.754 ↑ 1.0 1 43,377

Index Scan using pk_gmid on generalmolecule gm2 (cost=0.29..8.31 rows=1 width=222) (actual time=0.001..0.002 rows=1 loops=43,377)

  • Index Cond: (id = gp2.generalmoleculeid)
13. 86.754 86.754 ↑ 1.0 1 43,377

Index Scan using pk_peptideid on peptide p2 (cost=0.29..0.36 rows=1 width=17) (actual time=0.001..0.002 rows=1 loops=43,377)

  • Index Cond: (id = gm2.id)
14. 43.377 43.377 ↓ 0.0 0 43,377

Index Scan using pk_molecule on molecule m2 (cost=0.28..0.29 rows=1 width=23) (actual time=0.001..0.001 rows=0 loops=43,377)

  • Index Cond: (gm2.id = id)
15. 2,862.623 65,672.778 ↓ 2.0 2 43,377

Hash Left Join (cost=28.80..2,871.15 rows=1 width=4) (actual time=0.786..1.514 rows=2 loops=43,377)

  • Hash Cond: (gm.id = m.id)
  • Filter: ((COALESCE(gm.attributegroupid, p.sequence, m.customionname, m.ionformula))::text = ($4)::text)
  • Rows Removed by Filter: 228
16. 7,731.855 62,809.896 ↑ 1.2 230 43,377

Nested Loop Left Join (cost=1.29..2,842.56 rows=275 width=239) (actual time=0.067..1.448 rows=230 loops=43,377)

17. 7,861.986 45,112.080 ↑ 1.2 230 43,377

Nested Loop (cost=1.00..2,741.42 rows=275 width=230) (actual time=0.065..1.040 rows=230 loops=43,377)

18. 8,469.264 27,284.133 ↑ 1.2 230 43,377

Nested Loop (cost=0.71..2,644.50 rows=275 width=8) (actual time=0.062..0.629 rows=230 loops=43,377)

19. 8,848.908 8,848.908 ↑ 1.2 230 43,377

Index Scan using ix_precursorchrominfo_samplefileid on precursorchrominfo pci (cost=0.42..1,040.50 rows=275 width=8) (actual time=0.059..0.204 rows=230 loops=43,377)

  • Index Cond: (samplefileid = precursorchrominfo.samplefileid)
20. 9,965.961 9,965.961 ↑ 1.0 1 9,965,961

Index Scan using pk_precursor on generalprecursor gp (cost=0.29..5.82 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=9,965,961)

  • Index Cond: (id = pci.precursorid)
21. 9,965.961 9,965.961 ↑ 1.0 1 9,965,961

Index Scan using pk_gmid on generalmolecule gm (cost=0.29..0.34 rows=1 width=222) (actual time=0.001..0.001 rows=1 loops=9,965,961)

  • Index Cond: (id = gp.generalmoleculeid)
22. 9,965.961 9,965.961 ↑ 1.0 1 9,965,961

Index Scan using pk_peptideid on peptide p (cost=0.29..0.36 rows=1 width=17) (actual time=0.001..0.001 rows=1 loops=9,965,961)

  • Index Cond: (id = gp.generalmoleculeid)
23. 0.155 0.259 ↓ 1.0 883 1

Hash (cost=16.67..16.67 rows=867 width=23) (actual time=0.259..0.259 rows=883 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 47kB
24. 0.104 0.104 ↓ 1.0 883 1

Seq Scan on molecule m (cost=0.00..16.67 rows=867 width=23) (actual time=0.004..0.104 rows=883 loops=1)

Planning time : 1.512 ms
Execution time : 68,113.072 ms