explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 6JfA : Analise normalizada

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 9,173.210 57,738.061 ↑ 15,106.6 88 1

GroupAggregate (cost=1,542,631.87..1,662,276.52 rows=1,329,385 width=547) (actual time=45,633.500..57,738.061 rows=88 loops=1)

  • Group Key: (to_char(v.datahorafechamento, 'YYYY-MM'::text)), unidadenegocio.codigo, NULL::text, NULL::text, NULL::text, NULL::text, NULL::text, NULL::text, NULL::text, NULL::text, NULL::text, NULL::text, NULL::text
2. 13,330.306 48,564.851 ↓ 3.4 4,494,557 1

Sort (cost=1,542,631.87..1,545,955.34 rows=1,329,385 width=408) (actual time=45,460.178..48,564.851 rows=4,494,557 loops=1)

  • Sort Key: (to_char(v.datahorafechamento, 'YYYY-MM'::text)), unidadenegocio.codigo
  • Sort Method: external merge Disk: 233008kB
3. 3,228.190 35,234.545 ↓ 3.4 4,494,557 1

Hash Left Join (cost=572,136.36..916,682.95 rows=1,329,385 width=408) (actual time=15,181.274..35,234.545 rows=4,494,557 loops=1)

  • Hash Cond: (p.id = cp.produtoid)
4. 435.085 31,992.510 ↓ 2.1 1,997,543 1

Hash Left Join (cost=570,440.64..885,158.42 rows=960,836 width=40) (actual time=15,166.340..31,992.510 rows=1,997,543 loops=1)

  • Hash Cond: (v.unidadenegocioid = unidadenegocio.id)
5. 611.526 31,557.394 ↓ 2.1 1,997,543 1

Hash Left Join (cost=570,439.31..871,945.59 rows=960,836 width=45) (actual time=15,166.300..31,557.394 rows=1,997,543 loops=1)

  • Hash Cond: (i.embalagemid = e.id)
6. 2,291.924 30,903.340 ↓ 2.1 1,997,543 1

Hash Right Join (cost=565,860.97..854,155.76 rows=960,836 width=45) (actual time=15,123.713..30,903.340 rows=1,997,543 loops=1)

  • Hash Cond: (m.id = i.movimentacaoestoqueid)
7. 13,534.613 14,217.980 ↓ 1.0 2,372,867 1

Bitmap Heap Scan on movimentacaoestoque m (cost=61,073.87..307,774.37 rows=2,324,433 width=14) (actual time=689.436..14,217.980 rows=2,372,867 loops=1)

  • Recheck Cond: ((datahora >= '2018-01-01 00:00:00'::timestamp without time zone) AND (datahora <= '2018-12-31 23:59:59'::timestamp without time zone))
  • Rows Removed by Index Recheck: 1672647
  • Heap Blocks: exact=30439 lossy=53173
8. 683.367 683.367 ↓ 1.0 2,374,972 1

Bitmap Index Scan on idx_movimentacaoestoque_datahora_unidadenegocioid (cost=0.00..60,492.76 rows=2,324,433 width=0) (actual time=683.367..683.367 rows=2,374,972 loops=1)

  • Index Cond: ((datahora >= '2018-01-01 00:00:00'::timestamp without time zone) AND (datahora <= '2018-12-31 23:59:59'::timestamp without time zone))
9. 759.508 14,393.436 ↓ 2.1 1,997,543 1

Hash (cost=484,331.65..484,331.65 rows=960,836 width=47) (actual time=14,393.436..14,393.436 rows=1,997,543 loops=1)

  • Buckets: 65536 (originally 65536) Batches: 2048 (originally 32) Memory Usage: 4055kB
10. 1,301.385 13,633.928 ↓ 2.1 1,997,543 1

Hash Right Join (cost=232,091.24..484,331.65 rows=960,836 width=47) (actual time=8,990.535..13,633.928 rows=1,997,543 loops=1)

  • Hash Cond: (i.vendaid = v.id)
11. 3,350.993 5,761.230 ↓ 1.0 1,997,530 1

