explain.depesz.com

PostgreSQL's explain analyze made readable

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

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 129.462 7,622.786 ↑ 4.1 64,662 1

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

2. 242.906 7,493.324 ↑ 4.1 64,662 1

Sort (cost=220,506.06..221,164.82 rows=263,503 width=110) (actual time=7,460.181..7,493.324 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. 267.777 7,250.418 ↑ 4.1 64,662 1

Finalize GroupAggregate (cost=142,673.15..181,468.47 rows=263,503 width=110) (actual time=6,448.276..7,250.418 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. 315.809 6,982.641 ↑ 2.0 107,504 1

Gather Merge (cost=142,673.15..172,685.03 rows=219,586 width=110) (actual time=6,448.265..6,982.641 rows=107,504 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
5. 109.490 6,666.832 ↑ 3.1 35,835 3 / 3

Partial GroupAggregate (cost=141,673.13..146,339.33 rows=109,793 width=110) (actual time=6,434.341..6,666.832 rows=35,835 loops=3)

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

Sort (cost=141,673.13..141,947.61 rows=109,793 width=70) (actual time=6,434.331..6,557.342 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. 154.733 5,215.006 ↑ 1.1 103,062 3 / 3

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

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

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

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

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

  • Hash Cond: (ventas.sku = stock.sku)
10. 288.952 323.574 ↑ 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=95.524..323.574 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. 34.622 34.622 ↓ 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=103.866..103.866 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. 234.497 1,160.708 ↑ 1.0 550,360 3 / 3

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

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

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

14. 533.316 2,446.921 ↓ 1.0 786,409 3 / 3

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

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

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

16. 0.023 0.056 ↑ 1.0 66 3 / 3

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

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

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

  • Filter: (id_depto IS NOT NULL)