explain.depesz.com

PostgreSQL's explain analyze made readable

Result: DcXx

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 9,746.446 85,739.696 ↑ 14,712.1 97 1

GroupAggregate (cost=1,635,589.35..1,764,025.92 rows=1,427,073 width=547) (actual time=72,703.445..85,739.696 rows=97 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. 15,093.434 75,993.250 ↓ 3.3 4,766,857 1

Sort (cost=1,635,589.35..1,639,157.04 rows=1,427,073 width=408) (actual time=72,525.161..75,993.250 rows=4,766,857 loops=1)

  • Sort Key: (to_char(v.datahorafechamento, 'YYYY-MM'::text)), unidadenegocio.codigo
  • Sort Method: external merge Disk: 246048kB
3. 3,743.771 60,899.816 ↓ 3.3 4,766,857 1

Hash Left Join (cost=601,529.11..962,917.47 rows=1,427,073 width=408) (actual time=29,822.736..60,899.816 rows=4,766,857 loops=1)

  • Hash Cond: (p.id = cp.produtoid)
4. 484.982 57,141.804 ↓ 2.1 2,150,509 1

Hash Left Join (cost=599,833.39..929,201.02 rows=1,031,442 width=40) (actual time=29,808.431..57,141.804 rows=2,150,509 loops=1)

  • Hash Cond: (v.unidadenegocioid = unidadenegocio.id)
5. 782.961 56,656.805 ↓ 2.1 2,150,509 1

Hash Left Join (cost=599,832.05..915,017.35 rows=1,031,442 width=45) (actual time=29,808.405..56,656.805 rows=2,150,509 loops=1)

  • Hash Cond: (i.embalagemid = e.id)
6. 20,915.806 55,829.553 ↓ 2.1 2,150,509 1

Hash Right Join (cost=595,253.72..896,256.69 rows=1,031,442 width=45) (actual time=29,764.052..55,829.553 rows=2,150,509 loops=1)

  • Hash Cond: (m.id = i.movimentacaoestoqueid)
7. 5,259.450 5,845.794 ↑ 1.0 2,705,001 1

Bitmap Heap Scan on movimentacaoestoque m (cost=71,837.09..324,630.79 rows=2,734,113 width=14) (actual time=597.153..5,845.794 rows=2,705,001 loops=1)

  • Recheck Cond: ((datahora >= '2017-01-01 00:00:00'::timestamp without time zone) AND (datahora <= '2017-12-31 23:59:59'::timestamp without time zone))
  • Heap Blocks: exact=51660
8. 586.344 586.344 ↑ 1.0 2,705,001 1

Bitmap Index Scan on idx_movimentacaoestoque_datahora_unidadenegocioid (cost=0.00..71,153.57 rows=2,734,113 width=0) (actual time=586.344..586.344 rows=2,705,001 loops=1)

  • Index Cond: ((datahora >= '2017-01-01 00:00:00'::timestamp without time zone) AND (datahora <= '2017-12-31 23:59:59'::timestamp without time zone))
9. 1,302.493 29,067.953 ↓ 2.1 2,150,509 1

Hash (cost=501,457.60..501,457.60 rows=1,031,442 width=47) (actual time=29,067.953..29,067.953 rows=2,150,509 loops=1)

  • Buckets: 65536 (originally 65536) Batches: 131072 (originally 32) Memory Usage: 4297kB
10. 1,505.563 27,765.460 ↓ 2.1 2,150,509 1

Hash Right Join (cost=242,072.92..501,457.60 rows=1,031,442 width=47) (actual time=9,618.956..27,765.460 rows=2,150,509 loops=1)

  • Hash Cond: (i.vendaid = v.id)
11. 16,649.351 16,977.753 ↓ 1.0 2,150,492 1

Bitmap Heap Scan on itemvenda i (cost=58,792.55..264,708.26 rows=2,115,914 width=39) (actual time=335.672..16,977.753 rows=2,150,492 loops=1)

  • Recheck Cond: ((datahora >= '2017-01-01 00:00:00'::timestamp without time zone) AND (datahora <= '2017-12-31 23:59:59'::timestamp without time zone))
  • Rows Removed by Index Recheck: 3292611
  • Heap Blocks: exact=31216 lossy=79377
12. 328.402 328.402 ↓ 1.0 2,150,492 1

Bitmap Index Scan on cidx_itemvenda_datahora_unidadenegocioid (cost=0.00..58,263.57 rows=2,115,914 width=0) (actual time=328.402..328.402 rows=2,150,492 loops=1)

  • Index Cond: ((datahora >= '2017-01-01 00:00:00'::timestamp without time zone) AND (datahora <= '2017-12-31 23:59:59'::timestamp without time zone))
13. 281.660 9,282.144 ↓ 1.0 1,034,054 1

Hash (cost=162,328.34..162,328.34 rows=1,031,442 width=40) (actual time=9,282.144..9,282.144 rows=1,034,054 loops=1)

  • Buckets: 65536 Batches: 32 Memory Usage: 2795kB
14. 8,668.503 9,000.484 ↓ 1.0 1,034,054 1

Bitmap Heap Scan on venda v (cost=39,064.71..162,328.34 rows=1,031,442 width=40) (actual time=341.726..9,000.484 rows=1,034,054 loops=1)

  • Recheck Cond: ((datahorafechamento >= '2017-01-01 00:00:00'::timestamp without time zone) AND (datahorafechamento <= '2017-12-31 23:59:59'::timestamp without time zone))
  • Rows Removed by Index Recheck: 852208
  • Heap Blocks: exact=48489 lossy=26662
15. 331.981 331.981 ↓ 1.0 1,034,054 1

Bitmap Index Scan on idx_venda_datahorafechamento_unidadenegocioid (cost=0.00..38,806.85 rows=1,031,442 width=0) (actual time=331.981..331.981 rows=1,034,054 loops=1)

  • Index Cond: ((datahorafechamento >= '2017-01-01 00:00:00'::timestamp without time zone) AND (datahorafechamento <= '2017-12-31 23:59:59'::timestamp without time zone))
16. 6.955 44.291 ↓ 1.0 44,228 1

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

  • Buckets: 65536 Batches: 1 Memory Usage: 2586kB
17. 14.786 37.336 ↓ 1.0 44,228 1

Hash Left Join (cost=1,879.77..4,026.76 rows=44,126 width=16) (actual time=16.307..37.336 rows=44,228 loops=1)

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

Seq Scan on embalagem e (cost=0.00..1,540.26 rows=44,126 width=16) (actual time=0.016..6.320 rows=44,228 loops=1)

19. 6.524 16.230 ↓ 1.0 41,109 1

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

  • Buckets: 65536 Batches: 1 Memory Usage: 2536kB
20. 9.706 9.706 ↓ 1.0 41,109 1

Seq Scan on produto p (cost=0.00..1,367.12 rows=41,012 width=24) (actual time=0.012..9.706 rows=41,109 loops=1)

21. 0.001 0.017 ↑ 1.0 15 1

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

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

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

23. 7.391 14.241 ↑ 1.0 56,461 1

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

  • Buckets: 65536 Batches: 1 Memory Usage: 3159kB
24. 6.850 6.850 ↑ 1.0 56,461 1

Seq Scan on classificacaoproduto cp (cost=0.00..986.43 rows=56,743 width=16) (actual time=0.010..6.850 rows=56,461 loops=1)