Bitmap Heap Scan on itemvenda i (cost=53,954.25..257,294.85 rows=1,941,640 width=39) (actual time=2,417.592..5,761.230 rows=1,997,530 loops=1)

  • Recheck Cond: ((datahora >= '2018-01-01 00:00:00'::timestamp without time zone) AND (datahora <= '2018-12-31 23:59:59'::timestamp without time zone))
  • Heap Blocks: exact=35820
12. 2,410.237 2,410.237 ↓ 1.0 1,997,815 1

Bitmap Index Scan on cidx_itemvenda_datahora_unidadenegocioid (cost=0.00..53,468.83 rows=1,941,640 width=0) (actual time=2,410.237..2,410.237 rows=1,997,815 loops=1)

  • Index Cond: ((datahora >= '2018-01-01 00:00:00'::timestamp without time zone) AND (datahora <= '2018-12-31 23:59:59'::timestamp without time zone))
13. 254.637 6,571.313 ↑ 1.0 945,752 1

Hash (cost=158,619.54..158,619.54 rows=960,836 width=40) (actual time=6,571.313..6,571.313 rows=945,752 loops=1)

  • Buckets: 65536 Batches: 32 Memory Usage: 2596kB
14. 6,063.327 6,316.676 ↑ 1.0 945,752 1

Bitmap Heap Scan on venda v (cost=36,389.00..158,619.54 rows=960,836 width=40) (actual time=258.893..6,316.676 rows=945,752 loops=1)

  • Recheck Cond: ((datahorafechamento >= '2018-01-01 00:00:00'::timestamp without time zone) AND (datahorafechamento <= '2018-12-31 23:59:59'::timestamp without time zone))
  • Rows Removed by Index Recheck: 807223
  • Heap Blocks: exact=28244 lossy=26576
15. 253.349 253.349 ↑ 1.0 947,973 1

Bitmap Index Scan on idx_venda_datahorafechamento_unidadenegocioid (cost=0.00..36,148.79 rows=960,836 width=0) (actual time=253.349..253.349 rows=947,973 loops=1)

  • Index Cond: ((datahorafechamento >= '2018-01-01 00:00:00'::timestamp without time zone) AND (datahorafechamento <= '2018-12-31 23:59:59'::timestamp without time zone))
16. 6.858 42.528 ↓ 1.0 44,249 1

Hash (cost=4,026.76..4,026.76 rows=44,126 width=16) (actual time=42.528..42.528 rows=44,249 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 2587kB
17. 13.611 35.670 ↓ 1.0 44,249 1

Hash Left Join (cost=1,879.77..4,026.76 rows=44,126 width=16) (actual time=16.015..35.670 rows=44,249 loops=1)

  • Hash Cond: (e.produtoid = p.id)
18. 6.119 6.119 ↓ 1.0 44,249 1

Seq Scan on embalagem e (cost=0.00..1,540.26 rows=44,126 width=16) (actual time=0.019..6.119 rows=44,249 loops=1)

19. 6.296 15.940 ↓ 1.0 41,129 1

Hash (cost=1,367.12..1,367.12 rows=41,012 width=24) (actual time=15.940..15.940 rows=41,129 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 2537kB
20. 9.644 9.644 ↓ 1.0 41,129 1

Seq Scan on produto p (cost=0.00..1,367.12 rows=41,012 width=24) (actual time=0.010..9.644 rows=41,129 loops=1)

21. 0.004 0.031 ↑ 1.0 15 1

Hash (cost=1.15..1.15 rows=15 width=11) (actual time=0.031..0.031 rows=15 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
22. 0.027 0.027 ↑ 1.0 15 1

Seq Scan on unidadenegocio (cost=0.00..1.15 rows=15 width=11) (actual time=0.026..0.027 rows=15 loops=1)

23. 7.193 13.845 ↑ 1.0 56,486 1

Hash (cost=986.43..986.43 rows=56,743 width=16) (actual time=13.845..13.845 rows=56,486 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 3160kB
24. 6.652 6.652 ↑ 1.0 56,486 1

Seq Scan on classificacaoproduto cp (cost=0.00..986.43 rows=56,743 width=16) (actual time=0.011..6.652 rows=56,486 loops=1)