explain.depesz.com

PostgreSQL's explain analyze made readable

Result: uNCo

Settings
# exclusive inclusive rows x rows loops node
1. 5,490.987 79,823.087 ↓ 678,520.0 678,520 1

Subquery Scan on q (cost=7,268,949.35..7,268,974.93 rows=1 width=772) (actual time=70,055.547..79,823.087 rows=678,520 loops=1)

  • Buffers: shared hit=1774881, temp read=72198 written=72198
2. 2,050.686 74,332.100 ↓ 678,520.0 678,520 1

Hash Join (cost=7,268,949.35..7,268,974.91 rows=1 width=656) (actual time=70,035.896..74,332.100 rows=678,520 loops=1)

  • Hash Cond: (((f.cod_periodo)::text = (x.cod_periodo)::text) AND ((f.cod_conto)::text = (x.cod_conto)::text) AND ((f.cod_scenario)::text = (x.cod_scenario)::text) AND ((f.cod_dest1)::text = (x.cod_dest1)::text) AND ((f.cod_dest3)::text = (x.cod_dest3)::text) AND ((f.cod_dest4)::text = (x.cod_dest4)::text) AND ((f.cod_dest5)::text = (x.cod_dest5)::text) AND ((f.cod_valuta)::text = (x.cod_valuta)::text))
  • Buffers: shared hit=1774881, temp read=72198 written=72198
3.          

CTE dslv

4. 852.300 36,971.972 ↓ 667.8 678,520 1

GroupAggregate (cost=3,634,158.25..3,634,475.87 rows=1,016 width=714) (actual time=35,801.559..36,971.972 rows=678,520 loops=1)

  • Group Key: f_1.cod_periodo, f_1.cod_conto, f_1.cod_scenario, f_1.cod_azienda, f_1.cod_dest1, f_1.cod_dest2, f_1.cod_dest3, f_1.cod_dest4, f_1.cod_dest5, f_1.cod_valuta
  • Buffers: shared hit=887442, temp read=36099 written=36099
5.          

Initplan (forGroupAggregate)

6. 0.034 0.034 ↑ 1.0 1 1

Seq Scan on scenario (cost=0.00..1.69 rows=1 width=48) (actual time=0.033..0.034 rows=1 loops=1)

  • Filter: ((cod_scenario)::text = '2019_RF08_N0'::text)
  • Rows Removed by Filter: 54
  • Buffers: shared hit=1
7. 5,443.338 36,119.638 ↓ 66.8 678,520 1

Sort (cost=3,634,156.57..3,634,181.98 rows=10,164 width=682) (actual time=35,801.547..36,119.638 rows=678,520 loops=1)

  • Sort Key: f_1.cod_periodo, f_1.cod_scenario, f_1.cod_dest1, f_1.cod_dest2, f_1.cod_dest3, f_1.cod_dest4, f_1.cod_dest5, f_1.cod_valuta
  • Sort Method: quicksort Memory: 201452kB
  • Buffers: shared hit=887442, temp read=36099 written=36099
8. 219.701 30,676.300 ↓ 66.8 678,520 1

Subquery Scan on f_1 (cost=3,631,735.31..3,633,480.09 rows=10,164 width=682) (actual time=26,450.598..30,676.300 rows=678,520 loops=1)

  • Buffers: shared hit=887439, temp read=36099 written=36099
9. 1,676.720 30,456.599 ↓ 66.8 678,520 1

GroupAggregate (cost=3,631,735.31..3,633,378.45 rows=10,164 width=994) (actual time=26,450.597..30,456.599 rows=678,520 loops=1)

  • Group Key: a.cod_scenario, a.cod_periodo, a.cod_azienda, a.cod_conto, a.cod_dest1, a.cod_dest2, a.cod_dest3, a.cod_dest4, a.cod_dest5, a.cod_categoria, a.cod_valuta, a.cod_valuta_originaria, a.provenienza
  • Filter: ((sum(a.importo) <> '0'::numeric) AND (sum(a.importo) <> '0'::numeric))
  • Rows Removed by Filter: 251104
  • Buffers: shared hit=887439, temp read=36099 written=36099
