explain.depesz.com

PostgreSQL's explain analyze made readable

Result: fhcL

Settings
# exclusive inclusive rows x rows loops node
1. 0.397 1,822.087 ↓ 2,783.0 2,783 1

Unique (cost=80,801.71..80,801.71 rows=1 width=4) (actual time=1,821.496..1,822.087 rows=2,783 loops=1)

2. 0.853 1,821.690 ↓ 3,080.0 3,080 1

Sort (cost=80,801.71..80,801.71 rows=1 width=4) (actual time=1,821.494..1,821.690 rows=3,080 loops=1)

  • Sort Key: v_no_ass3.id_caso
  • Sort Method: quicksort Memory: 241kB
3. 0.219 1,820.837 ↓ 3,080.0 3,080 1

Subquery Scan on v_no_ass3 (cost=80,801.68..80,801.70 rows=1 width=4) (actual time=1,819.086..1,820.837 rows=3,080 loops=1)

4. 15.983 1,820.618 ↓ 3,080.0 3,080 1

HashAggregate (cost=80,801.68..80,801.69 rows=1 width=95) (actual time=1,819.085..1,820.618 rows=3,080 loops=1)

5. 38.129 1,804.635 ↓ 7,804.0 7,804 1

Nested Loop (cost=69,996.48..80,801.62 rows=1 width=95) (actual time=857.885..1,804.635 rows=7,804 loops=1)

  • Join Filter: ((t_caso.tp_accesso)::text = (t_tp_accesso.tp_accesso)::text)
  • Rows Removed by Join Filter: 101452
6. 1.522 1,750.898 ↓ 7,804.0 7,804 1

Nested Loop (cost=69,996.48..80,800.23 rows=1 width=93) (actual time=857.867..1,750.898 rows=7,804 loops=1)

  • Join Filter: (t_caso_1.id_paziente = t_paziente.id_paziente)
7. 36.048 1,725.958 ↓ 7,806.0 7,806 1

Nested Loop (cost=69,996.05..80,793.74 rows=1 width=85) (actual time=857.859..1,725.958 rows=7,806 loops=1)

  • Join Filter: ((t_vetrino.id_acc_mat = t_caso_1.id_acc_mat) AND (t_vetrino.id_caso = t_caso_1.id_caso))
  • Rows Removed by Join Filter: 1370
8. 18.405 1,544.002 ↓ 72,954.0 72,954 1

Nested Loop Left Join (cost=69,995.63..80,787.19 rows=1 width=97) (actual time=857.637..1,544.002 rows=72,954 loops=1)

  • Filter: (c.dt_cancel IS NULL)
  • Rows Removed by Filter: 1
9. 70.310 1,379.687 ↓ 72,955.0 72,955 1

Nested Loop (cost=69,995.20..80,780.66 rows=1 width=85) (actual time=857.628..1,379.687 rows=72,955 loops=1)

  • Join Filter: (t_vetrino.id_acc_mat = t_caso.id_acc_mat)
10. 101.808 1,189.247 ↓ 60,065.0 60,065 1

Hash Join (cost=69,994.77..80,774.12 rows=1 width=47) (actual time=857.612..1,189.247 rows=60,065 loops=1)

  • Hash Cond: ((t_vetrino.id_acc_mat = t_prelievo.id_acc_mat) AND (b.id_prelievo = t_prelievo.id_prelievo))
11. 82.152 387.841 ↓ 2.4 60,073 1

Hash Join (cost=15,224.55..25,311.44 rows=25,180 width=41) (actual time=157.639..387.841 rows=60,073 loops=1)

  • Hash Cond: (t_vetrino.id_blocchetto = b.id_blocchetto)
12. 86.760 173.938 ↓ 2.5 66,434 1

