explain.depesz.com

PostgreSQL's explain analyze made readable

Result: mLFR

Settings
# exclusive inclusive rows x rows loops node
1. 5.989 7,643.037 ↑ 81.9 7,102 1

WindowAgg (cost=385,349.55..23,174,627.10 rows=581,437 width=138) (actual time=7,573.719..7,643.037 rows=7,102 loops=1)

2. 2.357 7,573.130 ↑ 81.9 7,102 1

Sort (cost=385,349.55..386,803.15 rows=581,437 width=154) (actual time=7,572.634..7,573.130 rows=7,102 loops=1)

  • Sort Key: pro.pro_cd_produto_lmc
  • Sort Method: quicksort Memory: 1,300kB
3. 1,117.606 7,570.773 ↑ 81.9 7,102 1

GroupAggregate (cost=296,213.76..329,679.10 rows=581,437 width=154) (actual time=6,156.975..7,570.773 rows=7,102 loops=1)

  • Group Key: pro.pro_cd_produto, pgr.pgr_cd_produto_grupo, pun.pun_cd_produto_unidade_negocio
  • Filter: ((COALESCE(sum(prm.prm_vl_quantidade_movimento), '0'::numeric) <> '0'::numeric) OR (pun.pun_fl_ativo <> 'N'::bpchar))
  • Rows Removed by Filter: 878
4. 2,013.022 6,453.167 ↓ 4.8 2,780,423 1

Sort (cost=296,213.76..297,668.77 rows=582,006 width=93) (actual time=6,156.418..6,453.167 rows=2,780,423 loops=1)

  • Sort Key: pro.pro_cd_produto, pgr.pgr_cd_produto_grupo, pun.pun_cd_produto_unidade_negocio
  • Sort Method: external merge Disk: 271,432kB
5. 608.035 4,440.145 ↓ 4.8 2,780,423 1

Hash Left Join (cost=189,129.45..240,484.72 rows=582,006 width=93) (actual time=2,641.321..4,440.145 rows=2,780,423 loops=1)

  • Hash Cond: ((lmc.lmc_cd_produto_lmc = t.cd_produto_lmc) AND (lmc.lmc_dt_lmc = t.data_ultimo_lmc))
6. 812.293 3,828.423 ↓ 4.8 2,780,423 1

Merge Right Join (cost=181,459.22..229,758.95 rows=582,006 width=97) (actual time=2,637.620..3,828.423 rows=2,780,423 loops=1)

  • Merge Cond: (lmc.lmc_cd_produto_lmc = pro.pro_cd_produto_lmc)
7. 127.940 127.940 ↑ 1.6 633,949 1

Index Scan using lmc_lmc_cd_produto_lmc_idx on lmc (cost=0.42..37,200.39 rows=997,988 width=8) (actual time=0.013..127.940 rows=633,949 loops=1)

8. 1,564.490 2,888.190 ↓ 14.5 2,780,423 1

Sort (cost=181,333.50..181,812.55 rows=191,617 width=89) (actual time=2,569.771..2,888.190 rows=2,780,423 loops=1)

  • Sort Key: pro.pro_cd_produto_lmc
  • Sort Method: external sort Disk: 295,432kB
9. 230.124 1,323.700 ↓ 14.5 2,771,308 1

