explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 1BXq : Optimization for: Optimization for: Optimization for: Optimization for: ads; plan #hYOe; plan #MxQ; plan #9CPK; plan #NamN

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 121.876 7,439.634 ↑ 4.1 64,662 1

Subquery Scan on source (cost=220,506.06..227,752.40 rows=263,503 width=125) (actual time=7,285.655..7,439.634 rows=64,662 loops=1)

2. 242.921 7,317.758 ↑ 4.1 64,662 1

Sort (cost=220,506.06..221,164.82 rows=263,503 width=110) (actual time=7,285.647..7,317.758 rows=64,662 loops=1)

  • Sort Key: (sum(ventas.monto)) DESC, ventas.sku, (sum(ventas.cantidad))
  • Sort Method: external merge Disk: 6,576kB
3. 223.683 7,074.837 ↑ 4.1 64,662 1

Finalize GroupAggregate (cost=142,673.15..181,468.47 rows=263,503 width=110) (actual time=6,319.339..7,074.837 rows=64,662 loops=1)

  • Group Key: ventas.sku, stock.stock_disponible, jerarquia.costo_calculado, jerarquia.descripcion, ventas.depto, ventas.estado, ventas.id_depto
4. 312.760 6,851.154 ↑ 2.0 107,342 1

Gather Merge (cost=142,673.15..172,685.03 rows=219,586 width=110) (actual time=6,319.327..6,851.154 rows=107,342 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
5. 104.207 6,538.394 ↑ 3.1 35,781 3 / 3

Partial GroupAggregate (cost=141,673.13..146,339.33 rows=109,793 width=110) (actual time=6,308.040..6,538.394 rows=35,781 loops=3)

  • Group Key: ventas.sku, stock.stock_disponible, jerarquia.costo_calculado, jerarquia.descripcion, ventas.depto, ventas.estado, ventas.id_depto
6. 1,369.707 6,434.187 ↑ 1.1 103,062 3 / 3

Sort (cost=141,673.13..141,947.61 rows=109,793 width=70) (actual time=6,308.028..6,434.187 rows=103,062 loops=3)

  • Sort Key: ventas.sku, stock.stock_disponible, jerarquia.costo_calculado, jerarquia.descripcion, ventas.depto, ventas.estado, ventas.id_depto
  • Sort Method: external merge Disk: 8,840kB
7. 153.349 5,064.480 ↑ 1.1 103,062 3 / 3

Hash Semi Join (cost=67,577.29..127,976.52 rows=109,793 width=70) (actual time=3,636.523..5,064.480 rows=103,062 loops=3)

  • Hash Cond: (ventas.id_depto = neg_div_depto.id_depto)
8. 647.734 4,911.077 ↑ 1.1 104,436 3 / 3

Hash Left Join (cost=67,574.81..126,464.39 rows=109,793 width=70) (actual time=3,636.445..4,911.077 rows=104,436 loops=3)

  • Hash Cond: (ventas.estilo = jerarquia.estilo)
9. 502.506 1,751.987 ↑ 1.1 104,436 3 / 3

Hash Left Join (cost=28,130.21..77,275.13 rows=109,793 width=49) (actual time=1,124.876..1,751.987 rows=104,436 loops=3)

  • Hash Cond: (ventas.sku = stock.sku)
10. 147.345 159.079 ↑ 1.1 104,436 3 / 3

Parallel Bitmap Heap Scan on ventas (cost=7,636.11..50,653.38 rows=109,793 width=45) (actual time=31.846..159.079 rows=104,436 loops=3)

  • Recheck Cond: ((fecha >= (concat('2019-05-26 23:30:00', '-04'))::timestamp with time zone) AND (fecha <= (concat('2019-05-29 23:59:59', '-04'))::timestamp with time zone))
  • Filter: (estado ~~ 'paid'::text)
  • Heap Blocks: exact=1,657
11. 11.734 11.734 ↓ 1.2 313,307 1 / 3

Bitmap Index Scan on ventas_estado_fecha_idx (cost=0.00..7,570.23 rows=263,503 width=0) (actual time=35.202..35.202 rows=313,307 loops=1)

  • Index Cond: ((estado = 'paid'::text) AND (fecha >= (concat('2019-05-26 23:30:00', '-04'))::timestamp with time zone) AND (fecha <= (concat('2019-05-29 23:59:59', '-04'))::timestamp with time zone))
12. 588.465 1,090.402 ↑ 1.0 550,360 3 / 3

Hash (cost=10,926.60..10,926.60 rows=550,360 width=13) (actual time=1,090.402..1,090.402 rows=550,360 loops=3)

  • Buckets: 131,072 Batches: 16 Memory Usage: 2,592kB
13. 501.937 501.937 ↑ 1.0 550,360 3 / 3

Seq Scan on stock (cost=0.00..10,926.60 rows=550,360 width=13) (actual time=0.013..501.937 rows=550,360 loops=3)

14. 498.618 2,511.356 ↓ 1.0 786,409 3 / 3

Hash (cost=23,613.60..23,613.60 rows=779,360 width=35) (actual time=2,511.356..2,511.356 rows=786,409 loops=3)

  • Buckets: 65,536 Batches: 16 Memory Usage: 3,772kB
15. 2,012.738 2,012.738 ↓ 1.0 786,409 3 / 3

Seq Scan on jerarquia (cost=0.00..23,613.60 rows=779,360 width=35) (actual time=0.024..2,012.738 rows=786,409 loops=3)

16. 0.022 0.054 ↑ 1.0 66 3 / 3

Hash (cost=1.66..1.66 rows=66 width=3) (actual time=0.054..0.054 rows=66 loops=3)

  • Buckets: 1,024 Batches: 1 Memory Usage: 11kB
17. 0.032 0.032 ↑ 1.0 66 3 / 3

Seq Scan on neg_div_depto (cost=0.00..1.66 rows=66 width=3) (actual time=0.014..0.032 rows=66 loops=3)

  • Filter: (id_depto IS NOT NULL)