explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Usmm

Settings
# exclusive inclusive rows x rows loops node
1. 0.811 22,158.597 ↓ 1,175.7 3,527 1

Sort (cost=749,374.60..749,374.61 rows=3 width=4) (actual time=22,158.477..22,158.597 rows=3,527 loops=1)

  • Sort Key: v_no_ass2.id_caso
  • Sort Method: quicksort Memory: 262kB
2. 1.495 22,157.786 ↓ 1,175.7 3,527 1

HashAggregate (cost=749,374.55..749,374.58 rows=3 width=4) (actual time=22,157.525..22,157.786 rows=3,527 loops=1)

3. 0.252 22,156.291 ↓ 2,258.3 6,775 1

Append (cost=261,139.44..749,374.54 rows=3 width=4) (actual time=15,902.908..22,156.291 rows=6,775 loops=1)

4. 0.268 15,902.961 ↓ 691.0 691 1

HashAggregate (cost=261,139.44..261,139.45 rows=1 width=4) (actual time=15,902.907..15,902.961 rows=691 loops=1)

5. 0.065 15,902.693 ↓ 919.0 919 1

Subquery Scan on v_no_ass2 (cost=261,139.42..261,139.44 rows=1 width=4) (actual time=15,902.231..15,902.693 rows=919 loops=1)

6. 3.709 15,902.628 ↓ 919.0 919 1

HashAggregate (cost=261,139.42..261,139.43 rows=1 width=78) (actual time=15,902.230..15,902.628 rows=919 loops=1)

7. 0.548 15,898.919 ↓ 1,915.0 1,915 1

Nested Loop (cost=11,353.22..261,139.37 rows=1 width=78) (actual time=2,274.927..15,898.919 rows=1,915 loops=1)

8. 7.947 15,472.797 ↓ 1,917.0 1,917 1

Nested Loop (cost=11,352.80..261,133.75 rows=1 width=66) (actual time=2,274.891..15,472.797 rows=1,917 loops=1)

  • Join Filter: ((t_caso.tp_accesso)::text = (t_tp_accesso.tp_accesso)::text)
  • Rows Removed by Join Filter: 24921
9. 0.761 15,461.016 ↓ 1,917.0 1,917 1

Nested Loop (cost=11,352.80..261,132.37 rows=1 width=64) (actual time=2,274.873..15,461.016 rows=1,917 loops=1)

10. 1.087 15,270.373 ↓ 1,918.0 1,918 1

Nested Loop Left Join (cost=11,352.37..261,127.22 rows=1 width=62) (actual time=2,260.305..15,270.373 rows=1,918 loops=1)

  • Filter: (c.dt_cancel IS NULL)
11. 330.368 14,870.342 ↓ 1,918.0 1,918 1

Hash Join (cost=11,351.94..261,122.04 rows=1 width=50) (actual time=2,237.777..14,870.342 rows=1,918 loops=1)

  • Hash Cond: (t_caso.id_acc_mat = b.id_acc_mat)
  • Join Filter: (((t_caso.fl_stato)::text <> ALL ('{I,P,V,R,Z,F}'::text[])) OR ((b.splittato)::text = 'S'::text))
  • Rows Removed by Join Filter: 4961
12. 14,440.941 14,440.941 ↓ 198.7 1,738,800 1

Seq Scan on t_caso (cost=0.00..248,407.74 rows=8,751 width=40) (actual time=9.597..14,440.941 rows=1,738,800 loops=1)

  • Filter: ((dt_cancel IS NULL) AND (id_caso_p = id_caso))
  • Rows Removed by Filter: 11996
13. 1.918 99.033 ↑ 8.5 6,879 1