Gather (cost=1,331.11..164,521.16 rows=191,617 width=89) (actual time=7.711..1,323.700 rows=2,771,308 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
10. 177.051 1,093.576 ↓ 11.6 923,769 3 / 3

Nested Loop Left Join (cost=331.11..144,359.46 rows=79,840 width=89) (actual time=4.026..1,093.576 rows=923,769 loops=3)

11. 1.167 25.425 ↓ 1.0 2,660 3 / 3

Hash Left Join (cost=330.55..16,184.06 rows=2,563 width=76) (actual time=3.979..25.425 rows=2,660 loops=3)

  • Hash Cond: (pro.pro_cd_produto = y.pro_cd_produto)
12. 1.521 24.216 ↓ 1.0 2,660 3 / 3

Hash Join (cost=319.01..16,165.79 rows=2,563 width=72) (actual time=3.922..24.216 rows=2,660 loops=3)

  • Hash Cond: (pro.pro_cd_produto_grupo = pgr.pgr_cd_produto_grupo)
13. 2.156 20.221 ↓ 1.0 2,660 3 / 3

Nested Loop (cost=149.30..15,989.34 rows=2,563 width=60) (actual time=1.405..20.221 rows=2,660 loops=3)

14. 4.183 5.071 ↑ 1.3 4,331 3 / 3

Parallel Bitmap Heap Scan on produto_unidade_negocio pun (cost=148.87..8,843.43 rows=5,734 width=15) (actual time=1.347..5.071 rows=4,331 loops=3)

  • Recheck Cond: (pun_cd_unidade_negocio = 144)
  • Heap Blocks: exact=1,493
15. 0.888 0.888 ↑ 1.1 12,994 1 / 3

Bitmap Index Scan on unidade_negocio_produto_idx (cost=0.00..145.43 rows=13,761 width=0) (actual time=2.665..2.665 rows=12,994 loops=1)

  • Index Cond: (pun_cd_unidade_negocio = 144)
16. 12.994 12.994 ↑ 1.0 1 12,994 / 3

Index Scan using produto_pro_cd_produto_idx2 on produto pro (cost=0.42..1.25 rows=1 width=49) (actual time=0.003..0.003 rows=1 loops=12,994)

  • Index Cond: (pro_cd_produto = pun.pun_cd_produto)
17. 1.295 2.474 ↑ 1.0 4,476 3 / 3

Hash (cost=113.76..113.76 rows=4,476 width=16) (actual time=2.474..2.474 rows=4,476 loops=3)

  • Buckets: 8,192 Batches: 1 Memory Usage: 285kB
18. 1.179 1.179 ↑ 1.0 4,476 3 / 3

Seq Scan on produto_grupo pgr (cost=0.00..113.76 rows=4,476 width=16) (actual time=0.026..1.179 rows=4,476 loops=3)

19. 0.001 0.042 ↓ 0.0 0 3 / 3

Hash (cost=10.29..10.29 rows=100 width=8) (actual time=0.040..0.042 rows=0 loops=3)

  • Buckets: 1,024 Batches: 1 Memory Usage: 8kB
20. 0.001 0.041 ↓ 0.0 0 3 / 3

Subquery Scan on y (cost=7.54..10.29 rows=100 width=8) (actual time=0.039..0.041 rows=0 loops=3)

21. 0.003 0.040 ↓ 0.0 0 3 / 3

HashAggregate (cost=7.54..9.29 rows=100 width=8) (actual time=0.038..0.040 rows=0 loops=3)

  • Group Key: ((vei.value ->> 'vei_cd_produto'::text))::integer
22. 0.002 0.037 ↓ 0.0 0 3 / 3

Nested Loop (cost=0.57..6.29 rows=100 width=36) (actual time=0.036..0.037 rows=0 loops=3)

23. 0.035 0.035 ↓ 0.0 0 3 / 3

Index Scan using update_ven_js_dados_idx on venda ven (cost=0.56..2.79 rows=1 width=1,420) (actual time=0.035..0.035 rows=0 loops=3)

  • Index Cond: ((ven_cd_unidade_negocio = 144) AND (ven_dt_fiscal >= '2020-09-08'::date) AND (ven_dt_fiscal <= '2020-09-10'::date))
24. 0.000 0.000 ↓ 0.0 0 / 3

Function Scan on jsonb_array_elements vei (cost=0.01..1.76 rows=100 width=32) (never executed)

  • Filter: (((value ->> 'pro_cd_produto_lmc'::text))::integer IS NOT NULL)
25. 891.100 891.100 ↑ 2.9 347 7,980 / 3

Index Scan using produto_movimento_idx_produto_unidade_negocio on produto_movimento prm (cost=0.56..39.99 rows=1,002 width=17) (actual time=0.005..0.335 rows=347 loops=7,980)

  • Index Cond: (prm_cd_produto_unidade_negocio = pun.pun_cd_produto_unidade_negocio)
  • Filter: (prm_dt_movimento <= '2020-09-10'::date)
26. 0.004 3.687 ↑ 153.3 6 1

Hash (cost=7,656.43..7,656.43 rows=920 width=8) (actual time=3.686..3.687 rows=6 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
27. 0.002 3.683 ↑ 153.3 6 1

Subquery Scan on t (cost=7,638.03..7,656.43 rows=920 width=8) (actual time=3.677..3.683 rows=6 loops=1)

28. 1.161 3.681 ↑ 153.3 6 1

HashAggregate (cost=7,638.03..7,647.23 rows=920 width=8) (actual time=3.676..3.681 rows=6 loops=1)

  • Group Key: lmc_1.lmc_cd_produto_lmc
29. 2.122 2.520 ↓ 1.0 9,121 1

Bitmap Heap Scan on lmc lmc_1 (cost=97.02..7,593.46 rows=8,915 width=8) (actual time=0.498..2.520 rows=9,121 loops=1)

  • Recheck Cond: (lmc_cd_unidade_negocio = 144)
  • Filter: (lmc_dt_lmc <= '2020-09-10'::date)
  • Heap Blocks: exact=923
30. 0.398 0.398 ↓ 1.0 9,121 1

Bitmap Index Scan on lmc_lmc_cd_unidade_negocio_idx (cost=0.00..94.79 rows=8,915 width=0) (actual time=0.398..0.398 rows=9,121 loops=1)

  • Index Cond: (lmc_cd_unidade_negocio = 144)
31.          

SubPlan (for WindowAgg)

32. 0.000 63.918 ↑ 1.0 1 7,102

Limit (cost=39.17..39.17 rows=1 width=10) (actual time=0.009..0.009 rows=1 loops=7,102)

33. 7.102 63.918 ↑ 1.0 1 7,102

Sort (cost=39.17..39.17 rows=1 width=10) (actual time=0.009..0.009 rows=1 loops=7,102)

  • Sort Key: alp.alp_dt_alteracao DESC
  • Sort Method: quicksort Memory: 25kB
34. 3.092 56.816 ↓ 2.0 2 7,102

Nested Loop (cost=0.84..39.16 rows=1 width=10) (actual time=0.003..0.008 rows=2 loops=7,102)

35. 21.306 21.306 ↑ 5.5 2 7,102

Index Scan using altera_preco_produto_idx_produto on altera_preco_produto app (cost=0.42..10.09 rows=11 width=10) (actual time=0.001..0.003 rows=2 loops=7,102)

  • Index Cond: (app_cd_produto = pro.pro_cd_produto)
36. 32.418 32.418 ↑ 1.0 1 16,209

Index Scan using altera_preco_pkey on altera_preco alp (cost=0.42..2.64 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=16,209)

  • Index Cond: (alp_cd_altera_preco = app.app_cd_altera_preco)
  • Filter: ((alp_dt_alteracao <= '2020-09-10'::date) AND (alp_cd_unidade_negocio = 144))
  • Rows Removed by Filter: 0
Planning time : 12.261 ms
Execution time : 7,721.142 ms