explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Jph5

Settings
# exclusive inclusive rows x rows loops node
1. 8,946.763 30,158.540 ↑ 86,832.9 11 1

GroupAggregate (cost=1,563,516.48..1,673,784.76 rows=955,162 width=576) (actual time=20,260.493..30,158.540 rows=11 loops=1)

  • Group Key: (to_char(v.datahorafechamento, 'YYYY-MM'::text)), NULL::text, NULL::text, NULL::text, NULL::text, NULL::text, NULL::text, NULL::text, NULL::text, NULL::text, NULL::text, NULL::text, NULL::text
2. 7,821.087 21,211.777 ↓ 3.4 4,489,356 1

Sort (cost=1,563,516.48..1,566,839.14 rows=1,329,065 width=437) (actual time=19,327.242..21,211.777 rows=4,489,356 loops=1)

  • Sort Key: (to_char(v.datahorafechamento, 'YYYY-MM'::text))
  • Sort Method: external merge Disk: 219600kB
3. 3,277.691 13,390.690 ↓ 3.4 4,489,356 1

Hash Left Join (cost=571,061.78..901,377.41 rows=1,329,065 width=437) (actual time=6,796.325..13,390.690 rows=4,489,356 loops=1)

  • Hash Cond: (p.id = cp.produtoid)
4. 648.838 10,099.077 ↓ 2.1 1,995,224 1

Hash Left Join (cost=569,366.06..869,860.06 rows=960,605 width=37) (actual time=6,782.334..10,099.077 rows=1,995,224 loops=1)

  • Hash Cond: (i.embalagemid = e.id)
5. 1,646.323 9,407.539 ↓ 2.1 1,995,224 1

Hash Right Join (cost=564,787.73..852,073.41 rows=960,605 width=37) (actual time=6,739.577..9,407.539 rows=1,995,224 loops=1)

  • Hash Cond: (m.id = i.movimentacaoestoqueid)
6. 1,030.778 1,614.226 ↓ 1.0 2,369,869 1

Bitmap Heap Scan on movimentacaoestoque m (cost=61,060.03..307,699.98 rows=2,323,863 width=14) (actual time=589.532..1,614.226 rows=2,369,869 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=30387 lossy=53173
7. 583.448 583.448 ↓ 1.0 2,371,901 1

Bitmap Index Scan on idx_movimentacaoestoque_datahora_unidadenegocioid (cost=0.00..60,479.07 rows=2,323,863 width=0) (actual time=583.448..583.448 rows=2,371,901 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))
8. 524.592 6,146.990 ↓ 2.1 1,995,224 1

Hash (cost=484,215.13..484,215.13 rows=960,605 width=39) (actual time=6,146.990..6,146.990 rows=1,995,224 loops=1)

  • Buckets: 65536 (originally 65536) Batches: 512 (originally 32) Memory Usage: 3887kB
9. 1,222.022 5,622.398 ↓ 2.1 1,995,224 1

Hash Right Join (cost=232,033.94..484,215.13 rows=960,605 width=39) (actual time=3,796.106..5,622.398 rows=1,995,224 loops=1)

  • Hash Cond: (i.vendaid = v.id)
10. 611.159 784.302 ↓ 1.0 1,995,211 1

Bitmap Heap Scan on itemvenda i (cost=53,937.67..257,231.58 rows=1,941,194 width=39) (actual time=179.828..784.302 rows=1,995,211 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=35750
11. 173.143 173.143 ↓ 1.0 1,995,468 1

Bitmap Index Scan on cidx_itemvenda_datahora_unidadenegocioid (cost=0.00..53,452.38 rows=1,941,194 width=0) (actual time=173.143..173.143 rows=1,995,468 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))
12. 230.385 3,616.074 ↑ 1.0 944,679 1

Hash (cost=158,583.71..158,583.71 rows=960,605 width=40) (actual time=3,616.074..3,616.074 rows=944,679 loops=1)

  • Buckets: 65536 Batches: 32 Memory Usage: 2593kB
13. 3,103.201 3,385.689 ↑ 1.0 944,679 1

Bitmap Heap Scan on venda v (cost=36,382.63..158,583.71 rows=960,605 width=40) (actual time=288.552..3,385.689 rows=944,679 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=28218 lossy=26576
14. 282.488 282.488 ↑ 1.0 946,699 1

Bitmap Index Scan on idx_venda_datahorafechamento_unidadenegocioid (cost=0.00..36,142.48 rows=960,605 width=0) (actual time=282.488..282.488 rows=946,699 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))
15. 6.983 42.700 ↓ 1.0 44,228 1

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

  • Buckets: 65536 Batches: 1 Memory Usage: 2586kB
16. 14.319 35.717 ↓ 1.0 44,228 1

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

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

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

18. 6.010 15.075 ↓ 1.0 41,109 1

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

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

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

20. 7.298 13.922 ↑ 1.0 56,461 1

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

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

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