10. 17,133.554 28,779.879 ↓ 51.5 1,774,052 1

Sort (cost=3,631,735.31..3,631,821.50 rows=34,477 width=962) (actual time=26,450.570..28,779.879 rows=1,774,052 loops=1)

  • Sort Key: a.cod_scenario, a.cod_periodo, a.cod_dest1, a.cod_dest2, a.cod_dest3, a.cod_dest4, a.cod_dest5, a.cod_categoria, a.cod_valuta, a.cod_valuta_originaria, a.provenienza
  • Sort Method: external merge Disk: 288768kB
  • Buffers: shared hit=887439, temp read=36099 written=36099
11. 711.451 11,646.325 ↓ 51.5 1,774,052 1

Subquery Scan on a (cost=1,627,302.55..3,629,136.89 rows=34,477 width=962) (actual time=3,692.089..11,646.325 rows=1,774,052 loops=1)

  • Filter: ((a.cod_periodo)::text < '13'::text)
  • Buffers: shared hit=887439
12. 491.262 10,934.874 ↓ 17.2 1,774,052 1

Append (cost=1,627,302.55..3,627,844.00 rows=103,431 width=994) (actual time=3,692.083..10,934.874 rows=1,774,052 loops=1)

  • Buffers: shared hit=887439
13. 1,371.136 5,003.053 ↓ 17.1 929,624 1

Bitmap Heap Scan on dati_saldi_lordi (cost=1,627,302.55..1,812,640.32 rows=54,364 width=152) (actual time=3,692.081..5,003.053 rows=929,624 loops=1)

  • Recheck Cond: ((((cod_scenario)::text = '2019_RF08_N0'::text) AND ((cod_azienda)::text = 'ETI_0030'::text) AND ((cod_conto)::text = ANY ('{VLM_SLD_LBR_NET,VLM_SLD_TNS_NET}'::text[])) AND ((cod_conto)::text = 'VLM_SLD_LBR_NET'::text)) OR (((cod_scenario)::text = ($0)::text) AND ((cod_azienda)::text = 'ETI_0030'::text) AND ((cod_conto)::text = ANY ('{VLM_SLD_LBR_NET,VLM_SLD_TNS_NET}'::text[])) AND ((cod_conto)::text = 'VLM_SLD_LBR_NET'::text)))
  • Filter: ((cod_categoria)::text = ANY ('{GPS_AMOUNT,GPS_ADJ}'::text[]))
  • Rows Removed by Filter: 1163578
  • Heap Blocks: exact=238467
  • Buffers: shared hit=443720
14. 0.004 3,631.917 ↓ 0.0 0 1

BitmapOr (cost=1,627,302.55..1,627,302.55 rows=171,077 width=0) (actual time=3,631.917..3,631.917 rows=0 loops=1)

  • Buffers: shared hit=205253
15. 2,608.117 2,608.117 ↓ 11.7 1,194,160 1

Bitmap Index Scan on ix_dati_saldi_lordi_01 (cost=0.00..972,293.19 rows=102,334 width=0) (actual time=2,608.117..2,608.117 rows=1,194,160 loops=1)

  • Index Cond: (((cod_scenario)::text = '2019_RF08_N0'::text) AND ((cod_azienda)::text = 'ETI_0030'::text) AND ((cod_conto)::text = ANY ('{VLM_SLD_LBR_NET,VLM_SLD_TNS_NET}'::text[])) AND ((cod_conto)::text = 'VLM_SLD_LBR_NET'::text))
  • Buffers: shared hit=150719
16. 1,023.796 1,023.796 ↓ 13.1 899,042 1

