explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Hvvl : Optimization for: Optimization for: Optimization for: Optimization for: plan #Bt72; plan #PJoy; plan #WFZo; plan #LPQa

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.002 109.155 ↓ 0.0 0 1

GroupAggregate (cost=5,776.13..5,776.38 rows=6 width=123) (actual time=109.155..109.155 rows=0 loops=1)

  • Group Key: ordinativi_sla_view.data_ricezione, ordinativi_sla_view.banca_descrizione, ordinativi_sla_view.pacchetto_id, ordinativi_sla_view.identificativo_flusso, ordinativi_sla_view.abi, ordinativi_sla_view.banca_istituto, ordinativi_sla_view.zona_sla, ordinativi_sla_view.data_cut_off
2. 0.012 109.153 ↓ 0.0 0 1

Sort (cost=5,776.13..5,776.14 rows=6 width=97) (actual time=109.153..109.153 rows=0 loops=1)

  • Sort Key: ordinativi_sla_view.data_ricezione, ordinativi_sla_view.banca_descrizione, ordinativi_sla_view.pacchetto_id, ordinativi_sla_view.identificativo_flusso, ordinativi_sla_view.abi, ordinativi_sla_view.banca_istituto, ordinativi_sla_view.zona_sla, ordinativi_sla_view.data_cut_off
  • Sort Method: quicksort Memory: 25kB
3. 1.378 109.141 ↓ 0.0 0 1

Subquery Scan on ordinativi_sla_view (cost=5,457.69..5,776.05 rows=6 width=97) (actual time=109.141..109.141 rows=0 loops=1)

  • Filter: (ordinativi_sla_view.pacchetto_valido AND (NOT ordinativi_sla_view.stato_sla_pac) AND (ordinativi_sla_view.data_ricezione >= '2019-09-01 00:00:00'::timestamp without time zone) AND (ordinativi_sla_view.data_ricezione < '2019-09-30 00:00:00'::timestamp without time zone))
  • Rows Removed by Filter: 5,318
4. 2.859 107.763 ↓ 1.2 5,318 1

Unique (cost=5,457.69..5,707.83 rows=4,548 width=155) (actual time=104.114..107.763 rows=5,318 loops=1)

5. 41.026 104.904 ↓ 1.2 5,318 1

