explain.depesz.com

PostgreSQL's explain analyze made readable

Result: aZU2

Settings
# exclusive inclusive rows x rows loops node
1. 6,089.879 44,552.453 ↓ 851,416.0 851,416 1

Subquery Scan on q (cost=3,634,498.82..3,660,317.88 rows=1 width=740) (actual time=37,522.464..44,552.453 rows=851,416 loops=1)

  • Buffers: shared hit=897469, temp read=36099 written=36099
2. 893.391 38,462.574 ↓ 851,416.0 851,416 1

Hash Join (cost=3,634,498.82..3,660,317.86 rows=1 width=624) (actual time=37,508.785..38,462.574 rows=851,416 loops=1)

  • Hash Cond: (((z.tgk_pdt_cod)::text = (f.cod_dest5)::text) AND ((z.tgk_pha_cod)::text = (f.cod_scenario)::text) AND ((z.tgk_mth_cod)::text = (f.cod_periodo)::text))
  • Buffers: shared hit=897469, temp read=36099 written=36099
3.          

CTE dslv

4. 578.173 36,274.080 ↓ 667.8 678,520 1

GroupAggregate (cost=3,634,158.25..3,634,475.87 rows=1,016 width=714) (actual time=35,553.099..36,274.080 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.039 0.039 ↑ 1.0 1 1

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

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

Sort (cost=3,634,156.57..3,634,181.98 rows=10,164 width=682) (actual time=35,553.089..35,695.868 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. 221.755 30,568.889 ↓ 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,350.076..30,568.889 rows=678,520 loops=1)

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

GroupAggregate (cost=3,631,735.31..3,633,378.45 rows=10,164 width=994) (actual time=26,350.074..30,347.134 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. 16,922.056 28,665.415 ↓ 51.5 1,774,052 1

Sort (cost=3,631,735.31..3,631,821.50 rows=34,477 width=962) (actual time=26,350.053..28,665.415 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. 713.573 11,743.359 ↓ 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,727.993..11,743.359 rows=1,774,052 loops=1)

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

Append (cost=1,627,302.55..3,627,844.00 rows=103,431 width=994) (actual time=3,727.987..11,029.786 rows=1,774,052 loops=1)

  • Buffers: shared hit=887439
13. 1,378.184 5,044.841 ↓ 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,727.986..5,044.841 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,666.657 ↓ 0.0 0 1

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

  • Buffers: shared hit=205253
15. 2,623.011 2,623.011 ↓ 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,623.011..2,623.011 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,043.642 1,043.642 ↓ 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,043.642..1,043.642 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. 300.416 5,490.299 ↓ 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,371.227..5,490.299 rows=844,428 loops=1)

  • Buffers: shared hit=443719
18. 1,879.930 5,189.883 ↓ 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,371.223..5,189.883 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.005 3,309.953 ↓ 0.0 0 1

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

  • Buffers: shared hit=205252
20. 2,352.338 2,352.338 ↓ 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,352.338..2,352.338 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. 957.610 957.610 ↓ 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=957.610..957.610 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. 286.853 286.853 ↓ 3.4 305,781 1

Seq Scan on gps_noram_ess_imp_dat2 z (cost=0.00..24,802.00 rows=90,403 width=65) (actual time=3.518..286.853 rows=305,781 loops=1)

  • Filter: (((tgk_pdt_cod)::text <> 'PDT_NA'::text) AND ((tgk_ety_cod)::text = 'ETI_0030'::text) AND ((src_sys_cod)::text = 'LOG'::text) AND ((tgk_cus_cod)::text = 'CUS_GEN'::text) AND ((tgk_cst_dst_cod)::text = ANY ('{DST_GEN_IFO,DST_GEN_US,DST_GEN_FTC,DST_GEN_WHS,DST_GEN_OLC}'::text[])))
  • Rows Removed by Filter: 257076
  • Buffers: shared hit=10027
23. 307.859 37,282.330 ↓ 135,704.0 678,520 1

Hash (cost=22.86..22.86 rows=5 width=370) (actual time=37,282.330..37,282.330 rows=678,520 loops=1)

  • Buckets: 1048576 (originally 1024) Batches: 1 (originally 1) Memory Usage: 78427kB
  • Buffers: shared hit=887442, temp read=36099 written=36099
24. 36,974.471 36,974.471 ↓ 135,704.0 678,520 1

CTE Scan on dslv f (cost=0.00..22.86 rows=5 width=370) (actual time=35,553.118..36,974.471 rows=678,520 loops=1)

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