explain.depesz.com

PostgreSQL's explain analyze made readable

Result: UHwm

Settings
# exclusive inclusive rows x rows loops node
1. 214.399 41,925.453 ↓ 6.5 155,477 1

Hash Join (cost=231,319.18..477,250.67 rows=23,778 width=720) (actual time=4,081.296..41,925.453 rows=155,477 loops=1)

  • Hash Cond: (prf.cod_loc = loc.cod_loc)
2.          

Initplan (for Hash Join)

3. 0.012 0.012 ↑ 1.0 1 1

Index Scan using t_lda_maes_fechas_hzt_pkey on t_lda_maes_fechas_hzt (cost=0.28..8.29 rows=1 width=2) (actual time=0.012..0.012 rows=1 loops=1)

  • Index Cond: (fecha = '2020-01-07 00:00:00'::timestamp without time zone)
4. 21,466.671 41,698.442 ↓ 10.4 157,654 1

Nested Loop (cost=228,065.40..473,781.45 rows=15,092 width=702) (actual time=4,068.661..41,698.442 rows=157,654 loops=1)

  • Join Filter: (art_com.cod_articulo = prf.cod_articulo)
5.          

CTE articulo

6. 4.147 91.792 ↓ 3.9 6,374 1

Bitmap Heap Scan on t_lda_maes_art_estr_com (cost=17,696.86..23,761.18 rows=1,629 width=31) (actual time=87.677..91.792 rows=6,374 loops=1)

  • Recheck Cond: (((cod_n1)::text = ANY ('{0001,0002,0003,0004,0005,0008}'::text[])) AND ((cod_n2)::text = '0001'::text) AND (nivel = '5'::numeric) AND (cod_est_art = ANY ('{1,2,3}'::numeric[])))
  • Heap Blocks: exact=161
7. 0.045 87.645 ↓ 0.0 0 1

BitmapAnd (cost=17,696.86..17,696.86 rows=1,629 width=0) (actual time=87.645..87.645 rows=0 loops=1)

8. 11.724 11.724 ↓ 1.3 31,988 1

Bitmap Index Scan on t_lda_maes_art_estr_com_i2 (cost=0.00..663.48 rows=25,211 width=0) (actual time=11.724..11.724 rows=31,988 loops=1)

  • Index Cond: (((cod_n1)::text = ANY ('{0001,0002,0003,0004,0005,0008}'::text[])) AND ((cod_n2)::text = '0001'::text))
9. 75.876 75.876 ↑ 1.0 701,702 1

Bitmap Index Scan on t_lda_maes_art_estr_com_i3 (cost=0.00..17,032.32 rows=703,100 width=0) (actual time=75.876..75.876 rows=701,702 loops=1)

  • Index Cond: ((nivel = '5'::numeric) AND (cod_est_art = ANY ('{1,2,3}'::numeric[])))
10. 391.397 4,466.371 ↓ 4.1 157,654 1

Hash Join (cost=204,303.66..206,986.59 rows=38,413 width=140) (actual time=4,067.954..4,466.371 rows=157,654 loops=1)

  • Hash Cond: (art_com.cod_articulo = crr.cod_articulo)
11. 1.270 96.284 ↓ 3.9 6,374 1

Nested Loop (cost=0.28..57.16 rows=1,629 width=121) (actual time=87.692..96.284 rows=6,374 loops=1)

12. 0.021 0.021 ↑ 1.0 1 1

Index Scan using t_lda_maes_fechas_hzt_pkey on t_lda_maes_fechas_hzt fec (cost=0.28..8.29 rows=1 width=5) (actual time=0.007..0.021 rows=1 loops=1)

  • Index Cond: (fecha = '2020-01-07 00:00:00'::timestamp without time zone)
13. 94.993 94.993 ↓ 3.9 6,374 1

CTE Scan on articulo art_com (cost=0.00..32.58 rows=1,629 width=116) (actual time=87.682..94.993 rows=6,374 loops=1)

14. 1,352.620 3,978.690 ↓ 9.0 5,407,358 1

