explain.depesz.com

PostgreSQL's explain analyze made readable

Result: cb3X

Settings
# exclusive inclusive rows x rows loops node
1. 0.004 9,020.383 ↑ 1.7 23 1

Unique (cost=60,994.30..60,994.50 rows=40 width=32) (actual time=9,020.373..9,020.383 rows=23 loops=1)

2.          

CTE mandates

3. 0.011 0.011 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=24) (actual time=0.011..0.011 rows=1 loops=1)

4. 0.036 9,020.379 ↑ 1.7 23 1

Sort (cost=60,994.28..60,994.38 rows=40 width=32) (actual time=9,020.373..9,020.379 rows=23 loops=1)

  • Sort Key: cte.keyid
  • Sort Method: quicksort Memory: 26kB
5. 56.856 9,020.343 ↑ 1.7 23 1

Hash Join (cost=58,552.00..60,993.22 rows=40 width=32) (actual time=8,607.516..9,020.343 rows=23 loops=1)

  • Hash Cond: (cte.mandateid = mandates.mandateid)
  • Join Filter: ((cte.date >= mandates.fromdate) AND (cte.date <= mandates.todate))
6. 8,963.471 8,963.471 ↓ 5.0 364,222 1

CTE Scan on actorexpcte cte (cost=58,551.96..59,995.40 rows=72,172 width=1,114) (actual time=7,823.027..8,963.471 rows=364,222 loops=1)

7.          

CTE actorexpcte

8. 394.966 8,504.988 ↓ 5.0 364,222 1

Unique (cost=53,860.78..58,551.96 rows=72,172 width=384) (actual time=7,823.020..8,504.988 rows=364,222 loops=1)

9. 1,195.032 8,110.022 ↓ 5.0 364,222 1

Sort (cost=53,860.78..54,041.21 rows=72,172 width=384) (actual time=7,823.018..8,110.022 rows=364,222 loops=1)

  • Sort Key: "*SELECT* 1".keyid, ('A'::text), "*SELECT* 1".entityid, "*SELECT* 1".typeofquantification, "*SELECT* 1".date, "*SELECT* 1".incarico_ref_id_oe503, "*SELECT* 1".mandateid, "*SELECT* 1".reasonid, "*SELECT* 1".reason, "*SELECT* 1".collaboratorid, "*SELECT* 1".description, "*SELECT* 1".duration, "*SELECT* 1".timeunit, "*SELECT* 1".tariff, "*SELECT* 1".fixedpartofamount, "*SELECT* 1".amount, "*SELECT* 1".price, "*SELECT* 1".totalfee, "*SELECT* 1".printinbill, "*SELECT* 1".timeinbill, (("*SELECT* 1".amountinbill)::real), (false), (NULL::numeric(15,2)), (NULL::numeric(15,2)), (NULL::numeric(15,2))
  • Sort Method: external merge Disk: 50976kB
10. 43.642 6,914.990 ↓ 5.0 364,222 1

Append (cost=18,077.45..35,454.31 rows=72,172 width=384) (actual time=480.958..6,914.990 rows=364,222 loops=1)

11. 330.251 6,473.977 ↓ 200.1 288,476 1

Subquery Scan on *SELECT* 1 (cost=18,077.45..24,225.16 rows=1,442 width=243) (actual time=480.957..6,473.977 rows=288,476 loops=1)

12. 447.562 6,143.726 ↓ 200.1 288,476 1

Hash Left Join (cost=18,077.45..24,207.13 rows=1,442 width=242) (actual time=480.947..6,143.726 rows=288,476 loops=1)

  • Hash Cond: (att.oe006_causale_cdl_id_oe060 = causatt.oe060_id)
13. 291.400 5,695.994 ↓ 200.1 288,476 1

Nested Loop (cost=18,071.03..24,170.07 rows=1,442 width=96) (actual time=480.745..5,695.994 rows=288,476 loops=1)

14. 322.136 4,539.166 ↓ 200.1 288,476 1

Nested Loop (cost=18,070.74..23,692.16 rows=1,442 width=96) (actual time=480.736..4,539.166 rows=288,476 loops=1)

15. 213.646 3,062.750 ↓ 200.0 288,570 1

Nested Loop (cost=18,070.45..23,210.20 rows=1,443 width=100) (actual time=480.711..3,062.750 rows=288,570 loops=1)

16. 337.388 818.033 ↓ 200.0 290,153 1

Hash Right Join (cost=18,070.03..22,395.48 rows=1,451 width=96) (actual time=480.689..818.033 rows=290,153 loops=1)

  • Hash Cond: (att_fasi.oe959_attivita_id_oe006 = att.oe006_id)
  • Filter: (att_fasi.oe959_id IS NULL)
