explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Ak4y

Settings
# exclusive inclusive rows x rows loops node
1. 0.193 236.399 ↑ 28.4 371 1

Unique (cost=90,008.59..90,772.45 rows=10,536 width=505) (actual time=236.177..236.399 rows=371 loops=1)

2. 0.666 236.206 ↑ 28.4 371 1

Sort (cost=90,008.59..90,034.93 rows=10,536 width=505) (actual time=236.176..236.206 rows=371 loops=1)

  • Sort Key: c.id_commessa, (to_char((c.data_prevista_evasione)::timestamp with time zone, 'DD/MM/YYYY'::text)), c.stima_durata, c.massa, (COALESCE(p.descrizione, ''::text)), c.priorita, s.description, d.cliente, d.articolo, d.documento, d.quantita, ( (...)
  • Sort Method: quicksort Memory: 158kB
3. 11.358 235.540 ↑ 28.4 371 1

Merge Left Join (cost=7,361.83..89,304.62 rows=10,536 width=505) (actual time=176.278..235.540 rows=371 loops=1)

  • Merge Cond: ((c.id_commessa)::text = a.id_commessa)
4. 0.153 139.443 ↓ 1.0 371 1

Nested Loop Left Join (cost=6,287.97..6,689.61 rows=360 width=270) (actual time=136.501..139.443 rows=371 loops=1)

5. 0.383 137.064 ↓ 1.0 371 1

Merge Left Join (cost=6,287.68..6,290.01 rows=360 width=154) (actual time=136.494..137.064 rows=371 loops=1)

  • Merge Cond: ((c.id_commessa)::text = (c_1.id_commessa)::text)
6. 0.852 1.352 ↓ 1.0 371 1

Sort (cost=51.24..52.14 rows=360 width=140) (actual time=1.268..1.352 rows=371 loops=1)

  • Sort Key: c.id_commessa
  • Sort Method: quicksort Memory: 76kB
7. 0.088 0.500 ↓ 1.0 371 1

Hash Left Join (cost=13.26..35.96 rows=360 width=140) (actual time=0.250..0.500 rows=371 loops=1)

  • Hash Cond: (sg.stato_generale = (s.status_code)::bpchar)
8. 0.059 0.398 ↓ 1.0 371 1

Hash Left Join (cost=12.12..29.87 rows=360 width=104) (actual time=0.216..0.398 rows=371 loops=1)

  • Hash Cond: (c.priorita = p.id)
9. 0.104 0.323 ↓ 1.0 371 1

Hash Left Join (cost=11.03..24.55 rows=360 width=72) (actual time=0.189..0.323 rows=371 loops=1)

  • Hash Cond: ((c.id_commessa)::text = sg.id_commessa)
10. 0.057 0.057 ↓ 1.0 371 1

Seq Scan on abc_commessa c (cost=0.00..8.60 rows=360 width=70) (actual time=0.017..0.057 rows=371 loops=1)

11. 0.079 0.162 ↓ 1.0 359 1

Hash (cost=6.57..6.57 rows=357 width=21) (actual time=0.162..0.162 rows=359 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 27kB
12. 0.083 0.083 ↓ 1.0 359 1

Seq Scan on abc_commessa_stato_generale sg (cost=0.00..6.57 rows=357 width=21) (actual time=0.010..0.083 rows=359 loops=1)

13. 0.006 0.016 ↑ 1.0 4 1

Hash (cost=1.04..1.04 rows=4 width=36) (actual time=0.016..0.016 rows=4 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
14. 0.010 0.010 ↑ 1.0 4 1

Seq Scan on abc_priorita p (cost=0.00..1.04 rows=4 width=36) (actual time=0.010..0.010 rows=4 loops=1)

15. 0.005 0.014 ↑ 1.0 6 1

Hash (cost=1.06..1.06 rows=6 width=44) (actual time=0.014..0.014 rows=6 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
16. 0.009 0.009 ↑ 1.0 6 1

Seq Scan on abc_ordine_stato_segreteria s (cost=0.00..1.06 rows=6 width=44) (actual time=0.009..0.009 rows=6 loops=1)

17. 0.033 135.329 ↓ 6.7 67 1

Materialize (cost=6,236.44..6,236.84 rows=10 width=33) (actual time=135.222..135.329 rows=67 loops=1)

18. 0.067 135.296 ↓ 6.7 67 1

GroupAggregate (cost=6,236.44..6,236.71 rows=10 width=137) (actual time=135.216..135.296 rows=67 loops=1)

  • Group Key: c_1.id_commessa, p_1.descrizione, p_1.id_utente, (to_char((c_1.data_prevista_evasione)::timestamp with time zone, 'DD/MM/YYYY'::text)), u.utente, (CASE WHEN (abc_commessa_generale.stato IS NULL) THEN (c_1.st (...)
19. 0.187 135.229 ↓ 6.7 67 1

Sort (cost=6,236.44..6,236.46 rows=10 width=133) (actual time=135.212..135.229 rows=67 loops=1)

  • Sort Key: c_1.id_commessa, p_1.descrizione, p_1.id_utente, (to_char((c_1.data_prevista_evasione)::timestamp with time zone, 'DD/MM/YYYY'::text)), u.utente
  • Sort Method: quicksort Memory: 34kB
20. 0.159 135.042 ↓ 6.7 67 1

Hash Join (cost=2,022.35..6,236.27 rows=10 width=133) (actual time=34.831..135.042 rows=67 loops=1)

  • Hash Cond: (((c_1.id_commessa)::text = (abc_tempo.id_commessa)::text) AND (abc_commessa_generale.id_utente = p_1.id_utente))
21. 0.914 109.640 ↓ 2.5 122 1

Nested Loop (cost=647.32..4,835.78 rows=49 width=654) (actual time=8.424..109.640 rows=122 loops=1)

22. 21.447 96.160 ↓ 2.5 122 1

Hash Right Join (cost=647.04..4,765.84 rows=49 width=93) (actual time=3.658..96.160 rows=122 loops=1)

  • Hash Cond: (((movimag.numero)::text = c_1.sigla_numero) AND ((movimag.riga)::text = c_1.sigla_riga))
23. 6.882 71.118 ↓ 17.6 61,192 1

Nested Loop (cost=45.86..4,129.82 rows=3,481 width=14) (actual time=0.052..71.118 rows=61,192 loops=1)

24. 0.042 0.042 ↓ 6.0 6 1

Seq Scan on tipodocu (cost=0.00..1.73 rows=1 width=12) (actual time=0.021..0.042 rows=6 loops=1)

  • Filter: ((tipoeffett = ANY ('{O,V}'::bpchar[])) AND (usrgestcom = 'S'::bpchar))
  • Rows Removed by Filter: 43
25. 58.290 64.194 ↓ 2.5 10,199 6

Bitmap Heap Scan on movimag (cost=45.86..4,086.48 rows=4,160 width=17) (actual time=1.498..10.699 rows=10,199 loops=6)

  • Recheck Cond: (tipoprot = tipodocu.codice)
  • Heap Blocks: exact=20884
26. 5.904 5.904 ↓ 2.5 10,199 6

Bitmap Index Scan on ix_movimag_tipoprot (cost=0.00..44.82 rows=4,160 width=0) (actual time=0.984..0.984 rows=10,199 loops=6)

  • Index Cond: (tipoprot = tipodocu.codice)
27. 0.035 3.595 ↓ 2.5 122 1

Hash (cost=600.44..600.44 rows=49 width=107) (actual time=3.595..3.595 rows=122 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 19kB
28. 0.051 3.560 ↓ 2.5 122 1

Nested Loop Left Join (cost=14.60..600.44 rows=49 width=107) (actual time=0.238..3.560 rows=122 loops=1)

  • Join Filter: ((s_1.status_code)::bpchar = CASE WHEN (abc_commessa_generale.stato IS NULL) THEN (c_1.stato)::bpchar ELSE abc_commessa_generale.stato END)
29. 0.125 3.509 ↓ 2.5 122 1

Nested Loop Left Join (cost=14.60..598.63 rows=49 width=107) (actual time=0.222..3.509 rows=122 loops=1)

  • Join Filter: (u.id_utente = abc_commessa_generale.id_utente)
  • Rows Removed by Join Filter: 1831
30. 0.801 3.262 ↓ 2.5 122 1

Hash Right Join (cost=13.51..593.33 rows=49 width=75) (actual time=0.156..3.262 rows=122 loops=1)

  • Hash Cond: ((abc_commessa_generale.id_commessa)::text = (c_1.id_commessa)::text)
  • Filter: (CASE WHEN (abc_commessa_generale.stato IS NULL) THEN (c_1.stato)::bpchar ELSE abc_commessa_generale.stato END = 'P'::bpchar)
  • Rows Removed by Filter: 342
31. 0.421 2.332 ↓ 1.0 11,108 1

Append (cost=0.00..304.32 rows=11,066 width=68) (actual time=0.012..2.332 rows=11,108 loops=1)

32. 0.019 0.019 ↑ 1.0 149 1

Seq Scan on abc_commessa_generale (cost=0.00..3.49 rows=149 width=25) (actual time=0.012..0.019 rows=149 loops=1)

33. 1.026 1.892 ↓ 1.0 10,959 1

Subquery Scan on *SELECT* 2 (cost=0.00..299.34 rows=10,917 width=68) (actual time=0.012..1.892 rows=10,959 loops=1)

34. 0.866 0.866 ↓ 1.0 10,959 1

Seq Scan on abc_commessa_utente (cost=0.00..190.17 rows=10,917 width=25) (actual time=0.011..0.866 rows=10,959 loops=1)

35. 0.065 0.129 ↓ 1.0 327 1

Hash (cost=9.50..9.50 rows=321 width=43) (actual time=0.129..0.129 rows=327 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 33kB
36. 0.064 0.064 ↓ 1.0 327 1

Seq Scan on abc_commessa c_1 (cost=0.00..9.50 rows=321 width=43) (actual time=0.010..0.064 rows=327 loops=1)

  • Filter: ((stato)::text <> 'C'::text)
  • Rows Removed by Filter: 44
37. 0.066 0.122 ↓ 4.0 16 122

Materialize (cost=1.09..2.37 rows=4 width=36) (actual time=0.001..0.001 rows=16 loops=122)

38. 0.033 0.056 ↓ 4.0 16 1

Hash Join (cost=1.09..2.35 rows=4 width=36) (actual time=0.052..0.056 rows=16 loops=1)

  • Hash Cond: (u.id_ruolo = r.id_ruolo)
39. 0.009 0.009 ↑ 1.0 16 1

Seq Scan on abc_utente u (cost=0.00..1.16 rows=16 width=40) (actual time=0.008..0.009 rows=16 loops=1)

40. 0.005 0.014 ↑ 1.0 4 1

Hash (cost=1.04..1.04 rows=4 width=4) (actual time=0.014..0.014 rows=4 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
41. 0.009 0.009 ↑ 1.0 4 1

Seq Scan on abc_ruolo r (cost=0.00..1.04 rows=4 width=4) (actual time=0.008..0.009 rows=4 loops=1)

42. 0.000 0.000 ↑ 1.0 1 122

Materialize (cost=0.00..1.08 rows=1 width=8) (actual time=0.000..0.000 rows=1 loops=122)

43. 0.010 0.010 ↑ 1.0 1 1

Seq Scan on abc_ordine_stato s_1 (cost=0.00..1.08 rows=1 width=8) (actual time=0.010..0.010 rows=1 loops=1)

  • Filter: ((status_code)::bpchar = 'P'::bpchar)
  • Rows Removed by Filter: 5
44. 1.342 1.342 ↑ 1.0 1 122

Index Only Scan using ix_abc_commessa_dettaglio on abc_commessa_dettaglio d_1 (cost=0.29..1.15 rows=1 width=19) (actual time=0.011..0.011 rows=1 loops=122)

  • Index Cond: (id_commessa = (c_1.id_commessa)::text)
  • Heap Fetches: 122
45.          

SubPlan (forNested Loop)

46. 11.224 11.224 ↑ 1.0 1 122

Result (cost=0.00..0.26 rows=1 width=1) (actual time=0.092..0.092 rows=1 loops=122)

47. 0.151 25.243 ↑ 6.2 326 1

Hash (cost=1,344.90..1,344.90 rows=2,009 width=37) (actual time=25.243..25.243 rows=326 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 40kB
48. 1.757 25.092 ↑ 6.2 326 1

Hash Join (cost=1,086.22..1,344.90 rows=2,009 width=37) (actual time=20.613..25.092 rows=326 loops=1)

  • Hash Cond: ((max(abc_tempo.id_tempo)) = p_1.id_tempo)
49. 15.670 19.934 ↓ 1.1 10,665 1

HashAggregate (cost=881.82..982.28 rows=10,046 width=27) (actual time=17.189..19.934 rows=10,665 loops=1)

  • Group Key: abc_tempo.id_commessa, abc_tempo.id_utente
50. 4.264 4.264 ↑ 1.0 32,827 1

Seq Scan on abc_tempo (cost=0.00..635.47 rows=32,847 width=27) (actual time=0.019..4.264 rows=32,827 loops=1)

51. 1.089 3.401 ↓ 3.1 6,513 1

Hash (cost=178.11..178.11 rows=2,103 width=22) (actual time=3.401..3.401 rows=6,513 loops=1)

  • Buckets: 8192 (originally 4096) Batches: 1 (originally 1) Memory Usage: 438kB
52. 0.679 2.312 ↓ 3.1 6,599 1

Nested Loop (cost=1.44..178.11 rows=2,103 width=22) (actual time=0.036..2.312 rows=6,599 loops=1)

53. 0.002 0.023 ↓ 2.5 5 1

Unique (cost=1.15..1.16 rows=2 width=4) (actual time=0.020..0.023 rows=5 loops=1)

54. 0.008 0.021 ↓ 2.5 5 1

Sort (cost=1.15..1.16 rows=2 width=4) (actual time=0.019..0.021 rows=5 loops=1)

  • Sort Key: abc_motivo.id_motivo
  • Sort Method: quicksort Memory: 25kB
55. 0.001 0.013 ↓ 2.5 5 1

Append (cost=0.00..1.14 rows=2 width=4) (actual time=0.012..0.013 rows=5 loops=1)

56. 0.012 0.012 ↓ 4.0 4 1

Seq Scan on abc_motivo (cost=0.00..1.11 rows=1 width=4) (actual time=0.012..0.012 rows=4 loops=1)

  • Filter: (flag_segreteria = 1)
  • Rows Removed by Filter: 5
57. 0.000 0.000 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=4) (actual time=0.000..0.000 rows=1 loops=1)

58. 1.610 1.610 ↓ 1.3 1,320 5

Index Scan using ix_abc_pausa_motivo on abc_pausa p_1 (cost=0.29..77.95 rows=1,052 width=26) (actual time=0.008..0.322 rows=1,320 loops=5)

  • Index Cond: (id_motivo = abc_motivo.id_motivo)
59. 2.226 2.226 ↑ 1.0 1 371

Index Scan using ix_abc_commessa_dettaglio on abc_commessa_dettaglio d (cost=0.29..1.10 rows=1 width=135) (actual time=0.005..0.006 rows=1 loops=371)

  • Index Cond: (id_commessa = (c.id_commessa)::text)
60. 1.639 54.669 ↓ 1.0 10,577 1

Materialize (cost=1,073.86..1,419.14 rows=10,536 width=51) (actual time=38.338..54.669 rows=10,577 loops=1)

61. 13.093 53.030 ↓ 1.0 10,577 1

GroupAggregate (cost=1,073.86..1,287.44 rows=10,536 width=51) (actual time=38.336..53.030 rows=10,577 loops=1)

  • Group Key: a.id_commessa
62. 36.901 39.937 ↓ 1.0 10,959 1

Sort (cost=1,073.86..1,101.15 rows=10,917 width=51) (actual time=38.308..39.937 rows=10,959 loops=1)

  • Sort Key: a.id_commessa
  • Sort Method: quicksort Memory: 1241kB
63. 1.986 3.036 ↓ 1.0 10,959 1

Hash Join (cost=1.36..341.64 rows=10,917 width=51) (actual time=0.036..3.036 rows=10,959 loops=1)

  • Hash Cond: (a.id_utente = u_1.id_utente)
64. 1.037 1.037 ↓ 1.0 10,959 1

Seq Scan on abc_commessa_utente a (cost=0.00..190.17 rows=10,917 width=23) (actual time=0.019..1.037 rows=10,959 loops=1)

65. 0.005 0.013 ↑ 1.0 16 1

Hash (cost=1.16..1.16 rows=16 width=36) (actual time=0.013..0.013 rows=16 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
66. 0.008 0.008 ↑ 1.0 16 1

Seq Scan on abc_utente u_1 (cost=0.00..1.16 rows=16 width=36) (actual time=0.006..0.008 rows=16 loops=1)

67.          

SubPlan (forMerge Left Join)

68. 12.985 12.985 ↑ 1.0 1 371

Result (cost=0.00..0.26 rows=1 width=32) (actual time=0.035..0.035 rows=1 loops=371)

69. 12.614 12.614 ↑ 1.0 1 371

Result (cost=0.00..0.26 rows=1 width=1) (actual time=0.034..0.034 rows=1 loops=371)

70. 0.371 2.597 ↑ 1.0 1 371

Aggregate (cost=2.50..2.51 rows=1 width=8) (actual time=0.007..0.007 rows=1 loops=371)

71. 2.226 2.226 ↑ 1.0 1 371

Index Only Scan using ix_abc_commessa_allegato_commessa on abc_commessa_allegato (cost=0.28..2.50 rows=1 width=0) (actual time=0.006..0.006 rows=1 loops=371)

  • Index Cond: (id_commessa = (c.id_commessa)::text)
  • Heap Fetches: 299
72. 0.263 1.315 ↑ 1.0 1 263

Aggregate (cost=2.50..2.51 rows=1 width=8) (actual time=0.005..0.005 rows=1 loops=263)

73. 1.052 1.052 ↑ 1.0 1 263

Index Only Scan using ix_abc_commessa_allegato_commessa on abc_commessa_allegato abc_commessa_allegato_1 (cost=0.28..2.50 rows=1 width=0) (actual time=0.004..0.004 rows=1 loops=263)

  • Index Cond: (id_commessa = (c.id_commessa)::text)
  • Heap Fetches: 299
74. 0.043 0.559 ↑ 1.0 1 43

Result (cost=1.87..1.88 rows=1 width=32) (actual time=0.013..0.013 rows=1 loops=43)

75.          

Initplan (forResult)

76. 0.043 0.516 ↑ 1.0 1 43

Limit (cost=0.41..1.87 rows=1 width=8) (actual time=0.011..0.012 rows=1 loops=43)

77. 0.473 0.473 ↑ 3.0 1 43

Index Only Scan Backward using ix_abc_tempo on abc_tempo abc_tempo_1 (cost=0.41..4.77 rows=3 width=8) (actual time=0.011..0.011 rows=1 loops=43)

  • Index Cond: ((id_commessa = (c.id_commessa)::text) AND (fine IS NOT NULL))
  • Heap Fetches: 39
Planning time : 15.624 ms
Execution time : 237.951 ms