explain.depesz.com

PostgreSQL's explain analyze made readable

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

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 124.623 8,001.413 ↑ 4.1 64,662 1

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

2. 243.765 7,876.790 ↑ 4.1 64,662 1

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

  • Sort Key: (sum(ventas.monto)) DESC, ventas.sku, stock.stock_disponible, jerarquia.costo_calculado, jerarquia.descripcion, ventas.depto, (sum(ventas.cantidad))
  • Sort Method: external merge Disk: 6,576kB
3. 207.678 7,633.025 ↑ 4.1 64,662 1

Finalize GroupAggregate (cost=142,673.15..181,468.47 rows=263,503 width=110) (actual time=6,910.604..7,633.025 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. 327.732 7,425.347 ↑ 2.0 107,552 1

Gather Merge (cost=142,673.15..172,685.03 rows=219,586 width=110) (actual time=6,910.595..7,425.347 rows=107,552 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
5. 93.651 7,097.615 ↑ 3.1 35,851 3 / 3

Partial GroupAggregate (cost=141,673.13..146,339.33 rows=109,793 width=110) (actual time=6,887.970..7,097.615 rows=35,851 loops=3)

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

Sort (cost=141,673.13..141,947.61 rows=109,793 width=70) (actual time=6,887.953..7,003.964 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,912kB
7. 144.435 5,636.709 ↑ 1.1 103,062 3 / 3

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

  • Hash Cond: (ventas.id_depto = neg_div_depto.id_depto)
8. 689.406 5,492.220 ↑ 1.1 104,436 3 / 3

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

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

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

  • Hash Cond: (ventas.sku = stock.sku)
10. 189.977 202.476 ↑ 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=35.130..202.476 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,671
11. 12.499 12.499 ↓ 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=37.496..37.496 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. 607.916 1,107.747 ↑ 1.0 550,360 3 / 3

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

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

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

14. 709.695 2,904.165 ↓ 1.0 786,409 3 / 3

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

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

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

16. 0.023 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.031 0.031 ↑ 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.031 rows=66 loops=3)

  • Filter: (id_depto IS NOT NULL)