Bitmap Index Scan on ix_dati_saldi_lordi_01 (cost=0.00..654,982.18 rows=68,743 width=0) (actual time=1,023.796..1,023.796 rows=899,042 loops=1)

  • Index Cond: (((cod_scenario)::text = ($0)::text) AND ((cod_azienda)::text = 'ETI_0030'::text) AND ((cod_conto)::text = ANY ('{VLM_SLD_LBR_NET,VLM_SLD_TNS_NET}'::text[])) AND ((cod_conto)::text = 'VLM_SLD_LBR_NET'::text))
  • Buffers: shared hit=54534
17. 298.241 5,440.559 ↓ 17.2 844,428 1

Subquery Scan on *SELECT* 2 (cost=1,627,299.90..1,814,660.04 rows=49,067 width=229) (actual time=3,331.745..5,440.559 rows=844,428 loops=1)

  • Buffers: shared hit=443719
18. 1,871.382 5,142.318 ↓ 17.2 844,428 1

Bitmap Heap Scan on dati_saldi_lordi dati_saldi_lordi_1 (cost=1,627,299.90..1,814,169.37 rows=49,067 width=229) (actual time=3,331.743..5,142.318 rows=844,428 loops=1)

  • Recheck Cond: ((((cod_scenario)::text = '2019_RF08_N0'::text) AND ((cod_azienda)::text = 'ETI_0030'::text) AND ((cod_conto)::text = ANY ('{VLM_SLD_LBR_NET,VLM_SLD_TNS_NET}'::text[])) AND ((cod_conto)::text = 'VLM_SLD_LBR_NET'::text)) OR (((cod_scenario)::text = ($0)::text) AND ((cod_azienda)::text = 'ETI_0030'::text) AND ((cod_conto)::text = ANY ('{VLM_SLD_LBR_NET,VLM_SLD_TNS_NET}'::text[])) AND ((cod_conto)::text = 'VLM_SLD_LBR_NET'::text)))
  • Filter: (((cod_periodo)::text <> '12'::text) AND ((cod_categoria)::text = ANY ('{GPS_AMOUNT,GPS_ADJ}'::text[])))
  • Rows Removed by Filter: 1248774
  • Heap Blocks: exact=238467
  • Buffers: shared hit=443719
19. 0.012 3,270.936 ↓ 0.0 0 1

BitmapOr (cost=1,627,299.90..1,627,299.90 rows=171,077 width=0) (actual time=3,270.936..3,270.936 rows=0 loops=1)

  • Buffers: shared hit=205252
20. 2,343.737 2,343.737 ↓ 11.7 1,194,160 1

Bitmap Index Scan on ix_dati_saldi_lordi_01 (cost=0.00..972,293.19 rows=102,334 width=0) (actual time=2,343.737..2,343.737 rows=1,194,160 loops=1)

  • Index Cond: (((cod_scenario)::text = '2019_RF08_N0'::text) AND ((cod_azienda)::text = 'ETI_0030'::text) AND ((cod_conto)::text = ANY ('{VLM_SLD_LBR_NET,VLM_SLD_TNS_NET}'::text[])) AND ((cod_conto)::text = 'VLM_SLD_LBR_NET'::text))
  • Buffers: shared hit=150719
21. 927.187 927.187 ↓ 13.1 899,042 1

Bitmap Index Scan on ix_dati_saldi_lordi_01 (cost=0.00..654,982.18 rows=68,743 width=0) (actual time=927.187..927.187 rows=899,042 loops=1)

  • Index Cond: (((cod_scenario)::text = ($0)::text) AND ((cod_azienda)::text = 'ETI_0030'::text) AND ((cod_conto)::text = ANY ('{VLM_SLD_LBR_NET,VLM_SLD_TNS_NET}'::text[])) AND ((cod_conto)::text = 'VLM_SLD_LBR_NET'::text))
  • Buffers: shared hit=54533
22.          