17. 0.006 0.006 ↓ 0.0 0 1

Seq Scan on oe959_tbattivita_tariffegiud_fasigiud att_fasi (cost=0.00..30.40 rows=2,040 width=8) (actual time=0.006..0.006 rows=0 loops=1)

18. 222.536 480.639 ↑ 1.0 290,153 1

Hash (cost=10,191.79..10,191.79 rows=290,179 width=96) (actual time=480.639..480.639 rows=290,153 loops=1)

  • Buckets: 32768 Batches: 16 Memory Usage: 2615kB
19. 258.103 258.103 ↑ 1.0 290,153 1

Seq Scan on oe006_tbcdl_attivita att (cost=0.00..10,191.79 rows=290,179 width=96) (actual time=0.018..258.103 rows=290,153 loops=1)

20. 2,031.071 2,031.071 ↑ 1.0 1 290,153

Index Scan using oe006_c01pk on oe006_tbcdl_attivita att_1 (cost=0.42..0.55 rows=1 width=8) (actual time=0.007..0.007 rows=1 loops=290,153)

  • Index Cond: (oe006_id = att.oe006_id)
  • Filter: ((NOT oe006_flg_pianificata) AND oe006_flg_fatturabile)
  • Rows Removed by Filter: 0
21. 1,154.280 1,154.280 ↑ 1.0 1 288,570

Index Scan using oe005_c01pk on oe005_tbcdl_testata cdltestata_1 (cost=0.29..0.32 rows=1 width=4) (actual time=0.004..0.004 rows=1 loops=288,570)

  • Index Cond: (oe005_id = att_1.oe006_cdl_testata_id_oe005)
  • Filter: ((oe005_periodo_consolidamento_id_oe028 IS NOT NULL) AND (oe005_stato = 5))
  • Rows Removed by Filter: 0
22. 865.428 865.428 ↑ 1.0 1 288,476

Index Scan using oe005_c01pk on oe005_tbcdl_testata cdltestata (cost=0.29..0.32 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=288,476)

  • Index Cond: (oe005_id = att.oe006_cdl_testata_id_oe005)
23. 0.038 0.170 ↑ 1.0 152 1

