explain.depesz.com

PostgreSQL's explain analyze made readable

Result: a3aN

Settings
# exclusive inclusive rows x rows loops node
1. 5.044 7,843.317 ↑ 81.9 7,102 1

WindowAgg (cost=385,831.93..23,175,109.48 rows=581,437 width=138) (actual time=7,661.356..7,843.317 rows=7,102 loops=1)

2. 2.307 7,660.723 ↑ 81.9 7,102 1

Sort (cost=385,831.93..387,285.53 rows=581,437 width=154) (actual time=7,660.274..7,660.723 rows=7,102 loops=1)

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

GroupAggregate (cost=296,696.14..330,161.48 rows=581,437 width=154) (actual time=6,217.923..7,658.416 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,039.192 6,520.237 ↓ 4.8 2,780,423 1

Sort (cost=296,696.14..298,151.15 rows=582,006 width=93) (actual time=6,217.372..6,520.237 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. 616.795 4,481.045 ↓ 4.8 2,780,423 1

Hash Left Join (cost=189,611.83..240,967.10 rows=582,006 width=93) (actual time=2,686.087..4,481.045 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. 795.883 3,860.555 ↓ 4.8 2,780,423 1

Merge Right Join (cost=181,941.60..230,241.33 rows=582,006 width=97) (actual time=2,682.379..3,860.555 rows=2,780,423 loops=1)

  • Merge Cond: (lmc.lmc_cd_produto_lmc = pro.pro_cd_produto_lmc)
7. 133.342 133.342 ↑ 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.008..133.342 rows=633,949 loops=1)

8. 1,565.979 2,931.330 ↓ 14.5 2,780,423 1

Sort (cost=181,815.88..182,294.93 rows=191,617 width=89) (actual time=2,613.436..2,931.330 rows=2,780,423 loops=1)

  • Sort Key: pro.pro_cd_produto_lmc
  • Sort Method: external sort Disk: 295,424kB
9. 241.306 1,365.351 ↓ 14.5 2,771,308 1

Gather (cost=1,813.49..165,003.54 rows=191,617 width=89) (actual time=5.171..1,365.351 rows=2,771,308 loops=1)

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

Nested Loop Left Join (cost=813.49..144,841.84 rows=79,840 width=89) (actual time=2.796..1,124.045 rows=923,769 loops=3)

11. 1.093 29.757 ↓ 1.0 2,660 3 / 3

Hash Left Join (cost=812.93..16,666.44 rows=2,563 width=76) (actual time=2.615..29.757 rows=2,660 loops=3)

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

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

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

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

14. 9.879 10.437 ↑ 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=0.827..10.437 rows=4,331 loops=3)

  • Recheck Cond: (pun_cd_unidade_negocio = 144)
  • Heap Blocks: exact=1,693
15. 0.558 0.558 ↑ 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=1.674..1.675 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. 0.603 1.269 ↑ 1.0 4,476 3 / 3

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

  • Buckets: 8,192 Batches: 1 Memory Usage: 285kB
18. 0.666 0.666 ↑ 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.015..0.666 rows=4,476 loops=3)

19. 0.001 0.255 ↓ 0.0 0 3 / 3

Hash (cost=493.91..493.91 rows=1 width=8) (actual time=0.252..0.255 rows=0 loops=3)

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

Subquery Scan on y (cost=493.88..493.91 rows=1 width=8) (actual time=0.251..0.254 rows=0 loops=3)

21. 0.002 0.253 ↓ 0.0 0 3 / 3

GroupAggregate (cost=493.88..493.90 rows=1 width=8) (actual time=0.250..0.253 rows=0 loops=3)

  • Group Key: pro_1.pro_cd_produto
22. 0.022 0.251 ↓ 0.0 0 3 / 3

Sort (cost=493.88..493.89 rows=1 width=8) (actual time=0.249..0.251 rows=0 loops=3)

  • Sort Key: pro_1.pro_cd_produto
  • Sort Method: quicksort Memory: 25kB
  • Worker 0: Sort Method: quicksort Memory: 25kB
  • Worker 1: Sort Method: quicksort Memory: 25kB
23. 0.001 0.229 ↓ 0.0 0 3 / 3

Nested Loop (cost=1.41..493.87 rows=1 width=8) (actual time=0.228..0.229 rows=0 loops=3)

24. 0.001 0.228 ↓ 0.0 0 3 / 3

Nested Loop (cost=1.14..489.35 rows=2 width=8) (actual time=0.227..0.228 rows=0 loops=3)

25. 0.227 0.227 ↓ 0.0 0 3 / 3

Index Scan using update_ven_js_dados_idx on venda ven (cost=0.56..2.79 rows=1 width=4) (actual time=0.227..0.227 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))
26. 0.000 0.000 ↓ 0.0 0 / 3

Index Scan using venda_item_idx_venda_rebuild_2 on venda_item vei (cost=0.57..481.50 rows=506 width=12) (never executed)

  • Index Cond: (vei_cd_venda = ven.ven_cd_venda)
27. 0.000 0.000 ↓ 0.0 0 / 3

Index Only Scan using produto_pro_cd_produto_idx on produto pro_1 (cost=0.28..2.26 rows=1 width=4) (never executed)

  • Index Cond: (pro_cd_produto = vei.vei_cd_produto)
  • Heap Fetches: 0
28. 912.380 912.380 ↑ 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.006..0.343 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)
29. 0.005 3.695 ↑ 153.3 6 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
30. 0.001 3.690 ↑ 153.3 6 1

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

31. 1.125 3.689 ↑ 153.3 6 1

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

  • Group Key: lmc_1.lmc_cd_produto_lmc
32. 2.158 2.564 ↓ 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.499..2.564 rows=9,121 loops=1)

  • Recheck Cond: (lmc_cd_unidade_negocio = 144)
  • Filter: (lmc_dt_lmc <= '2020-09-10'::date)
  • Heap Blocks: exact=923
33. 0.406 0.406 ↓ 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.406..0.406 rows=9,121 loops=1)

  • Index Cond: (lmc_cd_unidade_negocio = 144)
34.          

SubPlan (for WindowAgg)

35. 7.102 177.550 ↑ 1.0 1 7,102

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

36. 7.102 170.448 ↑ 1.0 1 7,102

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

  • Sort Key: alp.alp_dt_alteracao DESC
  • Sort Method: quicksort Memory: 25kB
37. 2.174 163.346 ↓ 2.0 2 7,102

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

38. 63.918 63.918 ↑ 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.004..0.009 rows=2 loops=7,102)

  • Index Cond: (app_cd_produto = pro.pro_cd_produto)
39. 97.254 97.254 ↑ 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.006..0.006 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 : 7.243 ms
Execution time : 7,922.655 ms