Sort (cost=5,457.69..5,469.06 rows=4,548 width=155) (actual time=104.113..104.904 rows=5,318 loops=1)

  • Sort Key: banca.id, banca.abi, banca.istituto, banca.descrizione, ente.id, ipa.id, pacchetto.id, pacchetto.identificativo_flusso, pacchetto.esercizio, vers_ord.id, vers_ord.importo, vers_ord.tipo_ordinativo, vers_ord.tipo_operazione, vers_ord.esercizio, pacchetto.data_ricezione, "*SELECT* 1_1".data_fine_elaborazione, (CASE WHEN (date_part('isodow'::text, pacchetto.data_ricezione) = ANY ('{1,2,3,4,5}'::double precision[])) THEN CASE WHEN (((date_part('hour'::text, pacchetto.data_ricezione))::integer >= 5) AND ((date_part('hour'::text, pacchetto.data_ricezione))::integer < 11)) THEN (date_trunc('day'::text, pacchetto.data_ricezione) + '16:30:00'::interval) ELSE ((date_trunc('day'::text, pacchetto.data_ricezione) + '1 day'::interval) + '11:00:00'::interval) END ELSE ((date_trunc('day'::text, pacchetto.data_ricezione) + '2 days'::interval) + '11:00:00'::interval) END), (CASE WHEN (("*SELECT* 1".stato)::text = 'KO'::text) THEN true ELSE CASE WHEN ("*SELECT* 1_1".data_fine_elaborazione IS NOT NULL) THEN CASE WHEN ("*SELECT* 1_1".data_fine_elaborazione > CASE WHEN (date_part('isodow'::text, pacchetto.data_ricezione) = ANY ('{1,2,3,4}'::double precision[])) THEN CASE WHEN (((date_part('hour'::text, pacchetto.data_ricezione))::integer >= 5) AND ((date_part('hour'::text, pacchetto.data_ricezione))::integer < 11)) THEN (date_trunc('day'::text, pacchetto.data_ricezione) + '16:30:00'::interval) ELSE ((date_trunc('day'::text, pacchetto.data_ricezione) + '1 day'::interval) + '11:00:00'::interval) END WHEN (date_part('isodow'::text, pacchetto.data_ricezione) = '5'::double precision) THEN CASE WHEN (((date_part('hour'::text, pacchetto.data_ricezione))::integer >= 5) AND ((date_part('hour'::text, pacchetto.data_ricezione))::integer < 11)) THEN (date_trunc('day'::text, pacchetto.data_ricezione) + '16:30:00'::interval) ELSE ((date_trunc('day'::text, pacchetto.data_ricezione) + '3 days'::interval) + '11:00:00'::interval) END ELSE ((date_trunc('day'::text, pacchetto.data_ricezione) + '2 days'::interval) + '11:00:00'::interval) END) THEN false ELSE true END ELSE CASE WHEN (now() > CASE WHEN (date_part('isodow'::text, pacchetto.data_ricezione) = ANY ('{1,2,3,4}'::double precision[])) THEN CASE WHEN (((date_part('hour'::text, pacchetto.data_ricezione))::integer >= 5) AND ((date_part('hour'::text, pacchetto.data_ricezione))::integer < 11)) THEN (date_trunc('day'::text, pacchetto.data_ricezione) + '16:30:00'::interval) ELSE ((date_trunc('day'::text, pacchetto.data_ricezione) + '1 day'::interval) + '11:00:00'::interval) END WHEN (date_part('isodow'::text, pacchetto.data_ricezione) = '5'::double precision) THEN CASE WHEN (((date_part('hour'::text, pacchetto.data_ricezione))::integer >= 5) AND ((date_part('hour'::text, pacchetto.data_ricezione))::integer < 11)) THEN (date_trunc('day'::text, pacchetto.data_ricezione) + '16:30:00'::interval) ELSE ((date_trunc('day'::text, pacchetto.data_ricezione) + '3 days'::interval) + '11:00:00'::interval) END ELSE ((date_trunc('day'::text, pacchetto.data_ricezione) + '2 days'::interval) + '11:00:00'::interval) END) THEN false ELSE NULL::boolean END END END), (CASE WHEN (date_part('isodow'::text, pacchetto.data_ricezione) = ANY ('{1,2,3,4,5}'::double precision[])) THEN CASE WHEN (pacchetto.data_ricezione <= (date_trunc('day'::text, pacchetto.data_ricezione) + '11:00:00'::interval)) THEN 1 WHEN ((pacchetto.data_ricezione > (date_trunc('day'::text, pacchetto.data_ricezione) + '11:00:00'::interval)) AND (pacchetto.data_ricezione < (date_trunc('day'::text, pacchetto.data_ricezione) + '16:30:00'::interval))) THEN 2 ELSE 3 END ELSE 4 END), (bool_and(CASE WHEN (("*SELECT* 1".stato)::text = 'KO'::text) THEN true ELSE CASE WHEN ("*SELECT* 1_1".data_fine_elaborazione IS NOT NULL) THEN CASE WHEN ("*SELECT* 1_1".data_fine_elaborazione > CASE WHEN (date_part('isodow'::text, pacchetto.data_ricezione) = ANY ('{1,2,3,4}'::double precision[])) THEN CASE WHEN (((date_part('hour'::text, pacchetto.data_ricezione))::integer >= 5) AND ((date_part('hour'::text, pacchetto.data_ricezione))::integer < 11)) THEN (date_trunc('day'::text, pacchetto.data_ricezione) + '16:30:00'::interval) ELSE ((date_trunc('day'::text, pacchetto.data_ricezione) + '1 day'::interval) + '11:00:00'::interval) END WHEN (date_part('isodow'::text, pacchetto.data_ricezione) = '5'::double precision) THEN CASE WHEN (((date_part('hour'::text, pacchetto.data_ricezione))::integer >= 5) AND ((date_part('hour'::text, pacchetto.data_ricezione))::integer < 11)) THEN (date_trunc('day'::text, pacchetto.data_ricezione) + '16:30:00'::interval) ELSE ((date_trunc('day'::text, pacchetto.data_ricezione) + '3 days'::interval) + '11:00:00'::interval) END ELSE ((date_trunc('day'::text, pacchetto.data_ricezione) + '2 days'::interval) + '11:00:00'::interval) END) THEN false ELSE true END ELSE CASE WHEN (now() > CASE WHEN (date_part('isodow'::text, pacchetto.data_ricezione) = ANY ('{1,2,3,4,5}'::double precision[])) THEN CASE WHEN (((date_part('hour'::text, pacchetto.data_ricezione))::integer >= 5) AND ((date_part('hour'::text, pacchetto.data_ricezione))::integer < 11)) THEN (date_trunc('day'::text, pacchetto.data_ricezione) + '16:30:00'::interval) ELSE ((date_trunc('day'::text, pacchetto.data_ricezione) + '1 day'::interval) + '11:00:00'::interval) END ELSE ((date_trunc('day'::text, pacchetto.data_ricezione) + '2 days'::interval) + '11:00:00'::interval) END) THEN false ELSE NULL::boolean END END END) OVER (?)), (bool_and(true) OVER (?))
  • Sort Method: quicksort Memory: 1,605kB
