explain.depesz.com

PostgreSQL's explain analyze made readable

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

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 157.299 6,954.702 ↓ 1.6 64,654 1

Subquery Scan on source (cost=162,033.44..163,138.55 rows=40,186 width=157) (actual time=6,764.723..6,954.702 rows=64,654 loops=1)

2. 252.828 6,797.403 ↓ 1.6 64,654 1

Sort (cost=162,033.44..162,133.90 rows=40,186 width=161) (actual time=6,764.714..6,797.403 rows=64,654 loops=1)

  • Sort Key: (sum(ventas.monto)) DESC, ventas.sku, (sum(ventas.cantidad))
  • Sort Method: external merge Disk: 5,800kB
3. 222.931 6,544.575 ↓ 1.6 64,654 1

Finalize GroupAggregate (cost=140,855.31..155,663.33 rows=40,186 width=161) (actual time=5,772.008..6,544.575 rows=64,654 loops=1)

  • Group Key: ventas.sku
4. 388.284 6,321.644 ↓ 1.3 106,668 1

Gather Merge (cost=140,855.31..153,553.57 rows=80,372 width=129) (actual time=5,771.986..6,321.644 rows=106,668 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
5. 119.375 5,933.360 ↑ 1.1 35,556 3 / 3

Partial GroupAggregate (cost=139,855.29..143,276.62 rows=40,186 width=129) (actual time=5,695.305..5,933.360 rows=35,556 loops=3)

  • Group Key: ventas.sku
6. 1,049.155 5,813.985 ↑ 1.1 103,062 3 / 3

Sort (cost=139,855.29..140,129.79 rows=109,799 width=52) (actual time=5,695.291..5,813.985 rows=103,062 loops=3)

  • Sort Key: ventas.sku
  • Sort Method: external merge Disk: 6,184kB
7. 170.337 4,764.830 ↑ 1.1 103,062 3 / 3

Hash Semi Join (cost=67,577.47..126,907.14 rows=109,799 width=52) (actual time=3,312.326..4,764.830 rows=103,062 loops=3)

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

Hash Left Join (cost=67,574.99..125,394.92 rows=109,799 width=52) (actual time=3,312.249..4,594.439 rows=104,436 loops=3)

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

Hash Left Join (cost=28,130.39..76,849.58 rows=109,799 width=31) (actual time=1,134.384..1,755.312 rows=104,436 loops=3)

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

Parallel Bitmap Heap Scan on ventas (cost=7,636.29..50,655.75 rows=109,799 width=27) (actual time=28.203..180.481 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,495
11. 11.903 11.903 ↓ 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=35.710..35.710 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. 624.770 1,105.712 ↑ 1.0 550,360 3 / 3

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

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

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

14. 1,072.511 2,177.664 ↓ 1.0 786,409 3 / 3

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

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

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

16. 0.024 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.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)