CTE vlmtotal

23. 438.994 34,193.193 ↓ 24.8 25,161 1

GroupAggregate (cost=3,634,158.25..3,634,450.46 rows=1,016 width=636) (actual time=33,564.139..34,193.193 rows=25,161 loops=1)

  • Group Key: f_2.cod_periodo, f_2.cod_conto, f_2.cod_scenario, f_2.cod_azienda, f_2.cod_dest1, f_2.cod_dest3, f_2.cod_dest4, f_2.cod_dest5, f_2.cod_valuta
  • Buffers: shared hit=887439, temp read=36099 written=36099
24.          

Initplan (forGroupAggregate)

25. 0.023 0.023 ↑ 1.0 1 1

Seq Scan on scenario scenario_1 (cost=0.00..1.69 rows=1 width=48) (actual time=0.022..0.023 rows=1 loops=1)

  • Filter: ((cod_scenario)::text = '2019_RF08_N0'::text)
  • Rows Removed by Filter: 54
  • Buffers: shared hit=1
26. 3,611.932 33,754.176 ↓ 66.8 678,520 1

Sort (cost=3,634,156.57..3,634,181.98 rows=10,164 width=604) (actual time=33,564.123..33,754.176 rows=678,520 loops=1)

  • Sort Key: f_2.cod_periodo, f_2.cod_scenario, f_2.cod_dest1, f_2.cod_dest3, f_2.cod_dest4, f_2.cod_dest5, f_2.cod_valuta
  • Sort Method: quicksort Memory: 119993kB
  • Buffers: shared hit=887439, temp read=36099 written=36099
27. 223.658 30,142.244 ↓ 66.8 678,520 1

Subquery Scan on f_2 (cost=3,631,735.31..3,633,480.09 rows=10,164 width=604) (actual time=25,928.178..30,142.244 rows=678,520 loops=1)

  • Buffers: shared hit=887439, temp read=36099 written=36099
28. 1,672.756 29,918.586 ↓ 66.8 678,520 1

GroupAggregate (cost=3,631,735.31..3,633,378.45 rows=10,164 width=994) (actual time=25,928.177..29,918.586 rows=678,520 loops=1)

  • Group Key: a_1.cod_scenario, a_1.cod_periodo, a_1.cod_azienda, a_1.cod_conto, a_1.cod_dest1, a_1.cod_dest2, a_1.cod_dest3, a_1.cod_dest4, a_1.cod_dest5, a_1.cod_categoria, a_1.cod_valuta, a_1.cod_valuta_originaria, a_1.provenienza
  • Filter: ((sum(a_1.importo) <> '0'::numeric) AND (sum(a_1.importo) <> '0'::numeric))
  • Rows Removed by Filter: 251104
  • Buffers: shared hit=887439, temp read=36099 written=36099
29. 17,202.207 28,245.830 ↓ 51.5 1,774,052 1

Sort (cost=3,631,735.31..3,631,821.50 rows=34,477 width=962) (actual time=25,928.153..28,245.830 rows=1,774,052 loops=1)

  • Sort Key: a_1.cod_scenario, a_1.cod_periodo, a_1.cod_dest1, a_1.cod_dest2, a_1.cod_dest3, a_1.cod_dest4, a_1.cod_dest5, a_1.cod_categoria, a_1.cod_valuta, a_1.cod_valuta_originaria, a_1.provenienza
  • Sort Method: external merge Disk: 288768kB
  • Buffers: shared hit=887439, temp read=36099 written=36099
30. 738.327 11,043.623 ↓ 51.5 1,774,052 1

Subquery Scan on a_1 (cost=1,627,302.55..3,629,136.89 rows=34,477 width=962) (actual time=3,325.543..11,043.623 rows=1,774,052 loops=1)

  • Filter: ((a_1.cod_periodo)::text < '13'::text)
  • Buffers: shared hit=887439