6. 19.263 63.878 ↓ 1.2 5,318 1

WindowAgg (cost=3,379.23..5,181.38 rows=4,548 width=155) (actual time=33.757..63.878 rows=5,318 loops=1)

7. 9.979 44.615 ↓ 1.2 5,318 1

WindowAgg (cost=3,379.23..4,044.38 rows=4,548 width=144) (actual time=33.729..44.615 rows=5,318 loops=1)

8. 3.906 34.636 ↓ 1.2 5,318 1

Sort (cost=3,379.23..3,390.60 rows=4,548 width=143) (actual time=33.707..34.636 rows=5,318 loops=1)

  • Sort Key: pacchetto.id
  • Sort Method: quicksort Memory: 1,605kB
9. 3.103 30.730 ↓ 1.2 5,318 1

Hash Right Join (cost=1,872.91..3,102.92 rows=4,548 width=143) (actual time=29.204..30.730 rows=5,318 loops=1)

  • Hash Cond: ("*SELECT* 1_1".versione_ordinativo_id = vers_ord.id)
  • Join Filter: ((pacchetto.data_ricezione >= '2019-09-01 00:00:00'::timestamp without time zone) AND (pacchetto.data_ricezione < '2019-09-30 00:00:00'::timestamp without time zone))
  • Rows Removed by Join Filter: 5,198
10. 1.008 6.934 ↓ 1.0 5,382 1

Append (cost=0.00..1,147.17 rows=5,130 width=16) (actual time=0.007..6.934 rows=5,382 loops=1)

11. 0.996 5.884 ↓ 1.0 5,346 1

Subquery Scan on *SELECT* 1_1 (cost=0.00..1,132.62 rows=5,094 width=16) (actual time=0.007..5.884 rows=5,346 loops=1)

12. 4.888 4.888 ↓ 1.0 5,346 1

Seq Scan on proc_vers_ord (cost=0.00..1,081.68 rows=5,094 width=1,064) (actual time=0.007..4.888 rows=5,346 loops=1)

  • Filter: (((stato)::text = ANY ('{OK,KO}'::text[])) AND ((processo)::text = 'INVIOHOST'::text))
  • Rows Removed by Filter: 16,384
13. 0.007 0.042 ↑ 1.0 36 1

Subquery Scan on *SELECT* 2_1 (cost=4.58..14.54 rows=36 width=16) (actual time=0.024..0.042 rows=36 loops=1)

14. 0.015 0.035 ↑ 1.0 36 1

Bitmap Heap Scan on archivio_proc_vers_ordinativo (cost=4.58..14.18 rows=36 width=1,064) (actual time=0.024..0.035 rows=36 loops=1)

  • Recheck Cond: ((processo)::text = 'INVIOHOST'::text)
  • Filter: ((stato)::text = ANY ('{OK,KO}'::text[]))
  • Heap Blocks: exact=7
15. 0.020 0.020 ↑ 1.1 36 1

Bitmap Index Scan on idx_search_arch_proc_vers_ord (cost=0.00..4.57 rows=40 width=0) (actual time=0.020..0.020 rows=36 loops=1)

  • Index Cond: ((processo)::text = 'INVIOHOST'::text)
16. 2.555 20.693 ↓ 1.2 5,318 1

Hash (cost=1,816.06..1,816.06 rows=4,548 width=135) (actual time=20.693..20.693 rows=5,318 loops=1)

  • Buckets: 8,192 Batches: 1 Memory Usage: 992kB
17. 2.600 18.138 ↓ 1.2 5,318 1

Hash Right Join (cost=654.37..1,816.06 rows=4,548 width=135) (actual time=8.629..18.138 rows=5,318 loops=1)

  • Hash Cond: ("*SELECT* 1".versione_ordinativo_id = vers_ord.id)
18. 0.830 6.941 ↓ 1.0 5,503 1

Append (cost=0.00..1,097.66 rows=5,330 width=11) (actual time=0.011..6.941 rows=5,503 loops=1)

19. 1.056 6.004 ↓ 1.0 5,395 1

Subquery Scan on *SELECT* 1 (cost=0.00..1,081.33 rows=5,209 width=11) (actual time=0.010..6.004 rows=5,395 loops=1)

20. 4.948 4.948 ↓ 1.0 5,395 1

Seq Scan on proc_vers_ord proc_vers_ord_1 (cost=0.00..1,029.24 rows=5,209 width=551) (actual time=0.010..4.948 rows=5,395 loops=1)

  • Filter: ((processo)::text = 'VERIFICAPOTERI'::text)
  • Rows Removed by Filter: 16,335