Hash (cost=196,793.16..196,793.16 rows=600,818 width=19) (actual time=3,978.690..3,978.690 rows=5,407,358 loops=1)

  • Buckets: 8388608 (originally 1048576) Batches: 1 (originally 1) Memory Usage: 340202kB
15. 2,626.070 2,626.070 ↓ 9.0 5,407,358 1

Seq Scan on t_lda_maes_cen_ref_rdf crr (cost=0.00..196,793.16 rows=600,818 width=19) (actual time=0.015..2,626.070 rows=5,407,358 loops=1)

  • Filter: (('2020-01-07 00:00:00'::timestamp without time zone >= trunc(activo)) AND ('2020-01-07 00:00:00'::timestamp without time zone < trunc(inactivo)))
16. 14,504.168 14,504.168 ↑ 1.0 1 157,654

Index Scan using t_lda_approved_forecast_f21_pkey on t_lda_approved_forecast_f21 prf (cost=0.56..4.46 rows=1 width=533) (actual time=0.092..0.092 rows=1 loops=157,654)

  • Index Cond: ((fec_ini_sem = ('2020-01-07 00:00:00'::timestamp without time zone - '1'::numeric)) AND (cod_loc = crr.cod_loc) AND (cod_articulo = crr.cod_articulo))
17.          

SubPlan (for Nested Loop)

18. 157.654 1,261.232 ↑ 1.0 1 157,654

Aggregate (cost=4.45..4.46 rows=1 width=8) (actual time=0.008..0.008 rows=1 loops=157,654)

19. 1,103.578 1,103.578 ↓ 0.0 0 157,654

Index Only Scan using t_lda_prev_promo_i1 on t_lda_prev_promo (cost=0.43..4.45 rows=1 width=0) (actual time=0.007..0.007 rows=0 loops=157,654)

  • Index Cond: ((cod_loc = prf.cod_loc) AND (cod_articulo = prf.cod_articulo))
  • Heap Fetches: 0
20. 0.858 12.600 ↓ 1.0 3,914 1

Hash (cost=3,196.78..3,196.78 rows=3,896 width=29) (actual time=12.600..12.600 rows=3,914 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 289kB
21. 1.346 11.742 ↓ 1.0 3,914 1

Hash Join (cost=338.44..3,196.78 rows=3,896 width=29) (actual time=2.619..11.742 rows=3,914 loops=1)

  • Hash Cond: (ln.cod_nivel_estr_loc = nel.cod_nivel_estr_loc)
22. 1.656 8.184 ↓ 1.0 3,914 1

Nested Loop (cost=94.90..2,904.07 rows=3,896 width=17) (actual time=0.390..8.184 rows=3,914 loops=1)

23. 2.264 2.614 ↓ 1.0 3,914 1

Bitmap Heap Scan on loc_nel ln (cost=94.61..860.31 rows=3,896 width=11) (actual time=0.382..2.614 rows=3,914 loops=1)

  • Recheck Cond: (cod_tp_estr_loc = '29'::numeric)
  • Heap Blocks: exact=341
24. 0.350 0.350 ↓ 1.0 3,914 1

Bitmap Index Scan on loc_nel_pkey (cost=0.00..93.64 rows=3,896 width=0) (actual time=0.350..0.350 rows=3,914 loops=1)

  • Index Cond: (cod_tp_estr_loc = '29'::numeric)
25. 3.914 3.914 ↑ 1.0 1 3,914

Index Only Scan using localizaciones_pkey on localizaciones loc (cost=0.29..0.52 rows=1 width=6) (actual time=0.001..0.001 rows=1 loops=3,914)

  • Index Cond: (cod_loc = ln.cod_loc)
  • Heap Fetches: 280
26. 0.993 2.212 ↓ 1.0 5,574 1

Hash (cost=174.35..174.35 rows=5,535 width=24) (actual time=2.212..2.212 rows=5,574 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 389kB
27. 1.219 1.219 ↓ 1.0 5,574 1

Seq Scan on niveles_estr_loc nel (cost=0.00..174.35 rows=5,535 width=24) (actual time=0.010..1.219 rows=5,574 loops=1)