31. 492.736 10,305.296 ↓ 17.2 1,774,052 1

Append (cost=1,627,302.55..3,627,844.00 rows=103,431 width=994) (actual time=3,325.538..10,305.296 rows=1,774,052 loops=1)

  • Buffers: shared hit=887439
32. 1,087.075 4,353.179 ↓ 17.1 929,624 1

Bitmap Heap Scan on dati_saldi_lordi dati_saldi_lordi_2 (cost=1,627,302.55..1,812,640.32 rows=54,364 width=152) (actual time=3,325.536..4,353.179 rows=929,624 loops=1)

  • Recheck Cond: ((((cod_scenario)::text = '2019_RF08_N0'::text) AND ((cod_azienda)::text = 'ETI_0030'::text) AND ((cod_conto)::text = ANY ('{VLM_SLD_LBR_NET,VLM_SLD_TNS_NET}'::text[])) AND ((cod_conto)::text = 'VLM_SLD_LBR_NET'::text)) OR (((cod_scenario)::text = ($2)::text) AND ((cod_azienda)::text = 'ETI_0030'::text) AND ((cod_conto)::text = ANY ('{VLM_SLD_LBR_NET,VLM_SLD_TNS_NET}'::text[])) AND ((cod_conto)::text = 'VLM_SLD_LBR_NET'::text)))
  • Filter: ((cod_categoria)::text = ANY ('{GPS_AMOUNT,GPS_ADJ}'::text[]))
  • Rows Removed by Filter: 1163578
  • Heap Blocks: exact=238467
  • Buffers: shared hit=443720
33. 0.003 3,266.104 ↓ 0.0 0 1

BitmapOr (cost=1,627,302.55..1,627,302.55 rows=171,077 width=0) (actual time=3,266.104..3,266.104 rows=0 loops=1)

  • Buffers: shared hit=205253
34. 2,343.164 2,343.164 ↓ 11.7 1,194,160 1

Bitmap Index Scan on ix_dati_saldi_lordi_01 (cost=0.00..972,293.19 rows=102,334 width=0) (actual time=2,343.164..2,343.164 rows=1,194,160 loops=1)

  • Index Cond: (((cod_scenario)::text = '2019_RF08_N0'::text) AND ((cod_azienda)::text = 'ETI_0030'::text) AND ((cod_conto)::text = ANY ('{VLM_SLD_LBR_NET,VLM_SLD_TNS_NET}'::text[])) AND ((cod_conto)::text = 'VLM_SLD_LBR_NET'::text))
  • Buffers: shared hit=150719
35. 922.937 922.937 ↓ 13.1 899,042 1

Bitmap Index Scan on ix_dati_saldi_lordi_01 (cost=0.00..654,982.18 rows=68,743 width=0) (actual time=922.937..922.937 rows=899,042 loops=1)

  • Index Cond: (((cod_scenario)::text = ($2)::text) AND ((cod_azienda)::text = 'ETI_0030'::text) AND ((cod_conto)::text = ANY ('{VLM_SLD_LBR_NET,VLM_SLD_TNS_NET}'::text[])) AND ((cod_conto)::text = 'VLM_SLD_LBR_NET'::text))
  • Buffers: shared hit=54534
36. 310.738 5,459.381 ↓ 17.2 844,428 1

Subquery Scan on *SELECT* 2_1 (cost=1,627,299.90..1,814,660.04 rows=49,067 width=229) (actual time=3,338.036..5,459.381 rows=844,428 loops=1)

  • Buffers: shared hit=443719
37. 1,871.265 5,148.643 ↓ 17.2 844,428 1