21. 0.023 0.107 ↑ 1.1 108 1

Subquery Scan on *SELECT* 2 (cost=0.00..16.34 rows=121 width=11) (actual time=0.006..0.107 rows=108 loops=1)

22. 0.084 0.084 ↑ 1.1 108 1

Seq Scan on archivio_proc_vers_ordinativo archivio_proc_vers_ordinativo_1 (cost=0.00..15.12 rows=121 width=551) (actual time=0.006..0.084 rows=108 loops=1)

  • Filter: ((processo)::text = 'VERIFICAPOTERI'::text)
  • Rows Removed by Filter: 329
23. 2.457 8.597 ↓ 1.2 5,318 1

Hash (cost=597.51..597.51 rows=4,548 width=132) (actual time=8.597..8.597 rows=5,318 loops=1)

  • Buckets: 8,192 Batches: 1 Memory Usage: 977kB
24. 2.956 6.140 ↓ 1.2 5,318 1

Hash Join (cost=258.37..597.51 rows=4,548 width=132) (actual time=2.139..6.140 rows=5,318 loops=1)

  • Hash Cond: (vers_ord.pacchetto_id = pacchetto.id)
25. 1.059 1.059 ↑ 1.0 5,503 1

Seq Scan on vers_ord (cost=0.00..273.03 rows=5,503 width=41) (actual time=0.006..1.059 rows=5,503 loops=1)

26. 0.579 2.125 ↓ 1.0 1,567 1

Hash (cost=238.84..238.84 rows=1,562 width=91) (actual time=2.125..2.125 rows=1,567 loops=1)

  • Buckets: 2,048 Batches: 1 Memory Usage: 212kB
27. 0.538 1.546 ↓ 1.0 1,567 1

Hash Join (cost=7.07..238.84 rows=1,562 width=91) (actual time=0.124..1.546 rows=1,567 loops=1)

  • Hash Cond: (pacchetto.ipa_id = ipa.id)
28. 0.899 0.899 ↓ 1.0 1,567 1

Seq Scan on pacchetto (cost=0.00..210.35 rows=1,562 width=47) (actual time=0.006..0.899 rows=1,567 loops=1)

  • Filter: (((owner)::text <> 'MIG'::text) AND ((stato)::text <> 'ERRATO'::text))
  • Rows Removed by Filter: 323
29. 0.007 0.109 ↑ 1.1 17 1

Hash (cost=6.84..6.84 rows=18 width=52) (actual time=0.109..0.109 rows=17 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
30. 0.027 0.102 ↑ 1.1 17 1

Hash Join (cost=4.88..6.84 rows=18 width=52) (actual time=0.082..0.102 rows=17 loops=1)

  • Hash Cond: (ente.banca_id = banca.id)
31. 0.007 0.049 ↑ 1.1 17 1

Hash Join (cost=2.61..4.32 rows=18 width=24) (actual time=0.034..0.049 rows=17 loops=1)

  • Hash Cond: (ente.cliente_id = cliente.id)
32. 0.014 0.034 ↑ 1.0 18 1

Hash Join (cost=1.41..2.87 rows=18 width=32) (actual time=0.021..0.034 rows=18 loops=1)

  • Hash Cond: (ente.id = ipa.ente_id)
33. 0.009 0.009 ↓ 1.0 22 1

Seq Scan on ente (cost=0.00..1.21 rows=21 width=24) (actual time=0.003..0.009 rows=22 loops=1)

34. 0.002 0.011 ↑ 1.0 18 1

Hash (cost=1.18..1.18 rows=18 width=16) (actual time=0.011..0.011 rows=18 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
35. 0.009 0.009 ↑ 1.0 18 1

Seq Scan on ipa (cost=0.00..1.18 rows=18 width=16) (actual time=0.003..0.009 rows=18 loops=1)

36. 0.004 0.008 ↑ 1.0 9 1

Hash (cost=1.09..1.09 rows=9 width=8) (actual time=0.008..0.008 rows=9 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
37. 0.004 0.004 ↑ 1.0 9 1

Seq Scan on cliente (cost=0.00..1.09 rows=9 width=8) (actual time=0.003..0.004 rows=9 loops=1)

38. 0.020 0.026 ↓ 1.1 13 1

Hash (cost=2.12..2.12 rows=12 width=36) (actual time=0.026..0.026 rows=13 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
39. 0.006 0.006 ↓ 1.1 13 1

Seq Scan on banca (cost=0.00..2.12 rows=12 width=36) (actual time=0.003..0.006 rows=13 loops=1)

Planning time : 2.733 ms
Execution time : 109.539 ms