Hash (cost=4.52..4.52 rows=152 width=31) (actual time=0.170..0.170 rows=152 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 18kB
24. 0.132 0.132 ↑ 1.0 152 1

Seq Scan on oe060_tbcausali_attivita causatt (cost=0.00..4.52 rows=152 width=31) (actual time=0.049..0.132 rows=152 loops=1)

25. 0.339 81.721 ↓ 14.9 149 1

Nested Loop (cost=1,943.66..1,998.00 rows=10 width=337) (actual time=5.582..81.721 rows=149 loops=1)

26. 0.159 81.084 ↓ 14.9 149 1

Nested Loop (cost=1,943.52..1,995.63 rows=10 width=43) (actual time=5.537..81.084 rows=149 loops=1)

27. 0.199 80.627 ↓ 14.9 149 1

Nested Loop (cost=1,943.37..1,993.77 rows=10 width=43) (actual time=5.497..80.627 rows=149 loops=1)

28. 0.151 79.981 ↓ 14.9 149 1

Hash Join (cost=1,943.10..1,985.97 rows=10 width=43) (actual time=5.456..79.981 rows=149 loops=1)

  • Hash Cond: (oe202_tbnotaspese_dettaglio.oe202_id = exp.oe202_id)
29. 0.359 79.587 ↓ 14.9 149 1

Hash Left Join (cost=1,933.73..1,976.37 rows=10 width=4,310) (actual time=5.187..79.587 rows=149 loops=1)

  • Hash Cond: (oe201_tbnotaspese_testata_1.oe201_azienda_id_bp003 = cte_temp.bp003_id)
  • Filter: (((cte_temp.bp003_id IS NULL) AND (cte_is_cdl_consolidata.oe005_stato = 5) AND (cte_is_cdl_consolidata.oe005_periodo_consolidamento_id_oe028 IS NOT NULL)) OR ((cte_temp.bp003_id IS NOT NULL) AND oe202_tbnotaspese_dettaglio.oe202_flg_visto_amministrazione))
  • Rows Removed by Filter: 8
30.          

CTE cte_is_cons_collab_vert

31. 0.007 2.071 ↑ 1.0 1 1

Subquery Scan on tt (cost=0.00..8.04 rows=1 width=9) (actual time=1.925..2.071 rows=1 loops=1)

  • Filter: (tt.is_vertical AND (tt.enum_consolidamento = 2))
  • Rows Removed by Filter: 12
32. 2.064 2.064 ↑ 1.0 13 1

Seq Scan on bp003_tbaziende (cost=0.00..7.87 rows=13 width=9) (actual time=0.525..2.064 rows=13 loops=1)

33.          

CTE cte_is_cdl_consolidata

34. 1.085 74.120 ↑ 1.0 1,669 1

Hash Join (cost=102.41..1,860.38 rows=1,693 width=14) (actual time=1.760..74.120 rows=1,669 loops=1)

  • Hash Cond: (oe032_tbcdl_nota_spese_testata.oe032_nota_spese_testata_id_oe201 = oe201_tbnotaspese_testata.oe201_id)
35. 9.634 72.237 ↑ 1.0 1,669 1

Hash Join (cost=48.12..1,782.81 rows=1,693 width=14) (actual time=0.950..72.237 rows=1,669 loops=1)

  • Hash Cond: (oe005_tbcdl_testata.oe005_id = oe032_tbcdl_nota_spese_testata.oe032_cdl_testata_id_oe005)
36. 61.680 61.680 ↓ 1.0 52,520 1

Seq Scan on oe005_tbcdl_testata (cost=0.00..1,520.88 rows=52,504 width=10) (actual time=0.016..61.680 rows=52,520 loops=1)

  • Filter: ((oe005_periodo_consolidamento_id_oe028 IS NOT NULL) AND (oe005_stato = 5))
  • Rows Removed by Filter: 30
37. 0.400 0.923 ↑ 1.0 1,694 1

Hash (cost=26.94..26.94 rows=1,694 width=8) (actual time=0.923..0.923 rows=1,694 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 83kB
38. 0.523 0.523 ↑ 1.0 1,694 1

Seq Scan on oe032_tbcdl_nota_spese_testata (cost=0.00..26.94 rows=1,694 width=8) (actual time=0.021..0.523 rows=1,694 loops=1)

39. 0.408 0.798 ↑ 1.0 1,702 1

Hash (cost=33.02..33.02 rows=1,702 width=4) (actual time=0.798..0.798 rows=1,702 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 76kB
40. 0.390 0.390 ↑ 1.0 1,702 1

Seq Scan on oe201_tbnotaspese_testata (cost=0.00..33.02 rows=1,702 width=4) (actual time=0.007..0.390 rows=1,702 loops=1)

41. 0.528 77.148 ↓ 1.0 157 1

Hash Right Join (cost=65.28..107.02 rows=154 width=15) (actual time=3.090..77.148 rows=157 loops=1)

  • Hash Cond: (cte_is_cdl_consolidata.oe201_id = oe201_tbnotaspese_testata_1.oe201_id)
42. 75.314 75.314 ↑ 1.0 1,669 1

CTE Scan on cte_is_cdl_consolidata (cost=0.00..33.86 rows=1,693 width=10) (actual time=1.764..75.314 rows=1,669 loops=1)

43. 0.053 1.306 ↓ 1.0 157 1

Hash (cost=63.35..63.35 rows=154 width=13) (actual time=1.306..1.306 rows=157 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 16kB
44. 0.126 1.253 ↓ 1.0 157 1

Hash Join (cost=54.30..63.35 rows=154 width=13) (actual time=1.023..1.253 rows=157 loops=1)

  • Hash Cond: (oe202_tbnotaspese_dettaglio.oe202_testata_notaspese_id_oe201 = oe201_tbnotaspese_testata_1.oe201_id)
45. 0.126 0.126 ↓ 1.0 157 1

Seq Scan on oe202_tbnotaspese_dettaglio (cost=0.00..6.94 rows=154 width=9) (actual time=0.007..0.126 rows=157 loops=1)

  • Filter: oe202_flg_fatturabile
  • Rows Removed by Filter: 38
46. 0.419 1.001 ↑ 1.0 1,702 1

Hash (cost=33.02..33.02 rows=1,702 width=8) (actual time=1.001..1.001 rows=1,702 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 83kB
47. 0.582 0.582 ↑ 1.0 1,702 1

Seq Scan on oe201_tbnotaspese_testata oe201_tbnotaspese_testata_1 (cost=0.00..33.02 rows=1,702 width=8) (actual time=0.018..0.582 rows=1,702 loops=1)

48. 0.003 2.080 ↑ 1.0 1 1

Hash (cost=0.02..0.02 rows=1 width=4) (actual time=2.080..2.080 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
49. 2.077 2.077 ↑ 1.0 1 1

CTE Scan on cte_is_cons_collab_vert cte_temp (cost=0.00..0.02 rows=1 width=4) (actual time=1.930..2.077 rows=1 loops=1)

50. 0.081 0.243 ↓ 1.0 195 1

Hash (cost=6.94..6.94 rows=194 width=43) (actual time=0.243..0.243 rows=195 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 22kB
51. 0.162 0.162 ↓ 1.0 195 1

Seq Scan on oe202_tbnotaspese_dettaglio exp (cost=0.00..6.94 rows=194 width=43) (actual time=0.026..0.162 rows=195 loops=1)

52. 0.447 0.447 ↑ 1.0 1 149

Index Scan using oe201_c01pk on oe201_tbnotaspese_testata exptestata (cost=0.28..0.77 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=149)

  • Index Cond: (oe201_id = exp.oe202_testata_notaspese_id_oe201)
53. 0.298 0.298 ↑ 1.0 1 149

Index Scan using oe003_c01uq on oe003_tbcollaboratori coll_1 (cost=0.15..0.18 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=149)

  • Index Cond: (oe003_utente_id_bp010 = exptestata.oe201_utente_id_bp010)
54. 0.298 0.298 ↑ 1.0 1 149

Index Scan using oe211_c01pk on oe211_tbnotaspese_causali causexp (cost=0.14..0.22 rows=1 width=21) (actual time=0.002..0.002 rows=1 loops=149)

  • Index Cond: (oe211_id = exp.oe202_causale_id_oe211)
55. 99.010 315.650 ↓ 1.1 75,597 1

Hash Join (cost=4,049.39..8,523.85 rows=70,720 width=314) (actual time=120.945..315.650 rows=75,597 loops=1)

  • Hash Cond: (exp_1.oe108_causale_spesa_id_oe211 = causexp_1.oe211_id)
56. 74.072 216.565 ↓ 1.1 75,597 1

Hash Join (cost=4,047.00..7,018.66 rows=70,720 width=55) (actual time=120.836..216.565 rows=75,597 loops=1)

  • Hash Cond: (exp_1.oe108_id = oe108_tbincarichi_spese.oe108_id)
57. 21.849 21.849 ↑ 1.0 75,597 1

Seq Scan on oe108_tbincarichi_spese exp_1 (cost=0.00..1,980.97 rows=75,597 width=55) (actual time=0.024..21.849 rows=75,597 loops=1)

58. 26.103 120.644 ↓ 1.1 75,597 1

Hash (cost=3,163.00..3,163.00 rows=70,720 width=4) (actual time=120.644..120.644 rows=75,597 loops=1)

  • Buckets: 131072 Batches: 1 Memory Usage: 3682kB
59. 30.157 94.541 ↓ 1.1 75,597 1

Hash Join (cost=2.35..3,163.00 rows=70,720 width=4) (actual time=0.077..94.541 rows=75,597 loops=1)

  • Hash Cond: (oe108_tbincarichi_spese.oe108_causale_spesa_id_oe211 = oe211_tbnotaspese_causali.oe211_id)
60. 64.339 64.339 ↑ 1.0 75,597 1

Seq Scan on oe108_tbincarichi_spese (cost=0.00..2,169.96 rows=75,597 width=8) (actual time=0.017..64.339 rows=75,597 loops=1)

  • Filter: (oe108_stato = 1)
61. 0.020 0.045 ↑ 1.0 58 1

Hash (cost=1.62..1.62 rows=58 width=4) (actual time=0.045..0.045 rows=58 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
62. 0.025 0.025 ↑ 1.0 58 1

Seq Scan on oe211_tbnotaspese_causali (cost=0.00..1.62 rows=58 width=4) (actual time=0.009..0.025 rows=58 loops=1)

  • Filter: oe211_flg_riaddebito
  • Rows Removed by Filter: 4
63. 0.024 0.075 ↑ 1.0 62 1

Hash (cost=1.62..1.62 rows=62 width=22) (actual time=0.075..0.075 rows=62 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
64. 0.051 0.051 ↑ 1.0 62 1

Seq Scan on oe211_tbnotaspese_causali causexp_1 (cost=0.00..1.62 rows=62 width=22) (actual time=0.022..0.051 rows=62 loops=1)

65. 0.002 0.016 ↑ 1.0 1 1

Hash (cost=0.02..0.02 rows=1 width=12) (actual time=0.016..0.016 rows=1 loops=1)

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

CTE Scan on mandates (cost=0.00..0.02 rows=1 width=12) (actual time=0.014..0.014 rows=1 loops=1)

Planning time : 11.296 ms
Execution time : 9,043.755 ms