explain.depesz.com

PostgreSQL's explain analyze made readable

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

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 122.615 7,308.294 ↑ 2.5 64,662 1

Subquery Scan on source (cost=209,554.17..214,024.27 rows=162,549 width=162) (actual time=7,150.416..7,308.294 rows=64,662 loops=1)

2. 246.098 7,185.679 ↑ 2.5 64,662 1

Sort (cost=209,554.17..209,960.54 rows=162,549 width=182) (actual time=7,150.406..7,185.679 rows=64,662 loops=1)

  • Sort Key: (sum(ventas.monto)) DESC, ventas.sku, (sum(ventas.cantidad))
  • Sort Method: external merge Disk: 7,304kB
3. 263.093 6,939.581 ↑ 2.5 64,662 1

Finalize GroupAggregate (cost=142,678.31..181,037.13 rows=162,549 width=182) (actual time=6,083.220..6,939.581 rows=64,662 loops=1)

  • Group Key: ventas.sku, ventas.depto, ventas.estado, ventas.id_depto
4. 360.104 6,676.488 ↑ 2.0 107,421 1

Gather Merge (cost=142,678.31..172,966.33 rows=219,598 width=150) (actual time=6,083.180..6,676.488 rows=107,421 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
5. 130.199 6,316.384 ↑ 3.1 35,807 3 / 3

Partial GroupAggregate (cost=141,678.29..146,619.24 rows=109,799 width=150) (actual time=6,063.783..6,316.384 rows=35,807 loops=3)

  • Group Key: ventas.sku, ventas.depto, ventas.estado, ventas.id_depto
6. 1,221.412 6,186.185 ↑ 1.1 103,062 3 / 3

Sort (cost=141,678.29..141,952.79 rows=109,799 width=70) (actual time=6,063.768..6,186.185 rows=103,062 loops=3)

  • Sort Key: ventas.sku, ventas.depto, ventas.estado, ventas.id_depto
  • Sort Method: external merge Disk: 8,808kB
7. 167.521 4,964.773 ↑ 1.1 103,062 3 / 3

Hash Semi Join (cost=67,577.47..127,981.14 rows=109,799 width=70) (actual time=3,496.130..4,964.773 rows=103,062 loops=3)

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

Hash Left Join (cost=67,574.99..126,468.92 rows=109,799 width=70) (actual time=3,496.052..4,797.197 rows=104,436 loops=3)

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

Hash Left Join (cost=28,130.39..77,279.58 rows=109,799 width=49) (actual time=1,153.171..1,814.142 rows=104,436 loops=3)

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

Parallel Bitmap Heap Scan on ventas (cost=7,636.29..50,655.75 rows=109,799 width=45) (actual time=22.745..180.906 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,650
11. 12.020 12.020 ↓ 1.2 313,307 1 / 3

Bitmap Index Scan on ventas_estado_fecha_idx (cost=0.00..7,570.41 rows=263,517 width=0) (actual time=36.059..36.059 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. 595.085 1,129.013 ↑ 1.0 550,360 3 / 3

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

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

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

14. 838.079 2,342.674 ↓ 1.0 786,409 3 / 3

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

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

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

16. 0.025 0.055 ↑ 1.0 66 3 / 3

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

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

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

  • Filter: (id_depto IS NOT NULL)