Bitmap Heap Scan on dati_saldi_lordi dati_saldi_lordi_3 (cost=1,627,299.90..1,814,169.37 rows=49,067 width=229) (actual time=3,338.034..5,148.643 rows=844,428 loops=1)

  • Recheck Cond: ((((cod_scenario)::text = '2019_RF08_N0'::text) AND ((cod_azienda)::text = 'ETI_0030'::text) AND ((cod_conto)::text = ANY ('{VLM_SLD_LBR_NET,VLM_SLD_TNS_NET}'::text[])) AND ((cod_conto)::text = 'VLM_SLD_LBR_NET'::text)) OR (((cod_scenario)::text = ($2)::text) AND ((cod_azienda)::text = 'ETI_0030'::text) AND ((cod_conto)::text = ANY ('{VLM_SLD_LBR_NET,VLM_SLD_TNS_NET}'::text[])) AND ((cod_conto)::text = 'VLM_SLD_LBR_NET'::text)))
  • Filter: (((cod_periodo)::text <> '12'::text) AND ((cod_categoria)::text = ANY ('{GPS_AMOUNT,GPS_ADJ}'::text[])))
  • Rows Removed by Filter: 1248774
  • Heap Blocks: exact=238467
  • Buffers: shared hit=443719
38. 0.004 3,277.378 ↓ 0.0 0 1

BitmapOr (cost=1,627,299.90..1,627,299.90 rows=171,077 width=0) (actual time=3,277.378..3,277.378 rows=0 loops=1)

  • Buffers: shared hit=205252
39. 2,337.072 2,337.072 ↓ 11.7 1,194,160 1

Bitmap Index Scan on ix_dati_saldi_lordi_01 (cost=0.00..972,293.19 rows=102,334 width=0) (actual time=2,337.072..2,337.072 rows=1,194,160 loops=1)

  • Index Cond: (((cod_scenario)::text = '2019_RF08_N0'::text) AND ((cod_azienda)::text = 'ETI_0030'::text) AND ((cod_conto)::text = ANY ('{VLM_SLD_LBR_NET,VLM_SLD_TNS_NET}'::text[])) AND ((cod_conto)::text = 'VLM_SLD_LBR_NET'::text))
  • Buffers: shared hit=150719
40. 940.302 940.302 ↓ 13.1 899,042 1

Bitmap Index Scan on ix_dati_saldi_lordi_01 (cost=0.00..654,982.18 rows=68,743 width=0) (actual time=940.302..940.302 rows=899,042 loops=1)

  • Index Cond: (((cod_scenario)::text = ($2)::text) AND ((cod_azienda)::text = 'ETI_0030'::text) AND ((cod_conto)::text = ANY ('{VLM_SLD_LBR_NET,VLM_SLD_TNS_NET}'::text[])) AND ((cod_conto)::text = 'VLM_SLD_LBR_NET'::text))
  • Buffers: shared hit=54533
41. 38,047.136 38,047.136 ↓ 135,704.0 678,520 1

CTE Scan on dslv f (cost=0.00..25.40 rows=5 width=682) (actual time=35,801.571..38,047.136 rows=678,520 loops=1)

  • Filter: (((cod_dest5)::text <> 'PDT_NA'::text) AND ((cod_azienda)::text = 'ETI_0030'::text))
  • Buffers: shared hit=887442, temp read=36099 written=36099
42. 18.842 34,234.278 ↓ 5,032.2 25,161 1

Hash (cost=22.86..22.86 rows=5 width=604) (actual time=34,234.278..34,234.278 rows=25,161 loops=1)

  • Buckets: 32768 (originally 1024) Batches: 1 (originally 1) Memory Usage: 3494kB
  • Buffers: shared hit=887439, temp read=36099 written=36099
43. 34,215.436 34,215.436 ↓ 5,032.2 25,161 1

CTE Scan on vlmtotal x (cost=0.00..22.86 rows=5 width=604) (actual time=33,564.153..34,215.436 rows=25,161 loops=1)

  • Filter: ((cod_azienda)::text = 'ETI_0030'::text)
  • Buffers: shared hit=887439, temp read=36099 written=36099
Planning time : 2.889 ms