Hash (cost=10,621.42..10,621.42 rows=58,442 width=28) (actual time=99.033..99.033 rows=6,879 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 349kB
14. 97.115 97.115 ↑ 8.5 6,879 1

Seq Scan on t_blocchetto b (cost=0.00..10,621.42 rows=58,442 width=28) (actual time=0.037..97.115 rows=6,879 loops=1)

  • Filter: ((dt_cancel IS NULL) AND (((fl_estemporanea)::text <> 'S'::text) OR (fl_estemporanea IS NULL)) AND ((tp_inclusione IS NULL) OR ((tp_inclusione)::text <> 'O'::text)) AND (user (...)
  • Rows Removed by Filter: 252695
15. 398.944 398.944 ↑ 1.0 1 1,918

Index Scan using p_t_campionamento on t_campionamento c (cost=0.43..5.16 rows=1 width=24) (actual time=0.208..0.208 rows=1 loops=1,918)

  • Index Cond: (b.id_campionamento = id_campionamento)
16. 189.882 189.882 ↑ 1.0 1 1,918

Index Scan using p_t_prelievo on t_prelievo (cost=0.43..5.14 rows=1 width=10) (actual time=0.099..0.099 rows=1 loops=1,918)

  • Index Cond: (id_prelievo = b.id_prelievo)
  • Filter: (dt_cancel IS NULL)
  • Rows Removed by Filter: 0
17. 3.834 3.834 ↑ 1.0 14 1,917

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

  • Filter: (dt_cancel IS NULL)
  • Rows Removed by Filter: 7
18. 425.574 425.574 ↑ 1.0 1 1,917

Index Scan using p_t_paziente on t_paziente (cost=0.42..5.61 rows=1 width=20) (actual time=0.222..0.222 rows=1 loops=1,917)

  • Index Cond: (id_paziente = t_caso.id_paziente)
  • Filter: (dt_cancel IS NULL)
19. 0.983 4,079.721 ↓ 2,783.0 2,783 1

HashAggregate (cost=211,177.49..211,177.50 rows=1 width=4) (actual time=4,079.390..4,079.721 rows=2,783 loops=1)

20. 0.194 4,078.738 ↓ 3,080.0 3,080 1

Subquery Scan on v_no_ass3 (cost=211,177.47..211,177.49 rows=1 width=4) (actual time=4,077.224..4,078.738 rows=3,080 loops=1)

21. 18.635 4,078.544 ↓ 3,080.0 3,080 1

HashAggregate (cost=211,177.47..211,177.48 rows=1 width=99) (actual time=4,077.223..4,078.544 rows=3,080 loops=1)

22. 3.099 4,059.909 ↓ 7,804.0 7,804 1

Nested Loop Left Join (cost=4,177.21..211,177.41 rows=1 width=99) (actual time=99.912..4,059.909 rows=7,804 loops=1)

  • Filter: (c_1.dt_cancel IS NULL)
23. 0.000 2,878.406 ↓ 7,804.0 7,804 1

Nested Loop (cost=4,176.78..211,170.88 rows=1 width=87) (actual time=99.903..2,878.406 rows=7,804 loops=1)

24. 2.544 2,464.842 ↓ 7,804.0 7,804 1

Nested Loop (cost=4,176.36..211,164.36 rows=1 width=85) (actual time=99.890..2,464.842 rows=7,804 loops=1)

25. 39.784 1,919.712 ↓ 8,221.0 8,221 1

Nested Loop (cost=4,175.94..211,157.84 rows=1 width=79) (actual time=82.238..1,919.712 rows=8,221 loops=1)

  • Join Filter: ((t_caso_1.tp_accesso)::text = (t_tp_accesso_1.tp_accesso)::text)
  • Rows Removed by Join Filter: 106873
26. 5.687 1,863.486 ↓ 8,221.0 8,221 1

Nested Loop (cost=4,175.94..211,156.46 rows=1 width=77) (actual time=82.225..1,863.486 rows=8,221 loops=1)

27. 9.975 1,093.060 ↓ 8,223.0 8,223 1

Nested Loop (cost=4,175.51..211,149.66 rows=1 width=65) (actual time=82.214..1,093.060 rows=8,223 loops=1)

  • Join Filter: (t_caso_2.id_caso_p = t_caso_1.id_caso)
  • Rows Removed by Join Filter: 3332
28. 0.000 688.189 ↓ 95.7 8,227 1

Nested Loop (cost=4,175.09..210,523.56 rows=86 width=43) (actual time=77.697..688.189 rows=8,227 loops=1)

29. 83.406 224.561 ↓ 2.5 66,434 1

Hash Left Join (cost=4,174.66..13,705.82 rows=27,016 width=39) (actual time=72.293..224.561 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_c (...)
30. 30.640 79.207 ↓ 2.4 66,434 1

Hash Left Join (cost=1,729.91..10,803.94 rows=27,386 width=40) (actual time=10.295..79.207 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))
31. 39.506 48.535 ↓ 2.4 66,434 1

Bitmap Heap Scan on t_vetrino (cost=1,727.88..10,499.90 rows=27,802 width=41) (actual time=10.242..48.535 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
32. 9.029 9.029 ↑ 1.0 92,667 1

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

  • Index Cond: (user_consegna = 0)
33. 0.013 0.032 ↑ 1.0 38 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 2kB
34. 0.019 0.019 ↑ 1.0 38 1

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

  • Filter: (dt_cancel IS NULL)
  • Rows Removed by Filter: 17
35. 11.234 61.948 ↑ 1.0 68,700 1

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

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

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

37. 465.038 465.038 ↓ 0.0 0 66,434

Index Scan using p_t_caso on t_caso t_caso_2 (cost=0.43..7.28 rows=1 width=12) (actual time=0.007..0.007 rows=0 loops=66,434)

  • Index Cond: (id_caso = t_vetrino.id_caso)
  • Filter: ((dt_cancel IS NULL) AND ((fl_stato)::text <> ALL ('{I,P,V,R,Z,F}'::text[])))
  • Rows Removed by Filter: 1
38. 394.896 394.896 ↑ 1.0 1 8,227

Index Scan using t_caso2 on t_caso t_caso_1 (cost=0.43..7.27 rows=1 width=34) (actual time=0.048..0.048 rows=1 loops=8,227)

  • Index Cond: (id_acc_mat = t_vetrino.id_acc_mat)
  • Filter: (dt_cancel IS NULL)
  • Rows Removed by Filter: 0
39. 764.739 764.739 ↑ 1.0 1 8,223

Index Scan using p_t_paziente on t_paziente t_paziente_1 (cost=0.42..6.79 rows=1 width=20) (actual time=0.093..0.093 rows=1 loops=8,223)

  • Index Cond: (id_paziente = t_caso_2.id_paziente)
  • Filter: (dt_cancel IS NULL)
40. 16.442 16.442 ↑ 1.0 14 8,221

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

  • Filter: (dt_cancel IS NULL)
  • Rows Removed by Filter: 7
41. 542.586 542.586 ↑ 1.0 1 8,221

Index Scan using p_t_blocchetto on t_blocchetto b_1 (cost=0.42..6.51 rows=1 width=14) (actual time=0.066..0.066 rows=1 loops=8,221)

  • Index Cond: (id_blocchetto = t_vetrino.id_blocchetto)
  • Filter: ((dt_cancel IS NULL) AND ((tp_blocchetto)::text <> 'P'::text))
  • Rows Removed by Filter: 0
42. 413.612 413.612 ↑ 1.0 1 7,804

Index Scan using p_t_prelievo on t_prelievo t_prelievo_1 (cost=0.43..6.51 rows=1 width=10) (actual time=0.052..0.053 rows=1 loops=7,804)

  • Index Cond: (id_prelievo = b_1.id_prelievo)
  • Filter: ((dt_cancel IS NULL) AND ((cd_prelievo)::text <> '-'::text))
43. 1,178.404 1,178.404 ↑ 1.0 1 7,804

Index Scan using p_t_campionamento on t_campionamento c_1 (cost=0.43..6.52 rows=1 width=24) (actual time=0.150..0.151 rows=1 loops=7,804)

  • Index Cond: (b_1.id_campionamento = id_campionamento)
44. 1.081 2,173.357 ↓ 3,301.0 3,301 1

HashAggregate (cost=277,057.55..277,057.56 rows=1 width=4) (actual time=2,173.086..2,173.357 rows=3,301 loops=1)

45. 0.219 2,172.276 ↓ 3,754.0 3,754 1

Subquery Scan on v_no_ass5 (cost=277,057.53..277,057.55 rows=1 width=4) (actual time=2,170.526..2,172.276 rows=3,754 loops=1)

46. 17.411 2,172.057 ↓ 3,754.0 3,754 1

HashAggregate (cost=277,057.53..277,057.54 rows=1 width=99) (actual time=2,170.525..2,172.057 rows=3,754 loops=1)

47. 4.834 2,154.646 ↓ 10,717.0 10,717 1

Nested Loop Left Join (cost=16,004.34..277,057.47 rows=1 width=99) (actual time=364.057..2,154.646 rows=10,717 loops=1)

  • Filter: (c_2.dt_cancel IS NULL)
48. 2.996 2,042.642 ↓ 10,717.0 10,717 1

Nested Loop (cost=16,003.91..277,050.94 rows=1 width=87) (actual time=364.049..2,042.642 rows=10,717 loops=1)

49. 4.621 1,996.778 ↓ 10,717.0 10,717 1

Nested Loop (cost=16,003.48..277,044.42 rows=1 width=85) (actual time=364.040..1,996.778 rows=10,717 loops=1)

50. 41.730 1,813.917 ↓ 11,140.0 11,140 1

Nested Loop (cost=16,003.06..277,037.92 rows=1 width=79) (actual time=364.030..1,813.917 rows=11,140 loops=1)

  • Join Filter: ((t_caso_3.tp_accesso)::text = (t_tp_accesso_2.tp_accesso)::text)
  • Rows Removed by Join Filter: 144820
51. 4.054 1,749.907 ↓ 11,140.0 11,140 1

Nested Loop (cost=16,003.06..277,036.54 rows=1 width=77) (actual time=364.017..1,749.907 rows=11,140 loops=1)

52. 6.599 1,634.433 ↓ 11,142.0 11,142 1

Nested Loop (cost=16,002.64..277,029.74 rows=1 width=65) (actual time=364.010..1,634.433 rows=11,142 loops=1)

  • Join Filter: (t_caso_4.id_caso_p = t_caso_3.id_caso)
  • Rows Removed by Join Filter: 6167
53. 10.345 1,583.250 ↓ 40.1 11,146 1

Hash Join (cost=16,002.21..275,121.63 rows=278 width=43) (actual time=363.994..1,583.250 rows=11,146 loops=1)

  • Hash Cond: (t_caso_4.id_caso = t_vetrino_1.id_caso)
54. 1,420.678 1,420.678 ↓ 1.3 7,450 1

Seq Scan on t_caso t_caso_4 (cost=0.00..257,163.63 rows=5,600 width=12) (actual time=211.451..1,420.678 rows=7,450 loops=1)

  • Filter: ((dt_cancel IS NULL) AND ((fl_stato)::text <> ALL ('{I,P,V,R,Z,F}'::text[])))
  • Rows Removed by Filter: 1743346
55. 15.239 152.227 ↑ 1.1 78,140 1

Hash (cost=14,915.40..14,915.40 rows=86,945 width=39) (actual time=152.227..152.227 rows=78,140 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 4523kB
56. 59.094 136.988 ↑ 1.1 78,140 1

Hash Right Join (cost=12,358.77..14,915.40 rows=86,945 width=39) (actual time=72.519..136.988 rows=78,140 loops=1)

  • Hash Cond: (t_vetrino_test_1.id_vetrino = t_vetrino_1.id_vetrino)
  • Filter: (((t_colorazione_1.tp_colorazione)::text = 'S'::text) OR ((t_colorazione_1.tp_colorazione)::text = 'D'::text) OR ((t_colorazione_1.tp_colorazione)::text = 'R'::te (...)
57. 5.465 5.465 ↑ 1.0 68,700 1

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

58. 16.641 72.429 ↑ 1.1 78,140 1

Hash (cost=11,257.11..11,257.11 rows=88,133 width=40) (actual time=72.429..72.429 rows=78,140 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 4759kB
59. 29.313 55.788 ↑ 1.1 78,140 1

Hash Left Join (cost=1,745.33..11,257.11 rows=88,133 width=40) (actual time=7.082..55.788 rows=78,140 loops=1)

  • Hash Cond: ((t_vetrino_1.id_colorazione)::text = (t_colorazione_1.id_colorazione)::text)
  • Filter: (((t_colorazione_1.tp_colorazione)::text <> 'R'::text) OR (t_vetrino_1.id_colorazione IS NULL))
60. 20.437 26.453 ↑ 1.1 78,140 1

Bitmap Heap Scan on t_vetrino t_vetrino_1 (cost=1,743.30..10,283.15 rows=89,473 width=41) (actual time=7.030..26.453 rows=78,140 loops=1)

  • Recheck Cond: (user_consegna = 0)
  • Filter: ((dt_cancel IS NULL) AND (annullato IS NULL))
  • Rows Removed by Filter: 14527
61. 6.016 6.016 ↑ 1.0 92,667 1

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

  • Index Cond: (user_consegna = 0)
62. 0.008 0.022 ↑ 1.0 38 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 2kB
63. 0.014 0.014 ↑ 1.0 38 1

Seq Scan on t_colorazione t_colorazione_1 (cost=0.00..1.55 rows=38 width=6) (actual time=0.008..0.014 rows=38 loops=1)

  • Filter: (dt_cancel IS NULL)
  • Rows Removed by Filter: 17
64. 44.584 44.584 ↓ 2.0 2 11,146

Index Scan using t_caso2 on t_caso t_caso_3 (cost=0.43..6.85 rows=1 width=34) (actual time=0.004..0.004 rows=2 loops=11,146)

  • Index Cond: (id_acc_mat = t_vetrino_1.id_acc_mat)
  • Filter: (dt_cancel IS NULL)
  • Rows Removed by Filter: 0
65. 111.420 111.420 ↑ 1.0 1 11,142

Index Scan using p_t_paziente on t_paziente t_paziente_2 (cost=0.42..6.79 rows=1 width=20) (actual time=0.010..0.010 rows=1 loops=11,142)

  • Index Cond: (id_paziente = t_caso_4.id_paziente)
  • Filter: (dt_cancel IS NULL)
66. 22.280 22.280 ↑ 1.0 14 11,140

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

  • Filter: (dt_cancel IS NULL)
  • Rows Removed by Filter: 7
67. 178.240 178.240 ↑ 1.0 1 11,140

Index Scan using p_t_blocchetto on t_blocchetto b_2 (cost=0.42..6.49 rows=1 width=14) (actual time=0.016..0.016 rows=1 loops=11,140)

  • Index Cond: (id_blocchetto = t_vetrino_1.id_blocchetto)
  • Filter: ((dt_cancel IS NULL) AND ((tp_blocchetto)::text <> 'P'::text))
  • Rows Removed by Filter: 0
68. 42.868 42.868 ↑ 1.0 1 10,717

Index Scan using p_t_prelievo on t_prelievo t_prelievo_2 (cost=0.43..6.51 rows=1 width=10) (actual time=0.004..0.004 rows=1 loops=10,717)

  • Index Cond: (id_prelievo = b_2.id_prelievo)
  • Filter: ((dt_cancel IS NULL) AND ((cd_prelievo)::text <> '-'::text))
69. 107.170 107.170 ↑ 1.0 1 10,717

Index Scan using p_t_campionamento on t_campionamento c_2 (cost=0.43..6.52 rows=1 width=24) (actual time=0.010..0.010 rows=1 loops=10,717)

  • Index Cond: (b_2.id_campionamento = id_campionamento)
Total runtime : 22,162.973 ms