Hash Left Join (cost=4,174.66..13,705.82 rows=27,016 width=35) (actual time=25.762..173.938 rows=66,434 loops=1)

  • Hash Cond: (t_vetrino.id_vetrino = t_vetrino_test.id_vetrino)
  • Filter: (((t_colorazione.tp_colorazione)::text = 'S'::text) OR ((t_colorazione.tp_colorazione)::text = 'D'::text) OR ((t_colorazione.tp_colorazione)::text = 'R'::text) OR ((t_colorazione.t (...)
13. 29.603 68.253 ↓ 2.4 66,434 1

Hash Left Join (cost=1,729.91..10,803.94 rows=27,386 width=36) (actual time=6.796..68.253 rows=66,434 loops=1)

  • Hash Cond: ((t_vetrino.id_colorazione)::text = (t_colorazione.id_colorazione)::text)
  • Filter: (((t_colorazione.tp_colorazione)::text <> 'R'::text) OR (t_vetrino.id_colorazione IS NULL))
14. 32.839 38.632 ↓ 2.4 66,434 1

Bitmap Heap Scan on t_vetrino (cost=1,727.88..10,499.90 rows=27,802 width=37) (actual time=6.765..38.632 rows=66,434 loops=1)

  • Recheck Cond: (user_consegna = 0)
  • Filter: ((dt_cancel IS NULL) AND (annullato IS NULL) AND (user_taglio = 0))
  • Rows Removed by Filter: 26233
15. 5.793 5.793 ↑ 1.0 92,667 1

Bitmap Index Scan on i_vetrino1 (cost=0.00..1,720.93 rows=92,868 width=0) (actual time=5.793..5.793 rows=92,667 loops=1)

  • Index Cond: (user_consegna = 0)
16. 0.007 0.018 ↑ 1.0 38 1

Hash (cost=1.55..1.55 rows=38 width=6) (actual time=0.018..0.018 rows=38 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 2kB
17. 0.011 0.011 ↑ 1.0 38 1

Seq Scan on t_colorazione (cost=0.00..1.55 rows=38 width=6) (actual time=0.005..0.011 rows=38 loops=1)

  • Filter: (dt_cancel IS NULL)
  • Rows Removed by Filter: 17
18. 9.282 18.925 ↑ 1.0 68,700 1

Hash (cost=1,586.00..1,586.00 rows=68,700 width=7) (actual time=18.925..18.925 rows=68,700 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 2684kB
19. 9.643 9.643 ↑ 1.0 68,700 1

Seq Scan on t_vetrino_test (cost=0.00..1,586.00 rows=68,700 width=7) (actual time=0.003..9.643 rows=68,700 loops=1)

20. 49.703 131.751 ↓ 1.0 242,151 1

Hash (cost=8,025.68..8,025.68 rows=241,937 width=14) (actual time=131.751..131.751 rows=242,151 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 11132kB
21. 82.048 82.048 ↓ 1.0 242,151 1

Seq Scan on t_blocchetto b (cost=0.00..8,025.68 rows=241,937 width=14) (actual time=0.006..82.048 rows=242,151 loops=1)

  • Filter: ((dt_cancel IS NULL) AND ((tp_blocchetto)::text <> 'P'::text))
  • Rows Removed by Filter: 17423
22. 287.109 699.598 ↓ 1.0 1,163,608 1

Hash (cost=37,351.25..37,351.25 rows=1,161,265 width=14) (actual time=699.598..699.598 rows=1,163,608 loops=1)

  • Buckets: 131072 Batches: 1 Memory Usage: 54523kB
23. 412.489 412.489 ↓ 1.0 1,163,608 1

Seq Scan on t_prelievo (cost=0.00..37,351.25 rows=1,161,265 width=14) (actual time=0.058..412.489 rows=1,163,608 loops=1)

  • Filter: ((dt_cancel IS NULL) AND ((cd_prelievo)::text <> '-'::text))
  • Rows Removed by Filter: 9692
24. 120.130 120.130 ↑ 1.0 1 60,065

Index Scan using t_caso2 on t_caso (cost=0.43..6.53 rows=1 width=38) (actual time=0.002..0.002 rows=1 loops=60,065)

  • Index Cond: (id_acc_mat = t_prelievo.id_acc_mat)
  • Filter: (dt_cancel IS NULL)
  • Rows Removed by Filter: 0
25. 145.910 145.910 ↑ 1.0 1 72,955

Index Scan using p_t_campionamento on t_campionamento c (cost=0.43..6.52 rows=1 width=24) (actual time=0.002..0.002 rows=1 loops=72,955)

  • Index Cond: (b.id_campionamento = id_campionamento)
26. 145.908 145.908 ↓ 0.0 0 72,954

Index Scan using t_caso2 on t_caso t_caso_1 (cost=0.43..6.53 rows=1 width=16) (actual time=0.002..0.002 rows=0 loops=72,954)

  • Index Cond: (id_acc_mat = t_caso.id_acc_mat)
  • Filter: ((dt_cancel IS NULL) AND (t_caso.id_paziente = id_paziente) AND (t_caso.id_caso = id_caso_p) AND ((fl_stato)::text <> ALL ('{I,P,V,R,Z,F}'::text[])))
  • Rows Removed by Filter: 1
27. 23.418 23.418 ↑ 1.0 1 7,806

Index Scan using p_t_paziente on t_paziente (cost=0.42..6.48 rows=1 width=20) (actual time=0.003..0.003 rows=1 loops=7,806)

  • Index Cond: (id_paziente = t_caso.id_paziente)
  • Filter: (dt_cancel IS NULL)
28. 15.608 15.608 ↑ 1.0 14 7,804

Seq Scan on t_tp_accesso (cost=0.00..1.21 rows=14 width=5) (actual time=0.001..0.002 rows=14 loops=7,804)

  • Filter: (dt_cancel IS NULL)
  • Rows Removed by Filter: 7
Total runtime : 1,834.874 ms